Oracle build_plan_xml visualizer

Question

Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML

I now have a very nice looking Execution Plan display for SQL Server (as a result of my previous question), and am hoping that lightning could strike twice here and that I might find a similarly high-quality solution for Oracle.

My site SQL Fiddle displays execution plans for each of the queries that you run on it, for each of the database types I support (at the moment - MS SQL, MySQL, PGSQL, and Oracle). The Oracle execution plan is built in this manner:

explain plan set STATEMENT_ID = '#schema_short_code#/#query_id#' for 
select id, type, details from supportContacts

/

select 
dbms_xplan.build_plan_xml(statement_id => '#schema_short_code#/#query_id#').getclobval() 
AS XPLAN 
FROM dual

Where schema_short_code and query_id are obviously variables representing each distinct query. You can see the result of the above example running here: http://sqlfiddle.com/#!4/ee7da/596

This produces the following XML:

<plan>
  <operation name="SELECT STATEMENT" id="0" depth="0" pos="2">
    <card>2</card>
    <bytes>94</bytes>
    <cost>2</cost>
    <io_cost>2</io_cost>
    <cpu_cost>7501</cpu_cost>
    <time>00:00:01 </time>
  </operation>
  <operation name="TABLE ACCESS" options="FULL" id="1" depth="1" pos="1">
    <object>SUPPORTCONTACTS</object>
    <card>2</card>
    <bytes>94</bytes>
    <cost>2</cost>
    <io_cost>2</io_cost>
    <cpu_cost>7501</cpu_cost>
    <time>00:00:01 </time>
    <project>&quot;ID&quot;[NUMBER,22], &quot;TYPE&quot;[VARCHAR2,20], &quot;DETAILS&quot;[VARCHAR2,40]</project>
    <qblock>SEL$1</qblock>
    <object_alias>SUPPORTCONTACTS@SEL$1</object_alias>
    <other_xml>
      <info type="db_version">11.2.0.2</info>
      <info type="parse_schema"><![CDATA["USER_EE7DA"]]></info>
      <info type="dynamic_sampling">2</info>
      <info type="plan_hash">2812004906</info>
      <info type="plan_hash_2">2012527029</info>
      <outline_data>
        <hint><![CDATA[FULL(@"SEL$1" "SUPPORTCONTACTS"@"SEL$1")]]></hint>
        <hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
        <hint><![CDATA[ALL_ROWS]]></hint>
        <hint><![CDATA[DB_VERSION('11.2.0.2')]]></hint>
        <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]]></hint>
        <hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
      </outline_data>
    </other_xml>
  </operation>
</plan>

Although reading the raw XML is not terrible, it certainly does not look anywhere nearly as nice as what I now am able to show for SQL Server (compare by clicking "View Execution Plan" on this page: http://sqlfiddle.com/#!3/1fa93/1)

Has anyone worked with this XML before? Is it possible that someone might have built something close to what Justin built for SQL Server (see answerer for the SQL Server question)? If not, does anyone know where I can find some good direction for building my own XSLT (schema documentation, etc....), and possibly where I can look to see some nice example query plan visualizers for Oracle that I could hope to emulate?

Update

Since there are no answers yet provided, and the bounty is about to expire, I'll happily accept an answer that simply can tell me anything useful about this XML schema, or points me in the direction of any graphical execution plan display for Oracle (even desktop based GUIs).

Answer 1

You can use dbms_xplan.display_plan to get text, xml, html or 'active' output as a clob:

select dbms_xplan.display_plan(format=>'ALL', type=>'HTML') from dual;

select dbms_xplan.display_plan(format=>'ALL', type=>'ACTIVE') from dual;

Html is similar to the text output, but the 'active' type is quite different:

Active reports have a rich, interactive user interface akin to that found in Enterprise Manager while not requiring any EM installation. The report file built is in HTML format, so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity.

Answer 2

I think you'd be better off using the text version of the explain plan output rather than XML, as it gives you a pre-formatted readable ascii representation of the query plan.

eg:

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','#schema_short_code#/#query_id#','ALL'));

Failing that, if you have the means to do so server-side, you could use Oracle SQL Developer to give you a graphical representation of the query plan - Though this is really just the same as the ASCII version with a few icons.

Also, take a look at $ORACLE_HOME/rdbms/xml/orarep/xplan/xplSch.xsd , $ORACLE_HOME/rdbms/xml/orarep/xplan/xplHtml.xsl and $ORACLE_HOME/rdbms/xml/xsl/kuplan.xsl - They may help you format your XML output to prettier HTML.