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.
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.
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.
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.
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.
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.