Home » RDBMS Server » Performance Tuning » Explain plan
Explain plan [message #125834] Wed, 29 June 2005 08:01 Go to next message
peanand
Messages: 1
Registered: June 2005
Location: Chennai
Junior Member

Hi,

I have few queries which need to be tuned. My clarifiaction is based on the explain plan.

1. how can we make oracle use CBO for execution.
2. How can we arrive to conclusion whether a query needs to be tuned or not.
3. any descriptive information on the cost=<some value> which appears in the exaplain plan.
4. what are the other things which we need to keep in mind while tuning so.

Please share any documents / links on the same.

Rgds.
Anand.
Re: Explain plan [message #125841 is a reply to message #125834] Wed, 29 June 2005 08:23 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>1. how can we make oracle use CBO for execution.

it starts with setting OPTIMZER_MODE=CHOOSE
Read Documentation for pros and cons.


>>2. How can we arrive to conclusion whether a query needs to be tuned or not.
Very trivial question. you have to decide that.
If your customers/clients/users are happy with a simple select running for a few hours
you dont need to tune.

>>3. any descriptive information on the cost=<some value> which appears in the exaplain plan.
Cost is just some derived number.
It cannot be considered as a measure of performance.
The optimizer computes many criterions in many phases to come with this cost,
and uses the plan with the lowest cost.
But it doesnt mean that a plan with higher cost is bad or a plan with lower cost is better.
the tunining manual deals with this extensively.

>>4. what are the other things which we need to keep in mind while tuning so.
First understand what you are trying to do , underlying basic facts and whether it is really needed to it.
Some have what we call 'CTD' ( compulsive tuning disorder). These folks want to tune everything, pro-actively by indexing all the columns, adding hints etc.

More Reading should help you.
Please read documentation available in ( and many other sites)
http://www.oracle.com/technology/documentation/index.html





Previous Topic: Logical database design
Next Topic: unable to allocate 4128 bytes of shared memory ("shared pool
Goto Forum:
  


Current Time: Wed Jan 27 14:44:40 CST 2021