Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

DBMS_XPLAN.PREPARE_PLAN_XML_QUERY

21 November 2008 2 Comments Written by Christian Antognini

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;

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;

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]

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)

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>00:00:01</time>
</operation>
<operation name="TABLE ACCESS" options="BY INDEX ROWID" id="1" depth="1" pos="1">
  <object>EMP</object>
  <card>1</card>
  <bytes>37</bytes>
  <cost>1</cost>
  <io_cost>1</io_cost>
  <cpu_cost>8461</cpu_cost>
  <time>00:00:01</time>
  <project>"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>SEL$1</qblock>
  <other_xml>
    <info type="db_version">11.1.0.7</info>
    <info type="parse_schema">"OPS$CHA"</info>
    <info type="plan_hash">4024650034</info>
    <info type="plan_hash_2">1148924698</info>
    <outline_data>
      <hint>INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))</hint>
      <hint>OUTLINE_LEAF(@"SEL$1")</hint>
      <hint>ALL_ROWS</hint>
      <hint>DB_VERSION('11.1.0.7')</hint>
      <hint>OPTIMIZER_FEATURES_ENABLE('11.1.0.7')</hint>
      <hint>IGNORE_OPTIM_EMBEDDED_HINTS</hint>
    </outline_data>
  </other_xml>
</operation>
<operation name="INDEX" options="UNIQUE SCAN" id="2" depth="2" pos="1">
  <object>EMP_PK</object>
  <card>1</card>
  <cost>0</cost>
  <io_cost>0</io_cost>
  <cpu_cost>1050</cpu_cost>
  <time>00:00:01</time>
  <project>"EMP".ROWID[ROWID,10],
           "EMPNO"[NUMBER,22]</project>
  <predicates type="access">"EMPNO"=7788</predicates>
  <qblock>SEL$1</qblock>
</operation>

<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>00:00:01</time> </operation> <operation name="TABLE ACCESS" options="BY INDEX ROWID" id="1" depth="1" pos="1"> <object>EMP</object> <card>1</card> <bytes>37</bytes> <cost>1</cost> <io_cost>1</io_cost> <cpu_cost>8461</cpu_cost> <time>00:00:01</time> <project>"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>SEL$1</qblock> <other_xml> <info type="db_version">11.1.0.7</info> <info type="parse_schema">"OPS$CHA"</info> <info type="plan_hash">4024650034</info> <info type="plan_hash_2">1148924698</info> <outline_data> <hint>INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))</hint> <hint>OUTLINE_LEAF(@"SEL$1")</hint> <hint>ALL_ROWS</hint> <hint>DB_VERSION('11.1.0.7')</hint> <hint>OPTIMIZER_FEATURES_ENABLE('11.1.0.7')</hint> <hint>IGNORE_OPTIM_EMBEDDED_HINTS</hint> </outline_data> </other_xml> </operation> <operation name="INDEX" options="UNIQUE SCAN" id="2" depth="2" pos="1"> <object>EMP_PK</object> <card>1</card> <cost>0</cost> <io_cost>0</io_cost> <cpu_cost>1050</cpu_cost> <time>00:00:01</time> <project>"EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]</project> <predicates type="access">"EMPNO"=7788</predicates> <qblock>SEL$1</qblock> </operation>

11gR1, Query Optimizer
TVD$XTAT 4.0 Beta 8
Query Optimizer 11g – What’s new?

2 Comments

  1. alzingre jean-michel alzingre jean-michel
    28 January 2016    

    Hi Christian,
    Interested in execution plans output formatting, I wonder if it is possible
    to use this prepare_plan_xml_query function along with the display_cursor function
    to get some xml output.
    It seems that this prepare_plan_xml_query function is not very documented by Oracle.
    Thanks for your help.
    Jean-michel, Nemours, France
    PS : I can say I truly appreciate the TOP books !

    Reply
    • Christian Antognini Christian Antognini
      29 January 2016    

      Salut Jean-michel

      I don’t know whether it’s possible. I already thought about that myself, but I never spent time trying…

      Cheers,
      Chris

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.