DBMS XPLAN

From Robs_Wiki
Jump to: navigation, search

Introduction

The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of the package. You can use different sources as to where the information needs to come from, for example last cursor information, or AWR.

Some required settings for this to work

  • Make sure the user that executes this has the select privilege on v$sqp_plan. (SQL> grant select on v_$sql_plan to <user>;)
  • Also make sure that serveroutput is turned off. (SQL> set serverput off)

Otherwise you will end up with this annoying error: 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Use DBMS_XPLAN.DISPLAY_CURSOR

This procedure uses the last statement in the session as input. See examples below.

DBMS_XPLAN.display_cursor with SQL_ID

prompt Copy and paste SQL_ID and CHILD_NUMBER from results above
var name varchar2(30)
undefine sql_id
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'));

DBMS_XPLAN.display_cursor with defaults

Assume we run the following query in the database: 

SQL> select count(id) from robla.gg_tbl_custom_heartbeat_x1 where colour = 'GREEN';

 COUNT(ID)
----------
      4834

We can now generate the plan using the default settings as follows: 

SQL> set linesize 200
set pagesize 0
SQL> select * from table(dbms_xplan.display_cursor());
SQL_ID  b39z0f0g3czx9, child number 0
-------------------------------------
select count(id) from robla.gg_tbl_custom_heartbeat_x1 where colour =
'GREEN'

Plan hash value: 1397283917

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |       |       |     9 (100)|          |
|   1 |  SORT AGGREGATE    |                            |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| GG_TBL_CUSTOM_HEARTBEAT_X1 |  2919 | 17514 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("COLOUR"='GREEN')

Use DBMS_XPLAN with the actual rows and the actual execution time

  • It is important to see if the Optimizer estimated rows are in line with the actual rows returned. In order to get the actual rows returned as well we have to add the hint /*+ GATHER_PLAN_STATISTICS / to the statement and we have to add the ALLSTATS LAST argument to our dbms_xplan statement:
  • When we use the ALLSTATS argument, we also see the actual execution time of every step in the plan (A-Time), which makes it easy to find the bottleneck in the plan.
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST +cost +bytes'));
SQL_ID  5w8kayvx9au1a, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(id) from
robla.gg_tbl_custom_heartbeat_x1 where colour = 'GREEN'

Plan hash value: 1397283917

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |      1 |        |       |     9 (100)|      1 |00:00:00.01 |      31 |
|   1 |  SORT AGGREGATE    |                            |      1 |      1 |     6 |            |      1 |00:00:00.01 |      31 |
|*  2 |   TABLE ACCESS FULL| GG_TBL_CUSTOM_HEARTBEAT_X1 |      1 |   2919 | 17514 |     9   (0)|   4849 |00:00:00.01 |      31 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COLOUR"='GREEN')


20 rows selected.

DBMS_XPLAN + OUTLINE

The outline data is the complete set of hints you need to reproduce the execution plan. But this outline data can also give us a good indication to the exact join order which may be hard to read in a large, complex plan. All you have to do is add the +outline parameter: 

SQL> set linesize 200
set pagesize 0
select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST +cost +bytes +outline'));

SQL_ID  5w8kayvx9au1a, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(id) from
robla.gg_tbl_custom_heartbeat_x1 where colour = 'GREEN'

Plan hash value: 1397283917

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |      1 |        |       |     9 (100)|      1 |00:00:00.01 |      31 |
|   1 |  SORT AGGREGATE    |                            |      1 |      1 |     6 |            |      1 |00:00:00.01 |      31 |
|*  2 |   TABLE ACCESS FULL| GG_TBL_CUSTOM_HEARTBEAT_X1 |      1 |   2919 | 17514 |     9   (0)|   4855 |00:00:00.01 |      31 |
--------------------------------------------------------------------------------------------------------------------------------

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" "GG_TBL_CUSTOM_HEARTBEAT_X1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COLOUR"='GREEN')


34 rows selected.

You can see that the Outline Data is displayed at the end.

Use DBMS_XPLAIN with showing BIND values

Again, with a single argument you can see the value of bind variables in the plan. Consider the following: 

SQL> exec :kleur := 'GREEN';

PL/SQL procedure successfully completed.

SQL> print kleur;
GREEN

SQL> select /*+ GATHER_PLAN_STATISTICS */ count(id) from robla.gg_tbl_custom_heartbeat_x1 where colour = :kleur;
      4863

select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive'));

SQL> SQL_ID  6gdj7vpdyk5uy, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(id) from
robla.gg_tbl_custom_heartbeat_x1 where colour = :kleur

Plan hash value: 1397283917

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |      1 |        |       |     9 (100)|      1 |00:00:00.01 |      31 |
|   1 |  SORT AGGREGATE    |                            |      1 |      1 |     6 |            |      1 |00:00:00.01 |      31 |
|*  2 |   TABLE ACCESS FULL| GG_TBL_CUSTOM_HEARTBEAT_X1 |      1 |   2919 | 17514 |     9   (0)|   4863 |00:00:00.01 |      31 |
--------------------------------------------------------------------------------------------------------------------------------

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" "GG_TBL_CUSTOM_HEARTBEAT_X1"@"SEL$1")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=178): 'GREEN'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COLOUR"=:KLEUR)


39 rows selected.

Use DBMS_XPLAN and show execution order

This is using a script from Adrian Billington perf_dbms_xplan_cursor.sql which also shows the execution order of the plan, which is quite usefull when you want to read the plan. 

SQL> @c:/rob/rob3.sql
old   5:         and     sql_id = '&v_xc_sql_id'
new   5:         and     sql_id = '6gdj7vpdyk5uy'
old   6:         and     child_number = to_number('&v_xc_child_no')
new   6:         and     child_number = to_number('         0')
old  31:         from   table(dbms_xplan.display_cursor('&v_xc_sql_id',to_number('&v_xc_child_no'),'&v_xc_format')) x
new  31:         from   table(dbms_xplan.display_cursor('6gdj7vpdyk5uy',to_number('         0'),'typical')) x
old  74:                                          '  - XPlan v&v_xc_version by Adrian Billington (http://www.oracle-developer.net)'
new  74:                                          '  - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)'

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6gdj7vpdyk5uy, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(id) from
robla.gg_tbl_custom_heartbeat_x1 where colour = :kleur

Plan hash value: 1397283917

-------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 |     |   3 | SELECT STATEMENT   |                            |       |       |     9 (100)|       |
|   1 |   0 |   2 |  SORT AGGREGATE    |                            |     1 |     6 |            |       |
|*  2 |   1 |   1 |   TABLE ACCESS FULL| GG_TBL_CUSTOM_HEARTBEAT_X1 |  2919 | 17514 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COLOUR"=:KLEUR)

About
------
  - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)


20 rows selected.

DBMS_XPLAN.DISPLAY_AWR

SQL> 
set linesize 200
set pagesize 0
select * from table(dbms_xplan.display_awr(sql_id=>'&sql_id', format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive'));
Retrieved from "http://rob.lasonder.org/index.php?title=DBMS_XPLAN&oldid=1871"