India Trip Report: The Oracle Documentation is Always Correct NOT
I just got back from speaking at Sangam 10, the annual conference of the All India Oracle Users Group (AIOUG) in Hyderabad, India. Memorable moments include the sumptuous lunch buffets and Jonathan Lewis gamely speaking in pitch darkness when the lights went out. Did I mention the sumptuous lunch buffets? The Dahi Bhalla was to die for; lentil fritters in a yoghurt sauce.
One of my presentations was SQL 101: Interpreting Query Execution Plans. I showed the audience a complex query execution plan and asked which step was executed first. The most popular answer was 14 because it is most deeply indented to the right. Jonathan then raised his hand but I enforced the “anyone except Jonathan” rule 
Take a shot at it yourself. For extra credit, what are the second and third steps? The entire correct sequence can be found later in this article.
Here’s the query execution plan in my presentation. In the interests of simplicity, it contains just a few columns from the original plan.
------------------------------------------------------------------------- | Id | Operation | Name | A-Time | ------------------------------------------------------------------------- | 1 | SORT ORDER BY | |00:00:30.33 | | 2 | HASH GROUP BY | |00:00:30.30 | |* 3 | FILTER | |00:00:28.28 | |* 4 | HASH JOIN RIGHT OUTER | |00:00:27.12 | | 5 | TABLE ACCESS FULL | DIM_E |00:00:00.01 | |* 6 | HASH JOIN RIGHT OUTER | |00:00:23.63 | | 7 | TABLE ACCESS FULL | DIM_D |00:00:00.01 | |* 8 | HASH JOIN | |00:00:20.72 | | 9 | TABLE ACCESS BY INDEX ROWID | DIM_C |00:00:00.87 | | 10 | NESTED LOOPS | |00:00:00.04 | | 11 | NESTED LOOPS | |00:00:00.01 | |* 12 | TABLE ACCESS FULL | DIM_A |00:00:00.01 | |* 13 | TABLE ACCESS BY INDEX ROWID| DIM_B |00:00:00.01 | |* 14 | INDEX RANGE SCAN | IDX_DIM_B_1 |00:00:00.01 | |* 15 | INDEX RANGE SCAN | IDX_DIM_C_1 |00:00:00.02 | |* 16 | TABLE ACCESS FULL | FACT |00:00:13.41 | -------------------------------------------------------------------------
The SQL query in the example was of the type that is typically seen in a data warehouse. The schema was a snowflake with a large fact table joining to dimension tables. The text of the query would be very boring but here’s a picture of a snowflake—courtesy of the good people of WikiMedia Commons.
The most popular answer was 14 because it is most deeply indented to the right. This answer is in keeping with the following explanation in the Oracle documentation: “The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.”
But of course, the correct answer is 5, not 14. An execution plan is a tree and the nodes are intended to be processed in postorder sequence; that is, child nodes are processed before the parent nodes. Here’s a longwinded explanation from my book Beginning Oracle Database 11g Administration: “Perform operations in the order in which they are listed [in the output of EXPLAIN PLAN or DBMS_XPLAN] except that if the operations listed after a certain operation are more deeply indented in the listing, then perform those operations first (in the order in which those operations are listed.)” Therefore, the correct sequence of operations is 5, 7, 12, 14, 13, 11, 15, 10, 9, 16, 8, 6, 4, 3, 2, and 1.
After the presentation, a very nice lady came over and gently protested my providing different guidance than the Oracle documentation
I plan on going to Sangam 11 if they will have me there. I’m setting up a Yahoo Group for attendees of another of my presentations at Sangam 10: 52 Weeks in the Life of a Database. I made the same presentation at the NoCOUG Summer Conference in California on August 19 and, therefore, the Yahoo Group will be an opportunity for Oracle administrators and developers from opposite parts of the globe to discuss performance monitoring in the same forum. It’s a happy coincidence that the word “Sangam” means a confluence—a coming together—in the Sanskrit language of India.
Mark Rittman’s AIOUG trip report
Hi Iggy,
I would also recommend reading this OTN thread on the same topic.
Iggy,
Would you mind sharing how one can see the sequence of processing in action as you mentioned (aka “evidence”) ?
Hi, Narendra,
You can use the raw trace data produced by SQL_TRACE or the 10046 event to prove the sequence of processing in a query plan.
However, there’s a big hint in the A-Time column (actual cumulative elapsed times) in the above query plan. As you can see in the tree-structured graphic on page 7 of the white paper, the cumulative elapsed times increase correctly when the operations are performed in the claimed sequence:
5: build an in-memory hash table
7: build a second in-memory hash table
12, 14, 13, 11, 15, 10 , 9: build a third in-memory hash table
16: scan the fact table
8: probe the first hash table
6: probe the second hash table
4: probe the third hash table
3: filter the remaining records
2: group the remaining records
1: sort the remaining records
Also, the explanation makes perfect sense: three in-memory hash tables are built and then the records in the large fact table are checked using these hash tables. Records are sent to the FILTER, GROUP BY, and SORT operations only if they pass all three checks.
Regards,
Iggy
Minor issues I’ve found in the paper:
1) wrong database version:
DBMS_XPLAN.DISPLAY is available in 9iR2
2) SQL_TRACE is deprecated since 10gR2 and it’s good idea to remind about this
3) it’s not clear which Oracle version is supposed to be used throughout the paper
Overall, this is a great article, thanks for sharing it.
Thanks, Timur. Great catches.
Hi Iggy,
Thanks a lot for sharing the paper. Also, I was not aware that “A-Time” reports cumulative elapsed time. Will keep that in mind for future.
You managed to put a Kelly instead of a Kerry in the paper.
Thanks, Joel. I’ll correct it.
I’ve written a simpler version of the SQL query to generate Graphviz commands. It’s available at http://iggyfernandez.wordpress.com/2010/11/26/explaining-the-explain-plan-using-pictures/.