Solved

Analyzing Explain Plan

Posted on 2012-03-27
8
915 Views
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.

Thanks,
Chanikya.
0
Comment
Question by:chanikya
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 5

Expert Comment

by:Bajwa
ID: 37771307
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 37771830
Here is copy/paste from the above akadia.com website

Simple explain plan:

Query Plan
-----------------------------------------
SELECT STATEMENT     [CHOOSE] Cost=1234
  TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]

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:

SELECT STATEMENT     [CHOOSE] Cost=1234

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

SELECT STATEMENT     [CHOOSE] Cost=

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.
0
 
LVL 73

Expert Comment

by:sdstuber
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

always.

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
http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm

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.
0
 

Author Comment

by:chanikya
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?


Thanks,
Chanikya.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Assisted Solution

by:Akenathon
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!
0
 
LVL 73

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:chanikya
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.

Thanks,
Chanikya.
0
 
LVL 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now