{"id":3898,"date":"2018-10-17T17:28:40","date_gmt":"2018-10-17T17:28:40","guid":{"rendered":"https:\/\/dbtut.com\/?p=3898"},"modified":"2018-11-26T12:55:38","modified_gmt":"2018-11-26T12:55:38","slug":"how-to-fetch-explain-plan","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/10\/17\/how-to-fetch-explain-plan\/","title":{"rendered":"How To Fetch Explain Plan"},"content":{"rendered":"
<\/p>\n
Displaying an execution plan is made easy if you use the DBMS_XPLAN package.<\/p>\n
This packages provides several PL\/SQL procedures to display the plan from different sources:<\/p>\n
\u2022 EXPLAIN PLAN command \n\u2022 V$SQL_PLAN \n\u2022 Automatic Workload Repository (AWR) \n\u2022 SQL Tuning Set (STS) \n\u2022 SQL Plan Baseline (SPM)<\/p>\n
The following examples illustrate how to generate and display an execution plan for our original SQL statement using the different functions provided in the dbms_xplan package.<\/p>\n
<\/p>\n
Example 1:<\/strong> Uses the EXPLAIN PLAN command and the DBMS_XPLAN.DISPLAY function.<\/p>\n
SQL> EXPLAIN PLAN FOR\r\nselect prod_category, avg(amount_sold)\r\nfrom sales s, products p\r\nwhere p.prod_id = s.prod_id\r\ngroup by prod_category;<\/pre>\n
Explained.<\/p>\n
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));<\/pre>\n
<\/p>\n
The arguments are for DBMS_XPLAN.DISPLAY are: \n\u2022 Plan table name (default ‘PLAN_TABLE’) \n\u2022 Statement_id (default NULL) \n\u2022 Format (default ‘TYPICAL’) \nMore details can be found in $ORACLE_HOME\/rdbms\/admin\/dbmsxpln.sql.<\/p>\n
<\/p>\n
Example 2:<\/strong> Generating and displaying the execution plan for the last SQL statement executed in a session:<\/p>\n
SQL> select plan_table_output\r\nfrom table(dbms_xplan.display_cursor(null,null,'basic'));\r\n\r\n------------------------------------------\r\nId Operation Name\r\n------------------------------------------\r\n0 SELECT STATEMENT\r\n1 HASH GROUP BY\r\n2 HASH JOIN\r\n3 TABLE ACCESS FULL PRODUCTS\r\n4 PARTITION RANGE ALL\r\n5 TABLE ACCESS FULL SALES\r\n------------------------------------------<\/pre>\n
<\/p>\n
The arguments used by DBMS_XPLAN.DISPLAY_CURSOR are: \n\u2022 SQL ID (default NULL, which means the last SQL statement executed in this session) \n\u2022 Child number (default 0) \n\u2022 Format (default ‘TYPICAL’) \nThe details are in $ORACLE_HOME\/rdbms\/admin\/dbmsxpln.sql.<\/p>\n
<\/p>\n
Example 3:<\/strong> Displaying the execution plan for any other statement requires the SQL ID to be provided, either directly or indirectly:<\/p>\n
If the above statement has been executed more than once, a SQL Plan Baseline will be created for it and you can verified this using the follows query:<\/p>\n
The output of either of these two statements is:<\/p>\n
----------------------------------------------------------------------------\r\nSQL handle: SYS_SQL_1899bb9331ed7772\r\nSQL text: select prod_category, avg(amount_sold) from sales s, products p\r\nwhere p.prod_id = s.prod_id group by prod_category\r\n----------------------------------------------------------------------------\r\n----------------------------------------------------------------------------\r\nPlan name: SYS_SQL_PLAN_31ed7772f2c7a4c2\r\nEnabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE\r\n----------------------------------------------------------------------------\r\nPlan hash value: 4073170114\r\n---------------------------------------------------------\r\nId Operation Name\r\n---------------------------------------------------------\r\n0 SELECT STATEMENT\r\n1 HASH GROUP BY\r\n2 HASH JOIN\r\n3 VIEW index$_join$_002\r\n4 HASH JOIN\r\n5 INDEX FAST FULL SCAN PRODUCTS_PK\r\n6 INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX\r\n7 PARTITION RANGE ALL\r\n8 TABLE ACCESS FULL SALES\r\n---------------------------------------------------------<\/pre>\n
<\/p>\n
Formatting<\/h3>\n
The format argument is highly customizable and allows you to see as little (high-level) or as much (low-level) details as you need \/ want in the plan output. The high-level options are:<\/p>\n
1. Basic<\/strong> \nThe plan includes the operation, options, and the object name (table, index, MV, etc)<\/p>\n
2. Typical<\/strong> \nIt includes the information shown in BASIC plus additional optimizer-related internal information such as cost, size, cardinality, etc.<\/p>\n
These information are shown for every operation in the plan and represents what the optimizer thinks is the operation cost, the number of rows produced, etc.<\/p>\n
It also shows the predicates evaluation by the operation. There are two types of predicates: ACCESS and FILTER.<\/p>\n
The ACCESS predicates for an index are used to fetch the relevant blocks because they apply to the search columns.<\/p>\n
The FILTER predicates are evaluated after the blocks have been fetched.<\/p>\n
3. All<\/strong> \nIt includes the information shown in TYPICAL plus the lists of expressions (columns) produced by every operation, the hint alias and query block names where the operation belongs.<\/p>\n
The last two pieces of information can be used as arguments to add hints to the statement.<\/p>\n
The low-level options allow the inclusion or exclusion of find details, such as predicates and cost.<\/p>\n
<\/p>\n
For example:<\/strong><\/p>\n
select plan_table_output\r\nfrom table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));\r\n\r\n-------------------------------------------------------\r\nId Operation Name Cost (%CPU)\r\n-------------------------------------------------------\r\n0 SELECT STATEMENT 17 (18)\r\n1 HASH GROUP BY 17 (18)\r\n* 2 HASH JOIN 15 (7)\r\n3 TABLE ACCESS FULL PRODUCTS 9 (0)\r\n4 PARTITION RANGE ALL 5 (0)\r\n5 TABLE ACCESS FULL SALES 5 (0)\r\n-------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n2 - access(\"P\".\"PROD_ID\"=\"S\".\"PROD_ID\")\r\n\r\nselect plan_table_output from\r\ntable(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));<\/pre>\n
<\/p>\n
Note Section<\/strong> \nIn addition to the plan, the package displays notes in the NOTE section, such as that dynamic sampling was used during query optimization or that star transformation was applied to the query.<\/p>\n
For example, if the table SALES did not have statistics then the optimizer will use dynamic sampling and the plan display will report it as follows (see s’+note’ detail in the query):<\/p>\n
select plan_table_output\r\nfrom table(dbms_xplan.display('plan_table',null,'basic +note'));\r\n\r\n------------------------------------------\r\nId Operation Name\r\n------------------------------------------\r\n0 SELECT STATEMENT\r\n1 HASH GROUP BY\r\n2 HASH JOIN\r\n3 TABLE ACCESS FULL PRODUCTS\r\n4 PARTITION RANGE ALL\r\n5 TABLE ACCESS FULL SALES\r\n------------------------------------------\r\nNote\r\n-----\r\n- dynamic sampling used for this statement<\/pre>\n
<\/p>\n
Bind Peeking<\/strong> \nThe query optimizer takes into account the values of bind variable values when generation an execution plan.<\/p>\n
It does what is generally called bind peeking. See the first post in this blog about the concept of bind peeking and its impact on the plans and the performance of SQL statements.<\/p>\n
As stated earlier the plan shown in V$SQL_PLAN takes into account the values of bind variables while the one shown from using EXPLAIN PLAN does not.<\/p>\n
The DBMS_XPLAN package allows the display of the bind variable values used to generate a particular cursor\/plan.<\/p>\n
This is done by adding ‘+peeked_binds’ to the format argument when using display_cursor().<\/p>\n
This is illustrated with the following example:<\/p>\n
variable pcat varchar2(50)\r\nexec :pcat := 'Women'\r\n\r\nselect PROD_CATEGORY, avg(amount_sold)\r\nfrom sales s, products p\r\nwhere p.PROD_ID = s.PROD_ID\r\nand prod_category != :pcat\r\ngroup by PROD_CATEGORY;\r\nselect plan_table_output\r\nfrom table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));\r\n\r\n------------------------------------------\r\nId Operation Name\r\n------------------------------------------\r\n0 SELECT STATEMENT\r\n1 HASH GROUP BY\r\n2 HASH JOIN\r\n3 TABLE ACCESS FULL PRODUCTS\r\n4 PARTITION RANGE ALL\r\n5 TABLE ACCESS FULL SALES\r\n------------------------------------------\r\nPeeked Binds (identified by position):\r\n--------------------------------------\r\n1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'<\/pre>\n\n