dbms_xplan (4)

This little note on how dbms_xplan.display_cursor() behaves was prompted by a very simple question on OTN which raised a point that I often manage to forget (temporarily): “Why don’t I see all the execution plans for the SQL_ID I’ve supplied?” I’ve chosen to explain it through a little demonstration.

Session 1 – cut-n-paste (with minor cosmetic changes):

SQL> set pagesize 60
SQL> set linesize 180
SQL> set trimspool on
SQL> set tab off

SQL> set serveroutput off
SQL> select max(n2) from t1 where n1 = 15;

   MAX(N2)
----------
        15

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------
SQL_ID  b8ud16xgnsgt7, child number 0
-------------------------------------
select max(n2) from t1 where n1 = 15

Plan hash value: 269862921

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N1"=15)

20 rows selected.

Session 2 – cut-n-paste:

SQL> set pagesize 60
SQL> set linesize 180
SQL> set trimspool on
SQL> set tab off

SQL> set serveroutput off 
SQL> alter session set workarea_size_policy = manual;

Session altered.

SQL> select max(n2) from t1 where n1 = 15;

   MAX(N2)
----------
        15

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------
SQL_ID  b8ud16xgnsgt7, child number 1
-------------------------------------
select max(n2) from t1 where n1 = 15

Plan hash value: 269862921

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N1"=15)

20 rows selected.

SQL>

Because I’ve changed the optimizer environment for the second session Oracle has created a second child cursor for query – even though the execution plan turned out to be exactly the same. (The fact that you can get two child cursors  with the same plan sometimes surprises people but it’s not a rare occurrence.) You’ll notice that the two sessions report different values for child number.

So let’s use a third session to find the plans for the sql_id that the previous outputs report: b8ud16xgnsgt7. Here’s the complete cut-n-paste (again with minor cosmetic changes):

SQL> set pagesize 60 
SQL> set linesize 180 
SQL> set trimspool on
SQL> set tab off

SQL> select * from table(dbms_xplan.display_cursor('b8ud16xgnsgt7'));

PLAN_TABLE_OUTPUT
--------------------
SQL_ID  b8ud16xgnsgt7, child number 0
-------------------------------------
select max(n2) from t1 where n1 = 15

Plan hash value: 269862921

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N1"=15)

20 rows selected

Question: We’ve got the execution plan for child_number 0, what happened to child_number 1 ?
Answer: We didn’t ask for it.

The default value for the second parameter to display_cursor() is zero. If you want to see all the available execution plans for a given sql_id, you need to supply an explicit null to the call, viz:

SQL> select * from table(dbms_xplan.display_cursor('b8ud16xgnsgt7',null));

(I won’t bother to cut-n-paste the output – it just lists the two plans one after the other in child_number order reporting a total of 40 rows selected.)

If you want to read other notes that make significant points about dbms_xplan, there’s an entry for it in the Categories drop-down list to the top right of the screen.

Footnote:

Take note of the four formatting (set) commands I’ve issued in SQL*Plus. Execution plans tend to be quite long and can have  some wide bits of white space . If someone asks you to supply an execution plan on one of the Oracle forums or mailing lists then some suitable format settings will make the output much easier to read – you don’t want to see lines wrapping and page heading every few lines, and columns failing to align, if you’re trying to work out what the plan is saying.

Note also that for SQL*Plus, you need to set serveroutput off if you’re going to call dbms_xplan.display_cursor() without an SQL_ID or the last statement you executed (behind the scenes) will be a call to dbms_output() and you’ll get a surprising error message about being unable to find a cursor.

Update (Aug 2021)

A quick check of dbms_xplan.display_cursor() and the manuals, shows that (a) the behaviour hasn’t changed and (b) the documentation bug described by Randof Geist in comment #1 below (and raised by Greg Rahn in response) has not been fixed.