{"id":968,"date":"2008-11-21T13:00:40","date_gmt":"2008-11-21T17:00:40","guid":{"rendered":"http:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/"},"modified":"2008-11-21T13:00:40","modified_gmt":"2008-11-21T17:00:40","slug":"explain-plan-in-oracle","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/","title":{"rendered":"Explain Plan In Oracle"},"content":{"rendered":"

One of the most usefull tool which require for both DBAs and Developers to use\u00a0it effectively to avoid the\u00a0performance problem of Custom Code\u00a0specailly in ERP database as well as any database.\u00a0We should make best use of it.<\/p>\n

\u00a0An execution plan is a list of steps that Oracle will follow in order to execute a SQL statement. Each step is one of a finite number of basic operations known to the database server. Even the most complex SQL statement can be broken down into a series of basic operations.
\nEXPLAIN PLAN is a statement that allows you to have Oracle generate the execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table.<\/p>\n

\u00a0The Plan Table<\/u><\/strong><\/p>\n

1.A plan table holds execution plans generated by the EXPLAIN PLAN statement.
\n2.The typical name for a plan table is plan_table, but you may use any name you wish.
\n3.Create the plan table by running utlxplan.sql, located in $ORACLE_HOME\/rdbms\/admin.<\/p>\n

Input of\u00a0the Plan Table<\/u><\/strong> \u00a0<\/p>\n

\u00a0statement_id: Unique identifier for each execution plan
\n\u00a0timestamp:\u00a0\u00a0When the execution plan was generated
\n\u00a0operation:\u00a0\u00a0The operation performed in one step of the execution plan, such as \u201ctable access\u201d
\n\u00a0options:\u00a0\u00a0Additional information about the operation,such as \u201cby index ROWID\u201d
\n\u00a0object_name:\u00a0\u00a0Name of table, index, view, etc. accessed
\n\u00a0optimizer_id\u00a0:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Optimizer goal used when creating execution plan Step number in execution plan
\n\u00a0parent_id:\u00a0\u00a0Step number of parent step<\/p>\n

\u00a0Explain Plan Pre-Req<\/u><\/strong>\u00a0<\/p>\n

\u00a0 INSERT privilege on a plan table
\n\u00a0 All necessary privileges to execute the statement being explained
\n\u00a0 SELECT privileges on underlying tables of views, if the statement being explained involves views<\/p>\n

Explan Plan Syntax<\/u><\/strong><\/p>\n

EXPLAIN PLAN
\n[SET STATEMENT_ID = <string in single quotes>]
\n[INTO <plan table name>]
\nFOR
\n<SQL statement>;<\/p>\n

Querying an Execuction Plan from the Plan Table<\/u><\/strong><\/p>\n

Use a CONNECT BY clause to trace the hierarchy
\nUse LPAD function to indent rows, making the hierarchy easier to follow
\nPut statement_id in WHERE clause to retrieve only one execution plan at a time
\nSample script on next slide shows the most important information
\nYou can also try utlxpls.sql or utlxplp.sql in $ORACLE_HOME\/rdbms\/admin<\/p>\n

Sample Query to Display Execution<\/u><\/strong><\/p>\n

SET VERIFY OFF
\nACCEPT stmt_id CHAR PROMPT “Enter statement_id: ”
\nCOL id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FORMAT 999
\nCOL parent_id\u00a0\u00a0 FORMAT 999 HEADING “PARENT”
\nCOL operation\u00a0\u00a0 FORMAT a35 TRUNCATE
\nCOL object_name FORMAT a30
\nSELECT\u00a0\u00a0\u00a0\u00a0 id, parent_id, LPAD (‘ ‘, LEVEL – 1) ||
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 operation || ‘ ‘ || options operation,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name
\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 plan_table
\nWHERE\u00a0\u00a0\u00a0\u00a0\u00a0 statement_id = ‘&stmt_id’
\nSTART WITH id = 0
\nAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 statement_id = ‘&stmt_id’
\nCONNECT BY PRIOR id = parent_id
\nAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 statement_id = ‘&stmt_id’;
\nSample Execution Plan <\/u><\/strong><\/p>\n

SQL> EXPLAIN PLAN SET statement_id = ‘demo’ FOR
\n\u00a0 2\u00a0 SELECT a.customer_name, a.customer_number, b.invoice_number,
\n\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 b.invoice_type, b.invoice_date, b.total_amount,
\n\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.line_number, c.part_number, c.quantity, c.unit_cost
\n\u00a0 5\u00a0 FROM\u00a0\u00a0 customers a, invoices b, invoice_items c
\n\u00a0 6\u00a0 WHERE\u00a0 c.invoice_id = :b1
\n\u00a0 7\u00a0 AND\u00a0\u00a0\u00a0 c.line_number = :b2
\n\u00a0 8\u00a0 AND\u00a0\u00a0\u00a0 b.invoice_id = c.invoice_id
\n\u00a0 9\u00a0 AND\u00a0\u00a0\u00a0 a.customer_id = b.customer_id;
\nExplained.
\nSQL> @explain.sql
\nEnter statement_id: demo
\n\u00a0 ID PARENT OPERATION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_NAME
\n—- —— ———————————– —————–
\n\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT STATEMENT
\n\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0 NESTED LOOPS
\n\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 NESTED LOOPS
\n\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID\u00a0\u00a0\u00a0\u00a0\u00a0 INVOICE_ITEMS
\n\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0 INDEX UNIQUE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INVOICE_ITEMS_PK
\n\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID\u00a0\u00a0\u00a0\u00a0\u00a0 INVOICES
\n\u00a0\u00a0 6\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0 INDEX UNIQUE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INVOICES_PK
\n\u00a0\u00a0 7\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CUSTOMERS
\n\u00a0\u00a0 8\u00a0\u00a0\u00a0\u00a0\u00a0 7\u00a0\u00a0\u00a0 INDEX UNIQUE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CUSTOMERS_PK<\/p>\n

<\/u>
\nIn my next post i will try to cover other ways to view execution\u00a0plan with some examples.\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"

One of the most usefull tool which require for both DBAs and Developers to use\u00a0it effectively to avoid the\u00a0performance problem […]<\/p>\n","protected":false},"author":115,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[424],"tags":[],"class_list":["post-968","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\nExplain Plan In Oracle -<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Explain Plan In Oracle -\" \/>\n<meta property=\"og:description\" content=\"One of the most usefull tool which require for both DBAs and Developers to use\u00a0it effectively to avoid the\u00a0performance problem […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/\" \/>\n<meta property=\"article:published_time\" content=\"2008-11-21T17:00:40+00:00\" \/>\n<meta name=\"author\" content=\"Masroof Ahmad\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Masroof Ahmad\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/\",\"url\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/\",\"name\":\"Explain Plan In Oracle -\",\"isPartOf\":{\"@id\":\"https:\/\/onlineappsdba.com\/#website\"},\"datePublished\":\"2008-11-21T17:00:40+00:00\",\"author\":{\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\"},\"breadcrumb\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/onlineappsdba.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Explain Plan In Oracle\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/onlineappsdba.com\/#website\",\"url\":\"https:\/\/onlineappsdba.com\/\",\"name\":\"\",\"description\":\"Oracle Implementation & Training Experts\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/onlineappsdba.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\",\"name\":\"Masroof Ahmad\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"caption\":\"Masroof Ahmad\"},\"url\":\"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Explain Plan In Oracle -","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"Explain Plan In Oracle -","og_description":"One of the most usefull tool which require for both DBAs and Developers to use\u00a0it effectively to avoid the\u00a0performance problem […]","og_url":"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/","article_published_time":"2008-11-21T17:00:40+00:00","author":"Masroof Ahmad","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Masroof Ahmad","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/","url":"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/","name":"Explain Plan In Oracle -","isPartOf":{"@id":"https:\/\/onlineappsdba.com\/#website"},"datePublished":"2008-11-21T17:00:40+00:00","author":{"@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb"},"breadcrumb":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/11\/21\/explain-plan-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/onlineappsdba.com\/"},{"@type":"ListItem","position":2,"name":"Explain Plan In Oracle"}]},{"@type":"WebSite","@id":"https:\/\/onlineappsdba.com\/#website","url":"https:\/\/onlineappsdba.com\/","name":"","description":"Oracle Implementation & Training Experts","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/onlineappsdba.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb","name":"Masroof Ahmad","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","caption":"Masroof Ahmad"},"url":"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/"}]}},"_links":{"self":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/968","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/users\/115"}],"replies":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/comments?post=968"}],"version-history":[{"count":0,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/968\/revisions"}],"wp:attachment":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/media?parent=968"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/categories?post=968"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/tags?post=968"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}