Hide SQL statement text from DBMS_XPLAN output

22 Apr 2018

The dbms_xplan.display* functions have a lot of options to format the execution plan output via the format parameter. What often annoys me is the output of the SQL statement text. If, for example, you want to display all execution plans of a cursor with many child cursors and large SQL statement texts, the SQL text is returned for each child cursor and clutters your screen. Unfortunately there is no way to turn off this output with a format parameter.

Luckily we can just filter the rows returned by dbms_xplan.display* functions with a custom query. This blog post show you how. If you are just interested in how the final query looks like, click here.

Build the query

First, let’s have a look at a sample output of dbms_xplan.display_cursor for a cursor with 2 child cursors.

Query

select * from table(
  dbms_xplan.display_cursor('&sqlId', null, 'TYPICAL')
);

Output

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  934ur8r7tqbjx, child number 0
-------------------------------------
SELECT DBID FROM V$DATABASE

Plan hash value: 735420252

----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
|   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
----------------------------------------------------------------------

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

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))

SQL_ID  934ur8r7tqbjx, child number 1
-------------------------------------
SELECT DBID FROM V$DATABASE

Plan hash value: 735420252

----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
|   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
----------------------------------------------------------------------

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

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))

The highlighted lines in the output above should always occur in the output, no matter what format options are defined. They are also very easy to identify with a simple regex, so the idea is to filter out everything in between them, with some exceptions (like the Plan hash value line or the header of the execution plan table).

A couple of ways to do that come to my mind:

Create a PL/SQL function which accepts the output of the DBMS_XPLAN.DISPLAY_CURSOR function and process it procedurally row by row. I don’t want to create any objects in the database, but in 12c you could define the function in the WITH clause. As I want a solution also working on older Oracle version I don’t follow this approach.
The pattern matching functionality in 12c is very powerful and could to this job, but again, I don’t want to depend on 12c.
There must be a way to do this with analytic functions. They are available since Oracle 8i, so I’m able to use this solution on all environments I need.

First step: Use last_value analytic function

The idea is to add an additional column to the plan output which has the value 1 if the row should be display or 0 if it shouldn’t be displayed.

I use the CASE expression as a first step to mark a row to either be shown or not. In combination with the LAST_VALUE analytic function we can mark all subsequent rows the same way.

Query

