Analyzing Explain Plan

Posted on 2012-03-27
Last Modified: 2012-04-03
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.

Question by:chanikya
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1

Expert Comment

ID: 37771307

Expert Comment

ID: 37771830
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.
LVL 74

Expert Comment

ID: 37771859
>>> 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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 37775405
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?

LVL 11

Assisted Solution

Akenathon earned 200 total points
ID: 37798460
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!
LVL 74

Accepted Solution

sdstuber earned 300 total points
ID: 37799200
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.

Author Closing Comment

ID: 37799459
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.

LVL 74

Expert Comment

ID: 37800567
why the B?  penalty grades aren't appropriate if you haven't asked for additional followup

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question