{"id":366,"date":"2008-10-19T16:34:24","date_gmt":"2008-10-19T14:34:24","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=366"},"modified":"2008-10-25T20:03:07","modified_gmt":"2008-10-25T18:03:07","slug":"formatted-sql-plan-display-in-oracle-9i","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2008\/10\/19\/formatted-sql-plan-display-in-oracle-9i\/","title":{"rendered":"Formatted SQL Plan Display in Oracle 9i"},"content":{"rendered":"<p>Tom Kyte has posted a way to display the SQL Explain Plan from v$sql_plan in a formatted way in Oracle 9i on <a href=\"http:\/\/asktom.oracle.com\/pls\/ask\/f?p=4950:8:4653056091156531243::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:10353453905351\">AskTom<\/a>. Whereas this is easily possible in 10g with DBMS_XPLAN, in 9i this poses a bit of a challenge:<\/p>\n<ul>\n<li>Step 1: Create a view dynamic_plan_table<\/li>\n<\/ul>\n<blockquote><p>create or replace view dynamic_plan_table<br \/>\nas<br \/>\nselect<br \/>\nrawtohex(address) || &#8216;_&#8217; || child_number statement_id,<br \/>\nsysdate timestamp, operation, options, object_node,<br \/>\nobject_owner, object_name, 0 object_instance,<br \/>\noptimizer,   search_columns, id, parent_id, position,<br \/>\ncost, cardinality, bytes, other_tag, partition_start,<br \/>\npartition_stop, partition_id, other, distribution,<br \/>\ncpu_cost, io_cost, temp_space, access_predicates,<br \/>\nfilter_predicates<br \/>\nfrom v$sql_plan;<\/p><\/blockquote>\n<ul>\n<li>Step 2: Extract SQL child_number, rawtohex(address) from v$sql for a known SQL hash value:<\/li>\n<\/ul>\n<blockquote><p>SQL&gt; select rawtohex(address) , child_number from v$sql where hash_value = &#8216;4163478529&#8217;;<\/p><\/blockquote>\n<ul>\n<li>Step 3: Query DBMS_XPLAN with created view<\/li>\n<\/ul>\n<blockquote><p>SQL&gt; r    select plan_table_output    from TABLE(        dbms_xplan.display                   ( &#8216;dynamic_plan_table&#8217;,                     (select rawtohex(address)||&#8217;_&#8217;||child_number x                             from v$sql                             where hash_value = &#8216;3471874038&#8217; AND                            rawtohex(ADDRESS) = &#8216;00000003A924BBD0&#8217; AND                             child_number = 1 ), &#8216;ALL&#8217; ) )<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Tom Kyte has posted a way to display the SQL Explain Plan from v$sql_plan in a formatted way in Oracle 9i on AskTom. Whereas this is easily possible in 10g with DBMS_XPLAN, in 9i this poses a bit of a challenge: Step 1: Create a view dynamic_plan_table create or replace view dynamic_plan_table as select rawtohex(address) [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[15,5,37],"tags":[],"class_list":["post-366","post","type-post","status-publish","format-standard","hentry","category-9ir2","category-oracle-database","category-sql-oracle-database-2"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/366","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=366"}],"version-history":[{"count":6,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/366\/revisions"}],"predecessor-version":[{"id":422,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/366\/revisions\/422"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=366"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=366"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=366"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}