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

Reading an execution plan

How should I read an execution plan? And which of the TKPROF or EXPLAIN PLAN would give me more realistic details regarding how a SQL query is executed. How do you read the output displayed by the DBMS_XPLAN?

TKPROF, EXPLAIN PLAN, and DBMS_XPLAN all display essentially the same information. DBMS_XPLAN does have some nice additional information and it is a good package to use. The big benefit of TKPROF is that you can trace your session and generate Explain Plans of all your session's SQL statements, rather then generating them one by one.

To read the output, I go down and right until I can't go down and right any more. Then cascade back up the tree. For instance:

SELECT STATEMENT 
  SORT GROUP BY 
    HASH JOIN   
      TABLE ACCESS FULL| DEPT |  
      TABLE ACCESS FULL| EMP  |   
In the example above, going down and right leads us to a Full Table Access fo the DEPT table. On that same level, I see another Full Table Scan of the EMP table. Both of these actions are performed. Going back up the tree (or to the left, the tree is on its side), I perform a HASH JOIN of these two tables. I then Sort them and the results are passed to the SELECT statement.

The following Chapter in the Performance Tuning Guide give much more information: http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10752/ex_plan.htm#19259

I'd also read other chapters in that document that pertain to tuning.


This was last published in July 2004

Dig Deeper on Oracle database design and architecture

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