CODEWITHSUNDEEP
databaseoracle-databasesqlplus
hc0re
hc0re

Reputation: 1986

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

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?

Upvotes: 1

Views: 1252

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

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>

Upvotes: 3

user330315
user330315

Reputation:

You could add a where condition:

SELECT plan_table_output 
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC'))
WHERE substr(plan_table_output,1,1) in ('|', '-')

This will however not remove some of the "underlines" in the summary section of the output. However you can extend the where clause to include more rules

Upvotes: 1

Related Questions