select plan_table_output,show from (
select
  plan_table_output,
  last_value(
    case
      when plan_table_output like '| Id%'
        then 1
      when regexp_like(plan_table_output,
                       'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
        then 0 
      else null
    end) ignore nulls over (order by rownum)
  as show
from table(dbms_xplan.display_cursor('&sqlId',null,'TYPICAL')))
/

Check the output below first, to see what’s returned.

The use of NULL and the IGNORE NULLS clause in the LAST_VALUE function is important. This way, for every NULL returned by the CASE expression, the LAST_VALUE function ignores them and uses the last non-null value, which is either 1 or 0.

Note that the regex also searches for HASH_VALUE and EXPLAINED SQL STATEMENT. This is because, depending on the detail level chosen in the format parameter of the DBMS_XPLAN function, the output looks a little bit different.

Output

SHOW PLAN_TABLE_OUTPUT
---- ----------------------------------------------------------------------
   0 SQL_ID  934ur8r7tqbjx, child number 0
   0 -------------------------------------
   0 SELECT DBID FROM V$DATABASE
   0
   0 Plan hash value: 735420252
   0
   0 ----------------------------------------------------------------------
   1 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
   1 ----------------------------------------------------------------------
   1 |   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
   1 |   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
   1 |*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
   1 |   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
   1 |   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
   1 ----------------------------------------------------------------------
   1
   1 Predicate Information (identified by operation id):
   1 ---------------------------------------------------
   1
   1    2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
   1
   0 SQL_ID  934ur8r7tqbjx, child number 1
   0 -------------------------------------
   0 SELECT DBID FROM V$DATABASE
   0
   0 Plan hash value: 735420252
   0
   0 ----------------------------------------------------------------------
   1 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
   1 ----------------------------------------------------------------------
   1 |   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
   1 |   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
   1 |*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
   1 |   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
   1 |   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
   1 ----------------------------------------------------------------------
   1
   1 Predicate Information (identified by operation id):
   1 ---------------------------------------------------
   1
   1    2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
   1

The column SHOW now correctly contains 0 for the SQL statement section, but we also have some false negatives:

The row showing use the SQL_ID
The row after the SQL_ID which is kind of a separator
The row showing the plan hash value
The first row which is part of the execution plan table header

Second step: Refine query

To fix these false negatives, again we use the CASE expression in combination with some regex.

The LAG and LEAD analytic functions are used to decide what value the column has, depending on the next or previous row.

Query

select show,plan_table_output from (
select
  plan_table_output,
  case
    when regexp_like(plan_table_output, 
                    'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
      then 1
    when regexp_like(lag(plan_table_output) over (order by rownum),
                    'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
      then 1
    when (lead(plan_table_output) over (order by rownum)) like '| Id%'
      then 1
    when plan_table_output like 'Plan hash value%'
      then 1
    else last_value(
      case
        when plan_table_output like '| Id%'
          then 1
        when regexp_like(plan_table_output,
                        'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
          then 0 
	else null
    end) ignore nulls over (order by rownum)
  end as show
from table(dbms_xplan.display_cursor('&sqlId', null,'TYPICAL')))
/

Output

SHOW PLAN_TABLE_OUTPUT
---- ----------------------------------------------------------------------
   1 SQL_ID  934ur8r7tqbjx, child number 0
   1 -------------------------------------
   0 SELECT DBID FROM V$DATABASE
   0
   1 Plan hash value: 735420252
   0
   1 ----------------------------------------------------------------------
   1 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
   1 ----------------------------------------------------------------------
   1 |   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
   1 |   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
   1 |*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
   1 |   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
   1 |   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
   1 ----------------------------------------------------------------------
   1
   1 Predicate Information (identified by operation id):
   1 ---------------------------------------------------
   1
   1    2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
   1
   1 SQL_ID  934ur8r7tqbjx, child number 1
   1 -------------------------------------
   0 SELECT DBID FROM V$DATABASE
   0
   1 Plan hash value: 735420252
   0
   1 ----------------------------------------------------------------------
   1 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
   1 ----------------------------------------------------------------------
   1 |   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
   1 |   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
   1 |*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
   1 |   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
   1 |   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
   1 ----------------------------------------------------------------------
   1
   1 Predicate Information (identified by operation id):
   1 ---------------------------------------------------
   1
   1    2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
   1

Final query

This is exactly what we want. All we have to do now, is to remove the projection of the SHOW column and filter the rows with SHOW=0.

Query

select plan_table_output from (
select
  plan_table_output,
  case
    when regexp_like(plan_table_output, 
                    'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
      then 1
    when regexp_like(lag(plan_table_output) over (order by rownum),
                    'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
      then 1
    when (lead(plan_table_output) over (order by rownum)) like '| Id%'
      then 1
    when plan_table_output like 'Plan hash value%'
      then 1
    else last_value(
      case
        when plan_table_output like '| Id%' 
          then 1
        when regexp_like(plan_table_output, 
                        'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
          then 0 
	else null
    end) ignore nulls over (order by rownum)
  end as show
from table(dbms_xplan.display_cursor('&sqlId', null,'TYPICAL')))
where show=1
/

Output

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  934ur8r7tqbjx, child number 0
-------------------------------------
Plan hash value: 735420252
----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
|   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
----------------------------------------------------------------------

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

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))

SQL_ID  934ur8r7tqbjx, child number 1
-------------------------------------
Plan hash value: 735420252
----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
|   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
----------------------------------------------------------------------

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

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))

The query was tested with various format options and should work equally well under 12c and 11g and probably also on older versions.

I use a variaton of it in a script where I can control whether the SQL statement text is shown or not with when I invoke it.

blog. aregger.io

Hide SQL statement text from DBMS_XPLAN output

Build the query

Query

Output

First step: Use last_value analytic function

Query

Output

Second step: Refine query

Query

Output

Final query

Query

Output