Nov 21 2008
DBMS_XPLAN.PREPARE_PLAN_XML_QUERY
As of 11.1.0.7 in the package DBMS_XPLAN there is a new function:
FUNCTION prepare_plan_xml_query(plan_query IN VARCHAR2) RETURN VARCHAR2;
Simply put, the function takes as input a query that extract information from a plan table (e.g. PLAN_TABLE or V$SQL_PLAN) and builds a query based on SQLX functions that returns the output of the input query in XML.
Let’s have a look to an example:
- At first we generate an execution plan with EXPLAIN PLAN.
SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM scott.emp 4 WHERE empno = 7788;
- If we want to display all information stored in the plan table in a readable way, we can use a query like the following one.
SQL> SELECT *
2 FROM table(dbms_xplan.display(format=>'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 4024650034
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.7')
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
- Such an output is useful for a human being. However, if we have to provide that information to an application for further processing, that output is not good. In such a situation the new function might be useful. In fact, we can let it generate a query based on SQLX functions that returns an XML fragment (as you will see later, the output it’s not a valid XML document).
SQL> SELECT dbms_xplan.prepare_plan_xml_query('SELECT * FROM plan_table') AS query
2 FROM dual;
QUERY
----------------------------------------------------------------------------------
select xmlagg(xmlelement("operation",
xmlattributes(operation as "name",
options as "options",
id as "id",
depth as "depth",
position as "pos"),
nvl2(object_name,
xmlelement("object",
xmlcdata(object_name)), NULL),
nvl2(cardinality, xmlelement("card", cardinality),
NULL),
nvl2(bytes, xmlelement("bytes", bytes), NULL),
nvl2(temp_space, xmlelement("temp_space",temp_space),
NULL),
nvl2(cost, xmlelement("cost", cost), NULL),
nvl2(io_cost, xmlelement("io_cost", io_cost), NULL),
nvl2(cpu_cost, xmlelement("cpu_cost",cpu_cost),NULL),
nvl2(time,
xmlelement("time",
xmlcdata(sys.dbms_xplan.format_time_s(time))),
NULL),
nvl2(partition_start,
xmlelement("partition",
xmlattributes(partition_start as "start",
partition_stop as "stop")),
NULL),
nvl2(object_node, xmlelement("node", object_node),
NULL),
nvl2(distribution,xmlelement("distrib",distribution),
NULL),
nvl2(projection,
xmlelement("project", xmlcdata(projection)), NULL),
nvl2(access_predicates,
xmlelement("predicates",
xmlattributes('access' as "type"),
xmlcdata(access_predicates)),
NULL),
nvl2(filter_predicates,
xmlelement("predicates",
xmlattributes('filter' as "type"),
xmlcdata(filter_predicates)),
NULL),
nvl2(qblock_name,
xmlelement("qblock", xmlcdata(qblock_name)),
NULL),
(case when other_xml is null then null
else xmltype(other_xml) end)
)
) plan
from (SELECT * FROM plan_table)
- By running that query we get the following XML fragment (I formatted the output to make it readable). As you can see, it contains all information previously provided by the function DISPLAY.
<operation name="SELECT STATEMENT" id="0" depth="0" pos="1">
<card>1</card>
<bytes>37</bytes>
<cost>1</cost>
<io_cost>1</io_cost>
<cpu_cost>8461</cpu_cost>
<time><![CDATA[00:00:01 ]]></time>
</operation>
<operation name="TABLE ACCESS" options="BY INDEX ROWID" id="1" depth="1" pos="1">
<object><![CDATA[EMP]]></object>
<card>1</card>
<bytes>37</bytes>
<cost>1</cost>
<io_cost>1</io_cost>
<cpu_cost>8461</cpu_cost>
<time><![CDATA[00:00:01 ]]></time>
<project><![CDATA["EMPNO"[NUMBER,22],
"EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9],
"EMP"."MGR"[NUMBER,22],
"EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22],
"EMP"."DEPTNO"[NUMBER,22]]]></project>
<qblock><![CDATA[SEL$1]]></qblock>
<other_xml>
<info type="db_version">11.1.0.7</info>
<info type="parse_schema"><![CDATA["OPS$CHA"]]></info>
<info type="plan_hash">4024650034</info>
<info type="plan_hash_2">1148924698</info>
<outline_data>
<hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[ALL_ROWS]]></hint>
<hint><![CDATA[DB_VERSION('11.1.0.7')]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.1.0.7')]]></hint>
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
</outline_data>
</other_xml>
</operation>
<operation name="INDEX" options="UNIQUE SCAN" id="2" depth="2" pos="1">
<object><![CDATA[EMP_PK]]></object>
<card>1</card>
<cost>0</cost>
<io_cost>0</io_cost>
<cpu_cost>1050</cpu_cost>
<time><![CDATA[00:00:01 ]]></time>
<project><![CDATA["EMP".ROWID[ROWID,10],
"EMPNO"[NUMBER,22]]]></project>
<predicates type="access"><![CDATA["EMPNO"=7788]]></predicates>
<qblock><![CDATA[SEL$1]]></qblock>
</operation>