CODEWITHSUNDEEP
oracle-databaseformattingsql-execution-plan
Vicky Thakor
Vicky Thakor

Reputation: 3916

Explain Plan output in Oracle

I'm not expert on Oracle just started working around. When I execute following queries it gives me output in |(pipeline) format. I want EXPLAIN PLAN output in tabluar or json or xml, etc... Is it possible?

EXPLAIN PLAN FOR SELECT * FROM user_master;
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table'));

Output:

    Plan hash value: 3060894046

    ---------------------------------------------------------------------------------
    | Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |             |     1 |    94 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| USER_MASTER |     1 |    94 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------

Upvotes: 0

Views: 2006

Answers (1)

user330315
user330315

Reputation:

You can get it in tabular format if you access the PLAN_TABLE directly:

select plan_id,
       operation,
       options,
       cost,
       cpu_cost,
       io_cost,
       temp_space,
       access_predicates,
       bytes,
       object_name,
       object_alias,
       optimizer,
       object_type
from plan_table
start with parent_id is null
connect by prior id = parent_id;

As the plan_table can contain different plans, it's probably better to use an explicit statement id:

explain plan 
  set statement_id = 'foo'
for
select ...;

and then use that in the select on the plan_table:

select ....
from plan_table
start with parent_id is null and statement_id = 'foo'
connect by prior id = parent_id;

To get this as XML you can use:

select dbms_xplan.display_plan(type => 'xml')
FROM dual

Upvotes: 4

Related Questions