Analyzing Explain Plan

Hi Experts,

I read oracle docs and book on performance tuning. But still here is my question
"how to read and interpret the explain plan cost analysis details including when to focus on cost number versus focus on index/sql interpretation details".

Could you provide any information related to that.. And also source for SQL Tuning Tips is very much appreciated.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Here is copy/paste from the above website

Simple explain plan:

Query Plan

The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means we are doing a full table scan of table LARGE. When this operation completes then the resultant row source is passed up to the
next level of the query for processing. In this case it is the SELECT STATEMENT which is the top of the query.

[CHOOSE] is an indication of the optimizer_goal for the query. This DOES NOT necessarily indicate that plan has actually used this goal. The only way to confirm this is to check the
cost= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field:


However the explain plan below indicates the use of the RBO because the cost field is blank:


The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.

[:Q65001] indicates that this particular part of the query is being executed in parallel. This number indicates that the operation will be processed by a parallel query slave as opposed to being executed serially.

[ANALYZED] indicates that the object in question has been analyzed and there are currently statistics available for the CBO to use. There is no indication of the 'level' of analysis done.
>>> when to focus on cost number

never.  - the cost is an internal number that has no meaning after a plan is generated.  While it is true that "some" queries will have a lower plan and run faster, there is no cause/effect relationship between them.

>>> index/sql interpretation


Check the estimated cardinalities (rows)of each step.  If it's doing a scan and estimates 1000 rows, is that a reasonable estimate?  Or are there really 2 rows or 10000000 rows?  If Oracle is wrong about how big each step is it will likely make poor decisions about which indexes to use and how to join different tables.  If it's fairly accurate in how big each step is, it will "usually" pick good indexes and joins.

An excellent place to start is the Oracle Performance Tuning Guide

Other good sources are anything by Tom Kyte, Jonathan Lewis or Richard Foote.  The latter two tend to get into deep internals of the optimizer and index structures so you might want save them for later after doing some other reading first.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

chanikyaAuthor Commented:
Hi Genius,

where do we get the actual plan?
 In the one of the link above, i read as even plan from v$SQL_PLAN is also not the actual plan and the plan from only trace file(TKProf) is the actual plan. Is it correct?

A PLAN is what the optimizer tells you it plans to do when you give it a SQL. It can differ from the actual steps Oracle carries out when the same SQL is fired by one of your applications because of many many reasons, including:
From your schema the object names resolve to different objects than from the session executing the app
Both you and the session running the app could have adjusted some optimizer influencing parameter
Your bind variables have different values, or they HAD different values when the statement was parsed and the binds peeked
Instead, a 10046 trace gives you actual execution statistics, so it tells you what actually happened. TKPROF parses those and gives you something similar to an execution plan, but instead of estimated cardinalities it shows you actual rows. It can also give you the plan when it parses the trace file, and a good excercise as sdstuber suggests is comparing those two and giving Oracle more information to help it come up with better estimates, especially where it's off by an order of magnitude or more. A good portion of the art of SQL tuning includes figuring out how to best make that happen. Some ideas:
Updating stale statistics
Faking or manually creating statistics (read Wolfgang's papers for that one)
Creating or dropping an index
Hinting the SQL (typically as a last resort, since you are not solving the general case)
Of course SQL tuning is not everything... maybe what you really need is instance tuning, OS tuning, I/O subsystem tuning. Tuning is one of the most challenging aspects of dealing with the Oracle DBMS, but it's also very rewarding. There are no silver bullets, you have to read a lot and be prepared for much experimenting through trial and error. Personally I recommend Johnathan Lewis "Cost Based Oracle Fundamentals". It's not an easy read, very technical stuff but again, tuning is not something you'll learn in a few days. Good luck!

PS: Don't pay too much attention to the "cost" field at first. Concentrate on how the optimizer figured out it should answer your SQL expressed question, and try to come up with a better idea. Then test it, forcing your way using performance hints. If it works, try to understand what Oracle is missing and provide it!
The plans in v$sql_plan are real plans for former executions.

They do not necessarily mean that future executions will use the same plan.

you can generate a plan with the explain statement, there are examples in the links above but to summarize...

EXPLAIN PLAN FOR  select * from some_table;
select * from table(dbms_xplan.display);

explain plan offers other options (such giving the plan an id to differentiate it from others)
dbms_xplan package offers other options too to show additional information in the plan.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chanikyaAuthor Commented:
Hi Experts,

I read Jonathan Lewis "Cost Based Optimizer Fundamentals" and also "oracle documentation".. I am planning to give session on performance tuning.. So, i would like to have experts opinion before that.. Thans for your support.

why the B?  penalty grades aren't appropriate if you haven't asked for additional followup
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.