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