Home > DBA, Oracle, SQL > India Trip Report: The Oracle Documentation is Always Correct NOT

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

Show Me a Picture!

Categories: DBA, Oracle, SQL
  1. September 6, 2010 at 12:33 am

    Hi Iggy,

    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.

    I would also recommend reading this OTN thread on the same topic.

  2. Narendra
    September 6, 2010 at 1:10 am

    Iggy,

    Would you mind sharing how one can see the sequence of processing in action as you mentioned (aka “evidence”) ?

  3. Iggy Fernandez
    September 6, 2010 at 2:03 am

    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

  4. September 6, 2010 at 3:22 am

    Minor issues I’ve found in the paper:
    1) wrong database version:

    Prior to Oracle Database 10g , the plan had to be formatted manually using CONNECT BY. Beginning with Oracle Database 10g, you can use DBMS_XPLAN to format the plan

    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.

  5. Iggy Fernandez
    September 6, 2010 at 3:54 am

    Thanks, Timur. Great catches.

  6. Narendra
    September 6, 2010 at 7:29 am

    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.

  7. joel garry
    November 10, 2010 at 4:49 pm

    You managed to put a Kelly instead of a Kerry in the paper.

  8. Iggy Fernandez
    November 26, 2010 at 2:29 pm

    Thanks, Joel. I’ll correct it.

  9. Iggy Fernandez
    November 26, 2010 at 2:31 pm

    I’ve written a simpler version of the SQL query to generate Graphviz commands. It’s available at https://iggyfernandez.wordpress.com/2010/11/26/explaining-the-explain-plan-using-pictures/.

  1. September 12, 2010 at 4:27 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: