EXPLAIN PLAN – Session 1

 

Execution plan denotes how Optimizer has decided to execute particular SQL.

The EXPLAIN PLAN statement is a DML statement rather than a DDL statement. Therefore, Oracle Database does not implicitly commit the changes made by an EXPLAIN PLAN statement.

About PLAN_TABLE

 

Oracle Database automatically creates a global temporary table PLAN_TABLE$ in the SYS schema, and creates PLAN_TABLE as a synonym.

 

Global temporary table PLAN_TABLE$ in the SYS schema,

 

select owner,table_name,temporary,duration from dba_tables where table_name like 'PLAN_TABLE$';

 

OWNER                                     TABLE_NAME                 TEMPORARY                                    DURATION

-------------------- -------------------- ------------------------------ -----------------------------------------------

SYS                              PLAN_TABLE$                 Y                                                            SYS$SESSION

 

PLAN_TABLE  as a synonym

 

SQL> select owner,object_name,object_type from dba_objects where object_name like 'PLAN_TABLE%';

 

OWNER                                     OBJECT_NAME                                             OBJECT_TYPE

-------------------- -------------------- ----------------------------------------

SYS                              PLAN_TABLE$                                               TABLE

PUBLIC                      PLAN_TABLE                                                 SYNONYM

 

 

All necessary privileges to PLAN_TABLE are granted to PUBLIC. Consequently, every session gets its own private copy of PLAN_TABLE in its temporary tablespace.  

 

SQL> select owner,privilege,grantee,table_name from dba_tab_privs where table_name like 'PLAN_TABLE%';

 

OWNER                   PRIVILEGE  GRANTEE   TABLE_NAME

---------- ---------- ---------- --------------------

SYS            DELETE     PUBLIC           PLAN_TABLE$

SYS            INSERT     PUBLIC           PLAN_TABLE$

SYS            SELECT     PUBLIC           PLAN_TABLE$

SYS            UPDATE     PUBLIC         PLAN_TABLE$

 

 

 

When we issue EXPLAIN PLAN statement it’s data populated in session’s PLAN_TABLE.

So to fetch data we can user DBMS_XPLAN.DISPLAY Function.

 

 

Required Privileges to CHECK VALUES FROM PLAN_TABLE using DBMS_XPLAN

 

GRANT SELECT ON v_$session TO user;
GRANT SELECT ON v_$sql_plan_statistics_all TO user;
GRANT SELECT ON v_$sql_plan TO user;
GRANT SELECT ON v_$sql TO user;

 

 

 

Basic Methods to Display Execution plan for EXPLAINED SQL .

 

After EXPLAINING SQL-ID we can use below mentioned methods to see execution plans.

 

 

 

1=> DBMS_XPLAN.DISPLAY()

 

 

 

 

2=> Using Oracle supplied SQL script as utlxpls.sql

 

 

3=> Using Oracle supplied SQL script as utlxplp.sql

 

DBMS_XPLAN

The HEART of EXPLAIN PLAN / EXECUTION PLAN.

The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats.

 

DBMS_XPLAN Subprograms

Below mentioned are list of Subprograms for DBMS_XPLAN Package.

 

Subprogram	Description
COMPARE_PLANS Function	Compares each plan in a list with a reference plan and returns the report
DIFF_PLAN Function	Compares plans
DISPLAY Function	Displays the contents of the plan table
DISPLAY_AWR Function	Displays the contents of an execution plan stored in the AWR
DISPLAY_CURSOR Function	Displays the execution plan of any cursor in the cursor cache
DISPLAY_PLAN Function	Displays the contents of the plan table in a variety of formats with CLOB output type
DISPLAY_SQL_PLAN_BASELINE Function	Displays one or more execution plans for the specified SQL handle of a SQL plan baseline
DISPLAY_SQLSET Function	Displays the execution plan of a given statement stored in a SQL tuning set

 

 

We will see one-by-one important Subprogram with example as below.

 

 

 

 

 

 

DISPLAY Function

This table function displays the contents of the plan table.

After EXPLAINING SQL statement PLAN_Table get populated for the SQL ID related to explain plan

Syntax

DBMS_XPLAN.DISPLAY(

   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',

   statement_id  IN  VARCHAR2  DEFAULT  NULL,

   format        IN  VARCHAR2  DEFAULT  'TYPICAL',

   filter_preds  IN  VARCHAR2 DEFAULT NULL);

Table 212-5 DISPLAY Function Parameters

Parameter	Description
table_name	This parameter defaults to PLAN_TABLE
statement_id	Specifies the statement_id of the plan to be displayed.
format	Controls the level of details for the plan. It accepts the following values: BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option. TYPICAL: This is the default. SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel. ALL: Maximum user level. Format keywords must be separated by either a comma or a space: ROWS,BYTES ,COST,PARTITION,PARALLEL,PREDICATE ,PROJECTION,ALIAS,REMOTE ,NOTE
filter_preds	SQL filter predicate(s) to restrict the set of rows selected from the table where the plan is stored. For example: filter_preds=>'plan_id = 10'

 

 

 

Format clause in DISPLAY subprogram

 

 

1=> Using ALL

 

 

2=> without any parameter i.e TYPICAL

 

 

 

3=> Using “Alias” in format section.

 

 

4=> Using “ALLSTATS” in format clause.

 

 

 

 

 

DISPLAY_CURSOR Function

This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

Syntax

DBMS_XPLAN.DISPLAY_CURSOR(

   sql_id            IN  VARCHAR2  DEFAULT  NULL,

   cursor_child_no   IN  NUMBER    DEFAULT  0, 

   format            IN  VARCHAR2  DEFAULT  'TYPICAL');

 

Parameters

Parameter	Description
sql_id	Specifies the SQL_ID of the SQL statement in the cursor cache.
cursor_child_no	Child number of the cursor to display.
format	Controls the level of details for the plan. It accepts five values: BASIC: Displays the minimum information in the plan TYPICAL: This is the default. SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel. ALL: Maximum user level. ADAPTIVE: Displays the final plan, or the current plan if the execution has not completed. This section includes notes about runtime optimizations that affect the plan, such as switching from a Nested Loops join to a Hash join. Plan lineage. This section shows the plans that were run previously due to automatic reoptimization. It also shows the default plan, if the plan changed due to dynamic plans. Recommended plan. In reporting mode, the plan is chosen based on execution statistics displayed. Note that displaying the recommended plan for automatic reoptimization requires re-compiling the query with the optimizer adjustments collected in the child cursor. Displaying the recommended plan for a dynamic plan does not require this. Dynamic plans. This summarizes the portions of the plan that differ from the default plan chosen by the optimizer. You can add the following keywords to the preceding format options to customize their default behavior. ROWS,BYTES,COST,PARTITION,PARALLEL,PREDICATE,PROJECTION,ALIAS ,REMOTE NOTE   IOSTATS - assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format shows IO statistics for ALL (or only for the LAST as shown below) executions of the cursor. MEMSTATS - Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators. ALLSTATS - A shortcut for 'IOSTATS MEMSTATS' LAST - By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution.

 

 

Format clause in DISPLAY_CURSOR Subprogram.

 

1=> Using “DEFAULT” as TYPICAL

 

 

 

 

 

 

 

2=> Using “ALL”

 

 

3=> Using “ALLSTATS COST NOTE PARTITION ALIAS” for new table which does not have stats.

 

4=> Issue with “ALLSTATS” : It will cumulate A-Rows from all running instance as below.

 

 

Now if we run 2nd time it will cumulate A-rows

 

 

 

To overcome this use “ALLSTATS LAST” always as below.

 

1st Iteration

 

 

 

2nd Iteration

 

 

 

5=> TO check BIND Variable value passed in SQL use “PEEKED_BINDS” as below.

 

 

6=> Passing sql_id as below .

 

 

DISPLAY_AWR Function

This table function displays the contents of an execution plan stored in AWR.

Note:

This function is deprecated. Use DISPLAY_WORKLOAD_REPOSITORY instead. DISPLAY_AWR only works with snapshots for the local DBID, whereas DISPLAY_WORKLOAD_REPOSITORY supports all snapshots inside AWR, including remote and imported snapshots.

 

Syntax

DBMS_XPLAN.DISPLAY_AWR( 

   sql_id            IN      VARCHAR2,

   plan_hash_value   IN      NUMBER DEFAULT NULL,

   db_id             IN      NUMBER DEFAULT NULL,

   format            IN      VARCHAR2 DEFAULT TYPICAL);

Parameters

Parameter	Description
sql_id	Specifies the SQL_ID of the SQL statement
plan_hash_value	Specifies the PLAN_HASH_VALUE of a SQL statement.
db_id	Specifies the database_id
format	Controls the level of details for the plan. It accepts four values: You can add the following keywords to the preceding format options to customize their default behavior. ROWS,BYTES,COST,PARTITION,PARALLEL,PREDICATE,PROJECTION,ALIAS ,REMOTE NOTE

 

1= > Displaying sql_id from awr report.

 

2=> Display AWR for multiple PLAN HASH_VALUES for SQL_ID.

 

Setup for this Scenario

 

1 => we will perform costly Cartesian join as to populate in AWR repository.

2=> During operation first we will execute with 1 Column index and later we will try with 2 Column indexes.

3=> Query has to execute atleast 10 minutes in database to be expensive as per time and Size.

4=> During Setup we need to generate AWR-SNAPSHOT manually and then Flush shared_pool and all.

 

Query => select /*+new_find */ * from EMPLOYEE_data a ,EMPLOYEE_DATA b,EMPLOYEE_DATA C,EMPLOYEE_DATA D  where a.EMP_NO < 5000;

 

SQL> desc EMPLOYEE_DATA

 Name                                                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMP_NO                                                                NOT NULL NUMBER

 EMP_AGE                                                              NOT NULL NUMBER

 

SQL>

 

 

CURRENT_PLAN

 

 

 

 

 

 

 

 

PLAN_AFTER_CREATING 2 COLUMN INDEX

 

 

 

After complete flush let we check is plan is still there

 

 

 

Data in table and INDEX_Details

 

 

 

 

Now let we fetch details from AWR Repository.

 

 

• Using plan hash value in DISPLAY_AWR

DISPLAY_PLAN Function

 

This table function displays the contents of the plan table in a variety of formats with CLOB output type.

Syntax

DBMS_XPLAN.DISPLAY_PLAN (

   table_name       IN    VARCHAR2   DEFAULT 'PLAN_TABLE',

   statement_id     IN    VARCHAR2   DEFAULT NULL,

   format           IN    VARCHAR2   DEFAULT 'TYPICAL',

   filter_preds     IN    VARCHAR2   DEFAULT NULL,

   type             IN    VARCHAR2   DEFAULT 'TEXT')

  RETURN CLOB;

 

 

 

 

 

 

 

 

 

 

COMPARE_PLANS Function

This function compares each plan in a list with a reference plan and returns the report.

Syntax

DBMS_XPLAN.COMPARE_PLANS(

   reference_plan    IN generic_plan_object,

   compare_plan_list IN plan_object_list,

   type              IN VARCHAR2 := 'TEXT',

   level             IN VARCHAR2 := 'TYPICAL',

   section           IN VARCHAR2 := 'ALL')  

 RETURN CLOB;

 

Setup

 

1=> execute query using optimizer_mode=FIRST_ROWS_10

 

SQL> alter session set optimizer_mode=FIRST_ROWS_10;

 

Session altered.

 

SQL> SELECT /*+ comp */ * from EMPLOYEE_DATA;

SQL> EXIT;

 

2=> execute query using optimizer_mode=ALL_ROWS

 

SQL> alter session set optimizer_mode=ALL_ROWS;

 

Session altered.

 

SQL> SELECT /*+ comp */ * from EMPLOYEE_DATA;

 

Check Child details

 

 

 

 

 

 

 

 

 

 

 

CHECK LOADED CURSOR DETAILS FOR EACH CHILD

 

CHILD-> 0

 

 

 

 

CHILD ->1

 

 

 

LET WE COMPARE PLAN.

 

 

SQL> SET PAGESIZE 50000

SET LONG 100000

SET LINESIZE 210

COLUMN report FORMAT a200SQL> SQL> SQL>

SQL>

SQL>

SQL> SELECT :report REPORT FROM DUAL;

 

REPORT

------------------------------------------------------------------------------------------------------------------------------------------

COMPARE PLANS REPORT

---------------------------------------------------------------------------------------------

  Current user                     : SYS

  Total number of plans  : 2

  Number of findings       : 1

---------------------------------------------------------------------------------------------

 

COMPARISON DETAILS

---------------------------------------------------------------------------------------------

 Plan Number                     : 1 (Reference Plan)

 Plan Found                        : Yes

 Plan Source                        : Cursor Cache

 SQL ID                  : f366pc7j8a59m

 Child Number                   : 0

 Plan Database Version  : 19.0.0.0

 Parsing Schema                : "ABHI_TEST"

 SQL Text                              : SELECT /*+ comp */ * from EMPLOYEE_DATA

 

Plan

-----------------------------

 Plan Hash Value  : 2820370849

 

-------------------------------------------------------------------------------

| Id | Operation                      | Name                | Rows | Bytes | Cost | Time     |

-------------------------------------------------------------------------------

|  0 | SELECT STATEMENT       |         |             |              |          2 |               |

|  1 |      INDEX FAST FULL SCAN | IDX_005C0001 |          10 |      80 |      2 | 00:00:01 |

-------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1 (E - Syntax Error (1))

-------------------------------------------------------------------------------

 

 1 -  SEL$1

       E -  comp

---------------------------------------------------------------------------------------------

 Plan Number                     : 2

 Plan Found                        : Yes

 Plan Source                        : Cursor Cache

 SQL ID                  : f366pc7j8a59m

 Child Number                   : 1

 Plan Database Version  : 19.0.0.0

 Parsing Schema                : "ABHI_TEST"

 SQL Text                              : SELECT /*+ comp */ * from EMPLOYEE_DATA

 

Plan

-----------------------------

 

 Plan Hash Value  : 2820370849

 

-------------------------------------------------------------------------------

| Id | Operation                      | Name                | Rows | Bytes | Cost | Time     |

-------------------------------------------------------------------------------

|  0 | SELECT STATEMENT       |         |             |              |          6 |               |

|  1 |      INDEX FAST FULL SCAN | IDX_005C0001 | 4998 | 39984 |         6 | 00:00:01 |

-------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1 (E - Syntax Error (1))

-------------------------------------------------------------------------------

 

 1 -  SEL$1

       E -  comp

 

Comparison Results (1):

-----------------------------

 1. The plans are the same.

 

 

---------------------------------------------------------------------------------------------