DBMS_XPLAN.DISPLAY_CURSOR without unnecessary rows like EXPLAINED SQL STATEMENT or Plan hash value (without using sed or awk) Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of DBMS_XPLAN.DISPLAY_CURSOR without unnecessary rows like EXPLAINED SQL STATEMENT or Plan hash value (without using sed or awk) without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I’m using a query looking like this: SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC'));

The output it gives me looks like this:

EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ PARALLEL(T1 8 ) */   * FROM   T1

Plan hash value: 2494645258

-----------------------------------------
| Id  | Operation            | Name     |
-----------------------------------------
|   0 | SELECT STATEMENT     |          |
|   1 |  PX COORDINATOR      |          |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |
|   3 |    PX BLOCK ITERATOR |          |
|   4 |     TABLE ACCESS FULL| T1       |
-----------------------------------------

But I want the output like this:

-----------------------------------------
| Id  | Operation            | Name     |
-----------------------------------------
|   0 | SELECT STATEMENT     |          |
|   1 |  PX COORDINATOR      |          |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |
|   3 |    PX BLOCK ITERATOR |          |
|   4 |     TABLE ACCESS FULL| T1       |
-----------------------------------------

Without the unnecessary (for me at least) EXPLAINED SQL STATEMENT and Plan hash value.#

Is there any way to do this, without using sed or awk to remove the unnecessary lines?

Answer

If you really want to have a nice graphical representation of the execution plan, then use the EXPLAIN PLAN window in Oracle SQL Developer.

enter image description here

EDIT

Based on @a_horse_with_no_name solution, tweaking a bit more gives the exact desired output.

SQL> SELECT plan_table_output
  2  FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC'))
  3  WHERE substr(plan_table_output,1,1) in ('|', '-')
  4  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

7 rows selected.

So, I still see the PLAN_TABLE_OUTPUT in the output. Setting pagesize to 0 should take of it.

SQL> set pagesize 0
SQL> SELECT plan_table_output
  2  FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC'))
  3  WHERE substr(plan_table_output,1,1) in ('|', '-')
  4  /
------------------------
------------------------------------------------------------
| Id  | Operation                         | Name           |
------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |
------------------------------------------------------------

7 rows selected.

Now there is still something unwanted, ------------------------, let’s just not include in the output at all.

SQL> SELECT plan_table_output
  2  FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC'))
  3  WHERE substr(plan_table_output,1,1) in ('|', '-')
  4  and plan_table_output not in('------------------------')
  5  /
------------------------------------------------------------
| Id  | Operation                         | Name           |
------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |
------------------------------------------------------------

6 rows selected.

SQL>
We are here to answer your question about DBMS_XPLAN.DISPLAY_CURSOR without unnecessary rows like EXPLAINED SQL STATEMENT or Plan hash value (without using sed or awk) - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji