Q
Problem solve Get help with specific problems with your technologies, process and projects.

Reading Execution Plan output

I need a quick guide or how-to on reading an Execution Plan output. I am not familiar with analyzing different parameters, such as COST, Cardinality, Nested Loops, Merge Joins, Hash join, etc. I have just purchased your book on it and am going through it, but this is sort of urgent. If you could explain about the different parameters of the execution plan output, it would be a great help.

This is quite a tall order. Tuning SQL statements, with or without an explain plan can be tricky. Generally you read from the bottom up, inside to outside. A good formatting query can be a big help to eliminate unneeded data from the table.

For example:

 
Set lines 132 pages 47
COLUMN QUERY_PLAN FORMAT A80
column statement_id format a11 heading ID
spool ex_plan..lis
SELECT statement_id,
LPAD( '  ' , 2*LEVEL)||OPERATION||' '||options||' '||OBJECT_NAME||' cost: '||cost ||' goal:'||optimizer QUERY_PLAN
FROM &&owner..PLAN_TABLE 
CONNECT BY PRIOR ID = PARENT_ID 
START WITH ID=0;
spool off
truncate table &&owner..plan_table;
ed ex_plan..lis
undef id
undef owner 

Once you have a formatted the explain plan, look at the highest cost lines to determine what can be done to fix it.

Dig Deeper on Oracle DBA jobs, training and certification

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close