Execution plans in Mumbai

The upcoming now available version of Mumbai will have has “execution plans on steroids” and graphical execution plans.

To create an execution plan you can click on the tree-like icon in the console window:

Here you have the option to either use EXPLAIN PLAN or actually execute (and optionally fetch all rows) and then use DBMS_XPLAN.DISPLAY_CURSOR to generate the execution plan. You can also temporarily set the parameters STATISTICS_LEVEL and _ROW_SOURCE_SAMPFREQ to generate more accurate timing values (see Jonathan Lewis’ “Heisenberg” post for a discussion of the implications.

Further, you can decide if you want to show the execution plan in the lower part of the console window or in a separate window. Only the later option will let you see the graphical tree representation for the execution plan.

Once you click “Ok” the execution plan will show up. For the example SQL in the screenshot above, it might look like this:

SQL_ID 8qrpwfksu4c9d, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics mumbai_86C00594 */ * FROM dba_objects Plan hash value: 2125401064 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Pid | Ord | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | OMem | 1Mem | Used-Mem | A-Time Self |A-Ti S-Graph |TCF Graph |E-Rows*Sta| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 1 | 0 | 13 | VIEW | DBA_OBJECTS | 1 | 10961 | 1894K| 168 (2)| 0 |00:00:00.01 | | | | 00:00:00.00 | @@ | | 10961 | | 2 | 1 | 12 | UNION-ALL | | 1 | | | | 0 |00:00:00.01 | | | | 00:00:00.00 | @@@@ | | | |* 3 | 2 | 6 | FILTER | | 1 | | | | 0 |00:00:00.01 | | | | 00:00:00.00 | @@ | | | |* 4 | 3 | 3 | HASH JOIN | | 1 | 12957 | 1024K| 163 (1)| 0 |00:00:00.01 | 990K| 990K| | 00:00:00.00 | @@@@ | | 12957 | | 5 | 4 | 1 | TABLE ACCESS FULL | USER$ | 1 | 44 | 660 | 4 (0)| 0 |00:00:00.01 | | | | 00:00:00.00 | @@ | | 44 | |* 6 | 4 | 2 | TABLE ACCESS FULL | OBJ$ | 0 | 12957 | 835K| 159 (1)| 0 |00:00:00.01 | | | | 00:00:00.00 | | | 0 | |* 7 | 3 | 5 | TABLE ACCESS BY INDEX ROWID| IND$ | 0 | 1 | 8 | 1 (0)| 0 |00:00:00.01 | | | | 00:00:00.00 | | | 0 | |* 8 | 7 | 4 | INDEX UNIQUE SCAN | I_IND1 | 0 | 1 | | 1 (0)| 0 |00:00:00.01 | | | | 00:00:00.00 | | | 0 | | 9 | 2 | 11 | MERGE JOIN | | 0 | 10 | 550 | 5 (20)| 0 |00:00:00.01 | | | | 00:00:00.00 | | | 0 | | 10 | 9 | 8 | TABLE ACCESS CLUSTER | USER$ | 0 | 44 | 660 | 2 (0)| 0 |00:00:00.01 | | | | 00:00:00.00 | | | 0 | | 11 | 10 | 7 | INDEX FULL SCAN | I_USER# | 0 | 1 | | 1 (0)| 0 |00:00:00.01 | | | | 00:00:00.00 | | | 0 | |* 12 | 9 | 10 | SORT JOIN | | 0 | 10 | 400 | 3 (34)| 0 |00:00:00.01 | 73728 | 73728 | | 00:00:00.00 | | | 0 | | 13 | 12 | 9 | TABLE ACCESS FULL | LINK$ | 0 | 10 | 400 | 2 (0)| 0 |00:00:00.01 | | | | 00:00:00.00 | | | 0 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1))) 4 - access("O"."OWNER#"="U"."USER#") 6 - filter(("O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0 AND "O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_')) 7 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)) 8 - access("I"."OBJ#"=:B1) 12 - access("L"."OWNER#"="U"."USER#") filter("L"."OWNER#"="U"."USER#")

Notice the additional columns like PID, Ord, A-Time Self, A-Ti S-Graph, TCF Graph and E-Rows*Sta (not available when you used EXPLAIN PLAN to generate the plan).

These additional columns are extremely helpful for reading execution plan and spotting problematic row source operations. The underlying code is based on Randolf Geist’s terrific xplan_extended_display_cursor script. For a longer explanation and a documentation of the additional columns please see his blog post.

Besides the textual execution plan, you can see a graphical tree representation for your execution plan:

A few hints for using this graphical execution plan:

  • Use the navigation tree on the upper right side to navigate in large execution plans. Clicking on a node in the navigation tree will select the node in the main tree.
  • Filter and access predicates are indicated by little icons on the lower right of the nodes. The hint for a node will show the actual predicates.
  • A node might be marked with one or two colored dots of five different colors. These dots are based on the TCF, A-Time/Bufs/Reads/Write Self Graph columns in the execution plan and let you quickly spot possible problematic row source nodes.
  • Which of the colored dots is shown can be set by the buttons in the toolbar.
  • You can quickly see the details for an object referenced in a row source by right clicking on the node (see screenshot below)

On the toolbar are additional buttons for rotating the tree and for a condensed view which helps with large trees and looks like this:

Hope you like it and that it helps in your performance analysis quests.

This entry was posted in Mumbai. Bookmark the permalink.

4 Responses to Execution plans in Mumbai

  1. Andreas Buckenhofer says:

    I’m looking forward to the new version! Thanks for your work (I also liked your DOAG session).

  2. Noons says:

    Excellent! Looking forward to the new version. Mumbai has been a great help for us, thanks for making it available.

  3. Albrecht Ritter says:

    Hi,

    i created a user mumbai42. but nevertheless it’s not possible to view alert.log
    Error message:
    This functionality is only available when the Mumbai42 schama is available in the databas

    • Hello Albrecht,

      have you created a user only or have you created the complete Mumbai42 schema from within the Mumbai42 interface? There is a button “Create Schema” on the Connection tab in the main window. This will ask for sys user’s password, connect as sys, create the user/schema Mumbai42 and all necessary schema objects and disconnect. After that the extended functionality, like alert.log retrieval should be available.

      Cheers,
      Marcus

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