This post covers how you can use the PL/SQL package DBMS_XPLAN to display execution plan information. If you want to learn more about DBMS_XPLAN options, alternative methods for generating plans as well as HTML and graphical representations, then check out this post too.
Generating and displaying the execution plan of a SQL statement is a common task for most DBAs, SQL developers and performance experts as it provides them information on the performance characteristics of a SQL statement. An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The order of the operators and their implementation is decided by the query optimizer using a combination of query transformations and physical optimization techniques.
While the display is commonly shown in a tabular format, the plan is in fact tree-shaped. 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 this query’s 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
------------------------------------------While the tree-shaped representation of the plan is:
GROUP BY
|
JOIN
_____|_______
| |
ACCESS ACCESS
(PRODUCTS) (SALES)When you read a plan tree you should start from the bottom up. In the above example begin by looking at the access operators (or the leaves of the tree). In this case the access operators are implemented using full table scans. The rows produced by these tables scans will be consumed by the join operator. Here the join operator is a hash-join (other alternatives include nested-loop or sort-merge join). Finally the group-by operator implemented here using hash (alternative would be sort) consumes rows produced by the join-operator.
The execution plan generated for a SQL statement is just one of the many alternative execution plans considered by the query optimizer. The query optimizer selects the execution plan with the lowest cost. The cost model used by the optimizer accounts for the IO, CPU, and network usage in the query. Plans with lower cost are expected to be more efficient in terms of their usage of these resources.
There are two different methods you can use to look at the execution plan of a SQL statement:
- EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually executing the statement.
- V$SQL_PLAN – A dictionary view that 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 the EXPLAIN PLAN command 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.
It’s easy to display an execution plan if you use the DBMS_XPLAN package. This packages provides several PL/SQL procedures to display the plan from different sources:
- EXPLAIN PLAN command
- V$SQL_PLAN
- Automatic Workload Repository (AWR)
- SQL Tuning Set (STS)
- SQL Plan Baseline (SPM)
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.
Example 1: Uses the EXPLAIN PLAN command and the DBMS_XPLAN.DISPLAY function
SQL> EXPLAIN PLAN FOR
2 select prod_category, avg(amount_sold)
3 from sales s, products p
4 where p.prod_id = s.prod_id
5 group by prod_category;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));
------------------------------------------
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 arguments are for DBMS_XPLAN.DISPLAY are:
- Plan table name (default ‘PLAN_TABLE’)
- Statement_id (default NULL)
- Format (default ‘TYPICAL’)
More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.
Example 2: Generating and displaying the execution plan for the last SQL statement executed in a session:
SQL> select prod_category, avg(amount_sold)
2 from sales s, products p
3 where p.prod_id = s.prod_id
4 group by prod_category;
no rows selected
SQL> select plan_table_output
2 from table(dbms_xplan.display_cursor(null,null,'basic'));
------------------------------------------
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 arguments used by DBMS_XPLAN.DISPLAY_CURSOR are:
- SQL ID (default NULL, which means the last SQL statement executed in this session)
- Child number (default 0)
- Format (default ‘TYPICAL’)
The details are in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.
Example 3: Displaying the execution plan for any other statement requires the SQL ID to be provided, either directly or indirectly:
Directly:
SQL> select plan_table_output from
2 table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));Indirectly:
SQL> select plan_table_output
2 from v$sql s,
3 table(dbms_xplan.display_cursor(s.sql_id,
4 s.child_number, 'basic')) t
5 where s.sql_text like 'select PROD_CATEGORY%';
Example 4: Displaying an execution plan corresponding to a SQL Plan Baseline.
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
SQL> select prod_category, avg(amount_sold)
2 from sales s, products p
3 where p.prod_id = s.prod_id
4 group by prod_category;
no rows selectedIf the above statement has been executed more than once, a SQL Plan Baseline will be created for it and you can verifiy this using the following query:
SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED
2 from dba_sql_plan_baselines
3 where sql_text like 'select prod_category%';
SQL_HANDLE PLAN_NAME ACC
------------------------------ ------------------------------ ---
SYS_SQL_1899bb9331ed7772 SYS_SQL_PLAN_31ed7772f2c7a4c2 YESThe execution plan for the SQL Plan Baseline created above can be displayed either directly or indirectly:
Directly
select t.* from
table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
format => 'basic')) tIndirectly
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
---------------------------------------------------------Formatting
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:
- Basic
The plan includes the operation, options, and the object name (table, index, MV, etc) - 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, the 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. - 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 pieces of information can be used as arguments to add hints to the statement.
The low-level options allow the inclusion or exclusion of fine details, such as predicates and cost.
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")Note Section
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 s’+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 statementBind 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 earlier posts on 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. 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'
credit: https://blogs.oracle.com/optimizer/post/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement