Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans

In last week’s post I described how to use SQL Monitor to determine what is happening during the execution of long running SQL statements. Shortly after the post went up, I got some requests on both social media and via the blog comments asking, “If it is possible to get the same information from a traditional text based execution plan, as not everyone has access to SQL Monitor?”.

The answer is yes, it is possible to see a lot of the information showed in SQL Monitor by viewing the execution plan via the DBMS_XPLAN.DISPLAY_CURSOR function. In order to call this function you will need SELECT or READ privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN, otherwise you’ll get an error message.

The DBMS_XPLAN.DISPLAY_CURSOR function takes three parameters:

  1. SQL ID – default null, means the last SQL statement executed in this session
  2. CURSOR_CHILD_NO – default 0
  3. FORMAT – Controls the level of details that will be displayed in the execution plan, default TYPICAL.

The video below demonstrates how you can use the FORMAT parameter within the DBMS_XPLAN.DISPLAY_CURSOR function to show you information about a what’s happened during an execution plan including the bind variable values used, the actual number of rows returned by each step, and how much time was spent on each step.

Under the video you will find all of the commands used, so you can cut and paste them easily.

How do I see the actual number of rows and elapse time for each step in the plan?

You will need to do two things in order to see the actual number of rows:

  1. Add the GATHER_PLAN_STATISTICS hint to the SQL statement
  2. Setting the FORMAT parameter of DBMS_XPLAN.DISPLAY_CURSOR to ‘ALLSTATS LAST’
SELECT /*+ GATHER_PLAN_STATISTICS */ t2.owner, SUM(b.object_id)
FROM  big_table b, t2 ,t1
WHERE b.object_id = t2.object_id
AND   b.data_object_id = t1.data_object_id
AND   t1.object_type='TABLE'
AND   t2.owner ='SSB'
GROUP BY t2.owner;
 
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  d3z7q78jtgxm2, child NUMBER 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ t2.owner, SUM(b.object_id) FROM
big_table b, t2 ,t1 WHERE b.object_id = t2.object_id AND
b.data_object_id = t1.data_object_id AND t1.object_type='TABLE' AND
t2.owner ='SSB' GROUP BY t2.owner
 
Plan hash VALUE: 1122440390
 
-----------------------------------------------------------------------------------------------------------------------
 
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-TIME   | Buffers |  OMem |  1Mem | Used-Mem |
 
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |      1 |00:00:00.25 |   50110 |       |       |          |
|   1 |  SORT GROUP BY NOSORT|           |      1 |    256 |      1 |00:00:00.25 |   50110 |       |       |          |
|*  2 |   HASH JOIN          |           |      1 |     19 |    256 |00:00:00.91 |   50110 |  2440K|  2440K| 1474K (0)|    
|*  3 |    TABLE ACCESS FULL | T1        |      1 |    592 |    592 |00:00:00.01 |     146 |       |       |          |
|*  4 |    HASH JOIN         |           |      1 |   1599 |   3072 |00:00:06.31 |   49964 |  2293K|  2293K| 1590K (0)|
|*  5 |     TABLE ACCESS FULL| T2        |      1 |    102 |    102 |00:00:00.01 |       9 |       |       |          |
|*  6 |     TABLE ACCESS FULL| BIG_TABLE |      1 |    298K|    298K|00:00:00.61 |   49955 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified BY operation id):
---------------------------------------------------
2 - access("B"."DATA_OBJECT_ID"="T1"."DATA_OBJECT_ID")
3 - filter(("T1"."DATA_OBJECT_ID" IS NOT NULL AND "T1"."OBJECT_TYPE"='TABLE'))
4 - access("B"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T2"."OWNER"='SSB')
6 - filter("B"."DATA_OBJECT_ID" IS NOT NULL)

NOTE: If you can’t modify the SQL statement to add the hint, it is possible to add the hint via a SQL Profile as demonstrated by Kerry Osborne.

How do I get the COST of the plan to be displayed when I specify  ALLSTATS LAST for the FORMAT parameter?

When you use ‘ALLSTATS LAST’ for the FORMAT parameter, the estimates number of bytes (BYTES) and the estimated cost for the plan (COST) are not displayed by default. You can easily have these columns displayed by adding additional predicates to the FORMAT parameter. Each additional predicate is proceeded with ‘+’ sign.

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'d3z7q78jtgxm2',
                                      format=>'ALLSTATS LAST +cost +bytes')); 
