Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans

Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans:

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:


SQL ID – default null, means the last SQL statement executed in this session

CURSOR_CHILD_NO – default 0
FORMAT – Controls the level of details that will be displayed in the execution plan, default TYPICAL.


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 ,t1WHERE b.object_id = t2.object_idAND   b.data_object_id = t1.data_object_idAND   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) FROMbig_table b, t2 ,t1 WHERE b.object_id = t2.object_id ANDb.data_object_id = t1.data_object_id AND t1.object_type=’TABLE’ ANDt2.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 FORMATparameter. 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) FROMbig_table b, t2 ,t1 WHERE b.object_id = t2.object_id ANDb.data_object_id = t1.data_object_id AND t1.object_type=’TABLE’ ANDt2.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 WHEREb.object_id = t2.object_id AND b.data_object_id = t1.data_object_id ANDt1.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’x
  • September 25, 2018 | 15 views
  • Comments