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>"ID"[NUMBER,22], "TYPE"[VARCHAR2,20], "DETAILS"[VARCHAR2,40]</project> <qblock>SEL$1</qblock> <object_alias>SUPPORTCONTACTS@SEL$1</object_alias> <other_xml> <info type="db_version">126.96.36.199</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('188.8.131.52')]]></hint> <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('184.108.40.206')]]></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?
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).