Scripts for showing execution plans via plain SQL and also in Oracle 9i

Tanel Poder

2009-05-26

Hi all,

Here are few scripts which allow you to query SQL execution plans and their execution statistics out from V$SQL_PLAN and V$SQL_PLAN_STATISTICS yourself.

Why would you want to do this as there’s the DBMS_XPLAN.DISPLAY_CURSOR() function in Oracle 10g?

Well, my scripts work also on Oracle 9.2. Also they give you better detail (and flexibility) compared to DBMS_XPLAN.DISPLAY_CURSOR.

Of course in 9i there’s the DBMS_XPLAN.DISPLAY function which you can use in conjuction with EXPLAIN PLAN FOR command, but that approach has problems. EXPLAIN PLAN treats all bind variables as varchar2 datatype, possibly causing the reported execution plan to be different from reality. Also, if you have a long running report from last night and you run explain plan today morning, then if optimizer statistics have changed meanwhile, the explain plan command might come up with a different execution plan again, which is different from reality.

So that’s why in 10g we have the DBMS_XPLAN.DISPLAY_CURSOR which goes directly to required library cache child cursor and extracts (unparses) the execution plan from there. The function uses V$SQL_PLAN% views as its data source. And guess what – these views are there in version 9.2 already! And thats’ where my scripts come in:

Here’s an example. Let’s set statistics_level=all so we get rowsource level execution stats for the cursor (note that this parameter makes your query consume much more CPU so it should only be used at session level for troubleshooting a specific performance issue):

SQL> alter session set statistics_level = all;

Session altered.

SQL> select count(*) from all_users;

  COUNT(*)
----------
        36

I know the hash value of this query, so lets report its execution plan, directly from library cache. This is the REAL execution plan inside that child cursor, not some estimate like EXPLAIN PLAN command gives:

SQL> @xm 3416239794 %

Ch Pr   Op                                                         Objcect                        Optimizer Optim rows Optim bytes Optimizer
ld ed   ID Operation                                               Name                                Cost  from step   from step Mode
-- -- ---- ------------------------------------------------------- ------------------------------ --------- ---------- ----------- ----------
 0       0 SELECT STATEMENT                                                                              14                        ALL_ROWS
         1  SORT AGGREGATE                                                                                           1          15
   A     2   HASH JOIN                                                                                   14         36         540
   A     3    HASH JOIN                                                                                   9         36         432
         4     TABLE ACCESS FULL                                   TS$                                    5         13          39
    F    5     TABLE ACCESS FULL                                   USER$                                  3         36         324
         6    TABLE ACCESS FULL                                    TS$                                    5         13          39

7 rows selected.

Ch Op
ld ID     Predicate Information (identified by operation id):
-- ------ ----------------------------------------------------------------------------------------------------
 0     2  - access("U"."TEMPTS#"="TTS"."TS#")
       3  - access("U"."DATATS#"="DTS"."TS#")
       5  - filter("U"."TYPE#"=1)

XM means eXplain from Memory :)

As we had enabled rowsource level execution stats collection, we can use another script to report the response time and resource consumption at execution plan line level (if you’re interested how Oracle achieves this, check the last part of this blog post)

SQL> @xmsh 3416239794 %

SQL hash value:          3416239794    Cursor address:                  00000003A41E5290    |   Statement first parsed at: 2009-05-26/23:02:16  |  200 seconds ago

 Ch Pr   Op                                                         Object                           ms spent in     Estimated Real #rows  Rowsource Consistent    Current   Physical   Physical    Optimizer
 ld ed   ID Operation                                               Name                               operation   output rows   returned     starts       gets       gets      reads     writes         Cost
--- -- ---- ------------------------------------------------------- ------------------------------ ------------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
  0       0 SELECT STATEMENT                                                                                                                                                                               14
          1  SORT AGGREGATE                                                                                 2.49             1          1          1         42          0          0          0
    A     2   HASH JOIN                                                                                     2.43            36         36          1         42          0          0          0           14
    A     3    HASH JOIN                                                                                    1.87            36         36          1         24          0          0          0            9
          4     TABLE ACCESS FULL                                   TS$                                      .12            13         13          1         18          0          0          0            5
     F    5     TABLE ACCESS FULL                                   USER$                                    .06            36         36          1          6          0          0          0            3
          6    TABLE ACCESS FULL                                    TS$                                      .05            13         13          1         18          0          0          0            5

 Ch Op
 ld ID     Predicate Information (identified by operation id):
--- ------ ----------------------------------------------------------------------------------------------------
  0     2  - access("U"."TEMPTS#"="TTS"."TS#")
        3  - access("U"."DATATS#"="DTS"."TS#")
        5  - filter("U"."TYPE#"=1)

XMSH means eXplain from Memory with Statistics by Hash value (well it did make sense to me when I wrote the script, you can rename it to something else if you like :)

You may need to make your browser window pretty wide to see the full output to see all the columns reported. I normally run sqlplus in Windows cmd.exe and make the screen buffer wide, so the output doesn’t wrap.

If you want to read what these output columns mean, read my presentation about how SQL plan execution works.

As I mentioned, the above scripts run also on Oracle 9.2 (that’s why I wrote them years ago).