PLAN_TABLE_OUTPUT 
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d3z7q78jtgxm2, child NUMBER 1 
------------------------------------- 
SELECT /*+ GATHER_PLAN_STATISTICS */ t2.owner, SUM(b.object_id) 
FROM big_table b, t2 ,t1 
WHERE b.object_id = t2.object_id 
AND b.data_object_id = t1.data_object_id 
AND t1.object_type='TABLE' 
AND t2.owner ='SSB' 
GROUP BY t2.owner 
 
Plan hash VALUE: 1122440390 
-------------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation            | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-TIME   | Buffers |  OMem |  1Mem | Used-Mem | 
-------------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT     |           |      1 |        |       | 15855 (100)|      1 |00:00:00.23 |   50110 |       |       |          | 
|   1 |  SORT GROUP BY NOSORT|           |      1 |     19 |   475 | 15855   (1)|      1 |00:00:00.23 |   50110 |       |       |          | 
|*  2 |   HASH JOIN          |           |      1 |     19 |   475 | 15855   (1)|    256 |00:00:00.91 |   50110 |  2440K|  2440K| 1474K (0)| 
|*  3 |    TABLE ACCESS FULL | T1        |      1 |    113 |  1017 |  2222   (1)|    592 |00:00:00.01 |     146 |       |       |          | 
|*  4 |    HASH JOIN         |           |      1 |   1599 | 25584 | 13634   (1)|   3072 |00:00:05.65 |   49964 |  2293K|  2293K| 1567K (0)| 
|*  5 |     TABLE ACCESS FULL| T2        |      1 |    102 |   918 |    13   (0)|    102 |00:00:00.01 |       9 |       |       |          | 
|*  6 |     TABLE ACCESS FULL| BIG_TABLE |      1 |    298K|  2037K| 13620   (1)|    298K|00:00:00.73 |   49955 |       |       |          | 
-------------------------------------------------------------------------------------------------------------------------------------------- 
Predicate Information (identified BY operation id): 
--------------------------------------------------- 
2 - access("B"."DATA_OBJECT_ID"="T1"."DATA_OBJECT_ID") 
3 - filter(("T1"."DATA_OBJECT_ID" IS NOT NULL AND "T1"."OBJECT_TYPE"='TABLE')) 
4 - access("B"."OBJECT_ID"="T2"."OBJECT_ID") 5 - filter("T2"."OWNER"='SSB') 
6 - filter("B"."DATA_OBJECT_ID" IS NOT NULL)

NOTE: You can find a list of additional predicates that can be added in the Oracle Database PL/SQL Packages and Types Reference guide.

It’s also possible to remove columns from the plan table or other information from the output by adding additional predicates to the FORMAT parameter proceeded with a ‘-‘ sign. For example, the command below removes the E-Rows column and predicate information from below the plan.

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'d3z7q78jtgxm2', 
                                      format=>'ALLSTATS LAST -rows –predicate'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  d3z7q78jtgxm2, child NUMBER 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ t2.owner, SUM(b.object_id) FROM
big_table b, t2 ,t1 WHERE b.object_id = t2.object_id AND
b.data_object_id = t1.data_object_id AND t1.object_type='TABLE' AND
t2.owner ='SSB' GROUP BY t2.owner
 
Plan hash VALUE: 1122440390
--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | A-Rows |   A-TIME   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |      1 |00:00:00.25 |   50110 |       |       |          |
|   1 |  SORT GROUP BY NOSORT|           |      1 |      1 |00:00:00.25 |   50110 |       |       |          |
|*  2 |   HASH JOIN          |           |      1 |    256 |00:00:00.91 |   50110 |  2440K|  2440K| 1474K (0)|
|*  3 |    TABLE ACCESS FULL | T1        |      1 |    592 |00:00:00.01 |     146 |       |       |          |
|*  4 |    HASH JOIN         |           |      1 |   3072 |00:00:06.31 |   49964 |  2293K|  2293K| 1590K (0)|
|*  5 |     TABLE ACCESS FULL| T2        |      1 |    102 |00:00:00.01 |       9 |       |       |          |
|*  6 |     TABLE ACCESS FULL| BIG_TABLE |      1 |    298K|00:00:00.61 |   49955 |       |       |          |
--------------------------------------------------------------------------------------------------------------

How do I determine the join order if I can’t see the plan tree with DBMS_XPLAN.DISPLAY_CURSOR?

In the SQL Monitor video I showed you how I use the plan tree to determine the join order but with DBMS_XPLAN.DISPLAY_CURSOR the execution plan is displayed only as table. Although it is possible to determine the join order by looking at the indentation of the tables in the operation column, I find it far easier to use to use the FORMAT parameter of DBMS_XPLAN.DISPLAY_CURSOR to display the outline information for the plan, which will contain the join order.

Adding the ‘+outline’ predicate to the FORMAT parameter will return the outline (full set of hints to reproduce the statement) for the SQL statement.

 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'d3z7q78jtgxm2',
                                       format=>'ALLSTATS LAST +outline'));
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-TIME   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |      1 |00:00:00.23 |   50110 |       |       |          |
|   1 |  SORT GROUP BY NOSORT|           |      1 |     19 |      1 |00:00:00.23 |   50110 |       |       |          |
|*  2 |   HASH JOIN          |           |      1 |     19 |    256 |00:00:00.91 |   50110 |  2440K|  2440K| 1474K (0)|
|*  3 |    TABLE ACCESS FULL | T1        |      1 |    113 |    592 |00:00:00.01 |     146 |       |       |          |
|*  4 |    HASH JOIN         |           |      1 |   1599 |   3072 |00:00:05.65 |   49964 |  2293K|  2293K| 1567K (0)|
|*  5 |     TABLE ACCESS FULL| T2        |      1 |    102 |    102 |00:00:00.01 |       9 |       |       |          |
|*  6 |     TABLE ACCESS FULL| BIG_TABLE |      1 |    298K|    298K|00:00:00.73 |   49955 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------
 
Outline Data
-----------
/*+
  BEGIN_OUTLINE_DATA
  IGNORE_OPTIM_EMBEDDED_HINTS
  OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
  DB_VERSION('12.1.0.2')
  ALL_ROWS
  OUTLINE_LEAF(@"SEL$1")
  FULL(@"SEL$1""T2"@"SEL$1")
  FULL(@"SEL$1""B"@"SEL$1")
  FULL(@"SEL$1""T1"@"SEL$1")
  LEADING(@"SEL$1" "T2"@"SEL$1" "B"@"SEL$1" "T1"@"SEL$1")
  USE_HASH(@"SEL$1""B"@"SEL$1")
  USE_HASH(@"SEL$1""T1"@"SEL$1")
  SWAP_JOIN_INPUTS(@"SEL$1""T1"@"SEL$1")
  END_OUTLINE_DATA
*/

In the outline information, look for the line that begins with the word LEADING. This line shows the join order for this query. In this example you see “T2”, then “B”, then “T1” referenced on this line; these are the aliases for each of the tables used in our query T2, BIG_TABLE and T1.

How do I see what bind variable values were used for this execution?

In SQL Monitor the bind variable values used to generate an execution plan are shown via a link in the upper right hand corner of the screen.

To show the same information with DBMS_XPLAN.DISPLAY_CURSOR you simply add a ‘+peeked_binds’ predicate to the FORMAT parameter.

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'+PEEKED_BINDS'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID         1427awr1f8qkf, child NUMBER 0
-------------------------------------
SELECT t2.owner, SUM(b.object_id) FROM big_table b, t2 ,t1 WHERE
b.object_id = t2.object_id AND b.data_object_id = t1.data_object_id AND
t1.object_type= :obj_type AND t2.owner = :own GROUP BY t2.owner
 
Plan hash VALUE: 1122440390
 
