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.