Also there’s one more script, xms.sql which doesn’t take any parameters and shows you the execution plan of the latest SQL executed in current session. It uses v$session.prev_hash_value for determining this. However on Oracle 9.2 the prev_hash_value seems to be incorrectly reset and my script would display you wrong SQL statements execution plan. In 10g onwards Oracle has fixed this problem though. In 9i you need to identify the hash value of the statement and use xmsh.sql instead.

In 10g the xms works ok:

SQL> select count(*) from all_tables;

  COUNT(*)
----------
      1739

1 row selected.

SQL> @xms

SQL hash value:          3565202713    Cursor address:                  00000003A4311930    |   Statement first parsed at: 2009-05-26/23:05:59  |  2 seconds ago

 Ch Pr   Op                                          Object                             ms spent   Estimated Real #rows  Rowsource Consistent    Current   Physical   Physical  Optimizer
 ld ed   ID Operation                                Name                                 in op. output rows   returned     starts       gets       gets      reads     writes       Cost
--- -- ---- ---------------------------------------- ------------------------------ ------------ ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  0       0 SELECT STATEMENT                                                                                                                                                          558
          1  SORT AGGREGATE                                                               418.63           1          1          1       4559          0          1          0
     F    2   FILTER                                                                      416.78                   1739          1       4559          0          1          0
    A     3    HASH JOIN RIGHT OUTER                                                      387.23        1750       1739          1       2568          0          0          0        558
          4     TABLE ACCESS FULL                    USER$                                   .12          74         74          1          6          0          0          0          3
    A     5     HASH JOIN OUTER                                                           377.55        1750       1739          1       2562          0          0          0        555
          6      NESTED LOOPS OUTER                                                       225.00        1750       1739          1       1913          0          0          0        410
    A     7       HASH JOIN                                                               218.02        1750       1739          1       1819          0          0          0        410
          8        TABLE ACCESS FULL                 USER$                                   .11          74         74          1          6          0          0          0          3
    A     9        HASH JOIN                                                              206.61        1750       1739          1       1813          0          0          0        406
    A    10         HASH JOIN RIGHT OUTER                                                  41.63        1750       1750          1       1164          0          0          0        260
         11          TABLE ACCESS FULL               SEG$                                   5.28        5249       5249          1        156          0          0          0         35
    A    12          HASH JOIN                                                             17.97        1750       1750          1       1008          0          0          0        224
         13           MERGE JOIN CARTESIAN                                                  6.62          13         13          1         18          0          0          0          6
    A    14            HASH JOIN                                                            6.40           1          1          1          0          0          0          0          1
     F   15             FIXED TABLE FULL             X$KSPPI                                 .95           1          1          1          0          0          0          0          0
         16             FIXED TABLE FULL             X$KSPPCV                               1.45        1440       1440          1          0          0          0          0          0
         17            BUFFER SORT                                                           .20          13         13          1         18          0          0          0          6
         18             TABLE ACCESS FULL            TS$                                     .09          13         13          1         18          0          0          0          5
     F   19           TABLE ACCESS FULL              TAB$                                   3.55        1750       1750          1        990          0          0          0        218
     F   20         TABLE ACCESS FULL                OBJ$                                  55.26       53718      53716          1        649          0          0          0        146
    A    21       INDEX UNIQUE SCAN                  I_OBJ1                                 3.36           1         92       1739         94          0          0          0          0
         22      TABLE ACCESS FULL                   OBJ$                                   1.26       53728      53727          1        649          0          0          0        144
         23    NESTED LOOPS                                                                25.93           1         69        995       1991          0          1          0          2
    A    24     INDEX RANGE SCAN                     I_OBJAUTH1                            22.25           1        149        995       1991          0          1          0          2
     F   25     FIXED TABLE FULL                     X$KZSRO                                 .91           1         69        149          0          0          0          0          0
     F   26      FIXED TABLE FULL                    X$KZSPR                                 .05          14          1          1          0          0          0          0          0

 Ch Op
 ld ID     Predicate Information (identified by operation id):
--- ------ ----------------------------------------------------------------------------------------------------
  0     2  - filter(("O"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR  IS NOT NULL))
        3  - access("CX"."OWNER#"="CU"."USER#")
        5  - access("T"."DATAOBJ#"="CX"."OBJ#")
        7  - access("O"."OWNER#"="U"."USER#")
        9  - access("O"."OBJ#"="T"."OBJ#")
       10  - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND "T"."TS#"="S"."TS#")
       12  - access("T"."TS#"="TS"."TS#")
       14  - access("KSPPI"."INDX"="KSPPCV"."INDX")
       15  - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
       19  - filter(BITAND("T"."PROPERTY",1)=0)
       20  - filter(BITAND("O"."FLAGS",128)=0)
       21  - access("T"."BOBJ#"="CO"."OBJ#")
       24  - access("OA"."OBJ#"=:B1)
       25  - filter("GRANTEE#"="KZSROROL")
       26  - filter((((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49)
           OR (-"KZSPRPRV")=(-50)) AND "INST_ID"=USERENV('INSTANCE')))

I haven’t had a need to adjust the scripts to fix the problem what Jonathan Lewis has described earlier, but it should be easily doable (just need to make sure that the connect by recursive loop is done against a materialized resultset of v$sql_plan, not the view itself to avoid excessive latching).

The scripts are downloadable from here:

Update:

If you are on 10g and want to know how to convert SQL_ID’s to hash values for use with my XM scripts, read this:


  1. Updated video course material to be announced soon:
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training.
    Check the current versions out here!
  2. Get randomly timed updates by email or follow Social/RSS