----------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| TIME     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       | 15855 (100)|          |
|   1 |  SORT GROUP BY NOSORT|             |    19 |   475 | 15855   (1)| 00:00:01 |
|*  2 |   HASH JOIN          |             |    19 |   475 | 15855   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL | T1          |   113 |  1017 |  2222   (1)| 00:00:01 |
|*  4 |    HASH JOIN         |             |  1599 | 25584 | 13634   (1)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T2          |   102 |   918 |    13   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| BIG_TABLE   |   298K|  2037K| 13620   (1)| 00:00:01 |
----------------------------------------------------------------------------------
 
Peeked Binds (identified BY position):
--------------------------------------
1 - :OBJ_TYPE (VARCHAR2(30), CSID=873): 'TABLE'
2 - :OWN (VARCHAR2(30), CSID=873): 'SSB'
This entry was posted in Optimizer, Syntax and tagged , , , . Bookmark the permalink.

7 Responses to Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans

  1. Bala says:

    Hi Maria,

    Thanks for the article, I have a long running SQL which has complete execution and my statisics_level is set to typical. Is there any way I can get information about the actual rows returned.

    Thanks,
    Bala

    • Maria Colgan says:

      Hi Bala,

      If the statement is still in the SQL Monitor buffer, than you will be able to see the actual number of rows on the report as SQL Monitor always collects that information and stores it.

      However, if you are using DBMS_XPLAN.DISPLAY_CURSOR, then the only way to have Oracle persist the execution statistics is by supplying the GATHER_PLAN_STATISTICS hint or by setting the statistics_level to ALL.

      Thanks,
      Maria

  2. Olivier BERNHARD says:

    Hi Maria,
    if GATHER_PLAN_STATISTICS hint / statistics_level to ALL is set, will different consecutive executions of DBMS_XPLAN.DISPLAY_CURSOR display the evolution of statistics ? I have noticed that consecutive errorstack dumps (to get the execution plan and metrics) do not show metrics changing over time even if the statement is still running.
    Thanks
    Olivier.

    • Maria Colgan says:

      Hi Olivier,

      Yes, the DBMS_XPLAN.DISPLAY_CURSOR function will show the evolution of statistics during the execution of a long running query assuming you know the SQL_ID and the child cursor number.

      It’s not as simple or as elegant as using a SQL Monitor report but it is possible, assuming you are willing to do a little math to make this work.

      In a separate session, you will need to execute select * from table(dbms_xplan.display_cursor(sql_id=>’YOUR_SQL_ID’, cursor_child_no=>YOUR_CHILD_NO, format=>’ALLSTATS ALL’));

      Make note of all of the statistics you are interested in.

      By rerunning this command throughout the execution you will be able to tell which step of the plan is currently being executed by watching which A-Rows entry is currently increasing. The difference between the original run of the command and the current value will tell you the actual number of rows for this execution.

      Thanks,
      Maria

  3. William Laverde says:

    Hi Maria, Nice blog !!!
    If I have a sqlid with 5 child cursors how can I know which one is my cursor running via Display Cursor function

    • Maria Colgan says:

      Hi William,

      By default, if you do not supply any parameter values to the DBMS_XPLAN.DISPLAY_CURSOR function, it will display the child cursor used by the previously executed SQL statement in your session. You will be able to tell which child cursor it is, by looking for the SQL_ID and CHILD NUMBER just above the plan table.

      If however, you supply just the SQL_ID then you typically child cursor 0.

      If you know the child number of the plan you want to display, you can supply it as the second parameter to the DBMS_XPLAN.DISPLAY_CURSOR function.

      Thanks,
      Marua

  4. Sam says:

    Hi Maria,

    Thanks for the useful information.

    Is there a way where I could hide (using the minus sign) the whole SQL Query Text that gets displayed at the top of every EXECUTION PLAN if I have provided exactly the SQL_ID and CURSOR_CHILD_NO parameter values?

    It would help save the screen real-estate because I know exactly which SQL query text it is, so no need to show it again.

    I have the following:-

    SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => ‘8t6w2p8snazn9’, cursor_child_no => 0, FORMAT => ‘TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE’));

    I wish there was an option like “-SQLTEXT” or “-QUERY” so that I start seeing the results starting from the line that says “Plan hash value: ……”

Leave a Reply

Your email address will not be published. Required fields are marked *