Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
Not related to your question, but if you are the one behind SQLFiddle (great work btw), you might consider using the code from explain.depesz.com to get a better display of a PostgreSQL execution plan. –  a_horse_with_no_name Mar 13 '12 at 0:26
    
@a_horse_with_no_name thanks I'll definitely look at that. –  Jake Feasel Mar 13 '12 at 1:22

2 Answers 2

up vote 3 down vote accepted
+100

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.

share|improve this answer
    
Thanks for your tip! I've updated the site to use the flash viewer behind the "ACTIVE" option (Check out this link to see it in action: sqlfiddle.com/#!4/ee7da/596). Had to do some serious mucking around with Oracle's HTML and JS to get it to work, but finally managed to do so. Thanks! –  Jake Feasel Mar 22 '12 at 5:39
    
I'm getting ORA-02395 when I try and take a look –  Jack Douglas Mar 22 '12 at 12:55
    
Sigh, gotta love Oracle. It works when you go to bed and breaks by the time you wake up.... Anyhow, it's working now. Hopefully it will continue to do so. –  Jake Feasel Mar 22 '12 at 14:48
    
it's funky - well done :-) –  Jack Douglas Mar 22 '12 at 18:57

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.

share|improve this answer
    
Thanks for your answer, phil. Text would suffice if there were no convenient graphical alternatives, but as you can see now I've got the Oracle flash-based execution plan viewer working. I think this format is preferable for my specific case. Your tips would certainly be useful for people looking to solve similar problems, however. –  Jake Feasel Mar 22 '12 at 20:28
    
No worries - I'm glad jack mentioned the ACTIVE report, as I was unaware myself! –  Phil Mar 22 '12 at 20:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.