OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Explain plan

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Explain plan [message #125834]

Wed, 29 June 2005 08:01

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.

Report message to a moderator

Re: Explain plan [message #125841 is a reply to message #125834]

Wed, 29 June 2005 08:23

Mahesh Rajendran
Messages: 10707
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 22:33:28 CDT 2024