Go Premium for a chance to win a PS4. Enter to Win


Analyzing Explain Plan

Posted on 2012-03-27
Medium Priority
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
  • 3
  • 2
  • 2
  • +1

Expert Comment

ID: 37771307

Expert Comment

ID: 37771830
Here is copy/paste from the above akadia.com 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.
Industry Leaders: 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 600 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 900 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows how to recover a database from a user managed backup

877 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