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 |
---------------------------------------------------------------------------------
You can get it in tabular format if you access the
PLAN_TABLE
directly:As the plan_table can contain different plans, it's probably better to use an explicit statement id:
and then use that in the select on the plan_table:
To get this as XML you can use: