Introduction
The purpose of the Oracle Optimizer is to determine the most efficient execution plan for your queries. It makes these decisions based on the statistical information it has about your data and by leveraging Oracle database features such as hash joins, parallel query, partitioning, etc.
Still it isexpected that the optimizer will generate sub-optimal plans for some SQL statements now and then. In cases where there is an alternative plan that performed better than the plan generated by the optimizer, the first step in diagnosing why the Optimizer picked the sub-optimal plan is to visually inspect both of the execution plans.
Examining the different aspects of an execution plan, from selectivity to parallel execution and understanding what information you should be gleaming from the plan can be overwhelming even for the most experienced DBA. This paper
offers a detailed explanation about each aspect of the execution plan and an insight into what caused the CBO to make the decision it did.
Setup
If it is not already present create the SCOTT schema.
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql
Create a PLAN_TABLE if it does not already exist.
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;
EXPLAIN PLAN command - This displays an execution plan for a SQL statement without actually executing the statement.
V$SQL_PLAN - A dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored 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 since the introduction of the DBMS_XPLAN package in Oracle 9i and by the enhancements made to it in subsequent releases. This package provides several PL/SQL interfaces 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)
Using the EXPLAIN PLAN command and the DBMS_XPLAN.DISPLAY function
The following examples illustrate how to generate and display an execution plan for our original
SQLstatement using the different functions provided in the DBMS_XPLAN package.
The arguments for DBMS_XPLAN.DISPLAY are:
• plan table name (default 'PLAN_TABLE')
• statement_id (default null means the last statement inserted into the plan table)
• format, controls the amount of information displayed (default is 'TYPICAL')
To leverage the explain plan functionality you need the appropriate privileges to run the actual statement you are trying to explain. A default PLAN_TABLE exists for every user without the need to create it beforehand.
SQL> explain plan for
2 select a.deptno , avg(b.sal) ,c.empno
3 from dept a, bonus b, emp c
4 where a.deptno=c.deptno and b.ename=b.ename
5 group by c.empno,a.deptno;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 253314618
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 6 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 30 | 6 (17)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 30 | 5 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN| | 1 | 27 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BONUS | 1 | 20 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 14 | 98 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."ENAME" IS NOT NULL)
7 - access("A"."DEPTNO"="C"."DEPTNO")
20 rows selected.
DISPLAY_CURSOR Function
In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these. It accepts three optional parameters:
sql_id - The SQL_ID of the statement in the cursor cache. The SQL_ID as available from the V$SQL and V$SQLAREA views, or from the V$SESSION view using the PREV_SQL_ID column. If omitted, the last cursor executed by the session is displayed.
child_number - The child number of the cursor specified by the SQL_ID parameter. If not specified, all cursors for the specified SQL_ID are diaplyed.
format - In addition to the setting available for the DISPLAY function, this function also has 'RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively.
These additional format options require "STATISTICS_LEVEL=ALL".
DISPLAY Function
The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain an SQL statement.
CONN scott/tiger
SQL> select * from table (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID 9r63us1r3wh3c, child number 0
-------------------------------------
select a.deptno , avg(b.sal) ,c.empno from dept a, bonus b, emp c
where a.deptno=c.deptno and b.ename=b.ename group by c.empno,a.deptno
Plan hash value: 253314618
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 30 | 6 (17)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 30 | 5 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN| | 1 | 27 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BONUS | 1 | 20 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 14 | 98 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| |
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / B@SEL$1
6 - SEL$1 / C@SEL$1
7 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
FULL(@"SEL$1" "C"@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "B"@"SEL$1" "C"@"SEL$1" "A"@"SEL$1")
USE_MERGE(@"SEL$1" "C"@"SEL$1")
USE_NL(@"SEL$1" "A"@"SEL$1")
END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."ENAME" IS NOT NULL)
7 - access("A"."DEPTNO"="C"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
1 - "C"."EMPNO"[NUMBER,22], "A"."DEPTNO"[NUMBER,22], AVG("B"."SAL")[22]
2 - "B"."SAL"[NUMBER,22], "C"."EMPNO"[NUMBER,22],
"A"."DEPTNO"[NUMBER,22]
3 - "B"."SAL"[NUMBER,22], "C"."EMPNO"[NUMBER,22],
"C"."DEPTNO"[NUMBER,22]
4 - "B"."SAL"[NUMBER,22]
5 - (#keys=0) "C"."EMPNO"[NUMBER,22], "C"."DEPTNO"[NUMBER,22]
6 - "C"."EMPNO"[NUMBER,22], "C"."DEPTNO"[NUMBER,22]
7 - "A"."DEPTNO"[NUMBER,22]
65 rows selected.
Extending DBMS_XPLAN
Adrian Billington has created an "XPlan Utility", available here, to extend the output of DBMS_XPLAN to
include the execution order of the steps. The following output shows the difference between the default
output and that produced by Adrian's XPlan Utility.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.sal=1500
6 ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 351108634
---------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | | 5 | SELECT STATEMENT | | 1 | 117 | 4 (0)| 00:00:01 |
| 1 | 0 | 4 | NESTED LOOPS | | 1 | 117 | 4 (0)| 00:00:01 |
|* 2 | 1 | 1 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 |
| 3 | 1 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 |
|* 4 | 3 | 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL"=1500)
4 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
About
------
- XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net/utilities.php)
The purpose of the Oracle Optimizer is to determine the most efficient execution plan for your queries. It makes these decisions based on the statistical information it has about your data and by leveraging Oracle database features such as hash joins, parallel query, partitioning, etc.
Still it isexpected that the optimizer will generate sub-optimal plans for some SQL statements now and then. In cases where there is an alternative plan that performed better than the plan generated by the optimizer, the first step in diagnosing why the Optimizer picked the sub-optimal plan is to visually inspect both of the execution plans.
Examining the different aspects of an execution plan, from selectivity to parallel execution and understanding what information you should be gleaming from the plan can be overwhelming even for the most experienced DBA. This paper
offers a detailed explanation about each aspect of the execution plan and an insight into what caused the CBO to make the decision it did.
Setup
If it is not already present create the SCOTT schema.
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql
Create a PLAN_TABLE if it does not already exist.
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;
EXPLAIN PLAN command - This displays an execution plan for a SQL statement without actually executing the statement.
V$SQL_PLAN - A dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored 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 since the introduction of the DBMS_XPLAN package in Oracle 9i and by the enhancements made to it in subsequent releases. This package provides several PL/SQL interfaces 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)
Using the EXPLAIN PLAN command and the DBMS_XPLAN.DISPLAY function
The following examples illustrate how to generate and display an execution plan for our original
SQLstatement using the different functions provided in the DBMS_XPLAN package.
The arguments for DBMS_XPLAN.DISPLAY are:
• plan table name (default 'PLAN_TABLE')
• statement_id (default null means the last statement inserted into the plan table)
• format, controls the amount of information displayed (default is 'TYPICAL')
To leverage the explain plan functionality you need the appropriate privileges to run the actual statement you are trying to explain. A default PLAN_TABLE exists for every user without the need to create it beforehand.
SQL> explain plan for
2 select a.deptno , avg(b.sal) ,c.empno
3 from dept a, bonus b, emp c
4 where a.deptno=c.deptno and b.ename=b.ename
5 group by c.empno,a.deptno;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 253314618
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 6 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 30 | 6 (17)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 30 | 5 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN| | 1 | 27 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BONUS | 1 | 20 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 14 | 98 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."ENAME" IS NOT NULL)
7 - access("A"."DEPTNO"="C"."DEPTNO")
20 rows selected.
DISPLAY_CURSOR Function
In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these. It accepts three optional parameters:
sql_id - The SQL_ID of the statement in the cursor cache. The SQL_ID as available from the V$SQL and V$SQLAREA views, or from the V$SESSION view using the PREV_SQL_ID column. If omitted, the last cursor executed by the session is displayed.
child_number - The child number of the cursor specified by the SQL_ID parameter. If not specified, all cursors for the specified SQL_ID are diaplyed.
format - In addition to the setting available for the DISPLAY function, this function also has 'RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively.
These additional format options require "STATISTICS_LEVEL=ALL".
DISPLAY Function
The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain an SQL statement.
CONN scott/tiger
SQL> select * from table (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID 9r63us1r3wh3c, child number 0
-------------------------------------
select a.deptno , avg(b.sal) ,c.empno from dept a, bonus b, emp c
where a.deptno=c.deptno and b.ename=b.ename group by c.empno,a.deptno
Plan hash value: 253314618
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 30 | 6 (17)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 30 | 5 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN| | 1 | 27 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BONUS | 1 | 20 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 14 | 98 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| |
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / B@SEL$1
6 - SEL$1 / C@SEL$1
7 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
FULL(@"SEL$1" "C"@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "B"@"SEL$1" "C"@"SEL$1" "A"@"SEL$1")
USE_MERGE(@"SEL$1" "C"@"SEL$1")
USE_NL(@"SEL$1" "A"@"SEL$1")
END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."ENAME" IS NOT NULL)
7 - access("A"."DEPTNO"="C"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
1 - "C"."EMPNO"[NUMBER,22], "A"."DEPTNO"[NUMBER,22], AVG("B"."SAL")[22]
2 - "B"."SAL"[NUMBER,22], "C"."EMPNO"[NUMBER,22],
"A"."DEPTNO"[NUMBER,22]
3 - "B"."SAL"[NUMBER,22], "C"."EMPNO"[NUMBER,22],
"C"."DEPTNO"[NUMBER,22]
4 - "B"."SAL"[NUMBER,22]
5 - (#keys=0) "C"."EMPNO"[NUMBER,22], "C"."DEPTNO"[NUMBER,22]
6 - "C"."EMPNO"[NUMBER,22], "C"."DEPTNO"[NUMBER,22]
7 - "A"."DEPTNO"[NUMBER,22]
65 rows selected.
Extending DBMS_XPLAN
Adrian Billington has created an "XPlan Utility", available here, to extend the output of DBMS_XPLAN to
include the execution order of the steps. The following output shows the difference between the default
output and that produced by Adrian's XPlan Utility.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.sal=1500
6 ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 351108634
---------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | | 5 | SELECT STATEMENT | | 1 | 117 | 4 (0)| 00:00:01 |
| 1 | 0 | 4 | NESTED LOOPS | | 1 | 117 | 4 (0)| 00:00:01 |
|* 2 | 1 | 1 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 |
| 3 | 1 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 |
|* 4 | 3 | 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL"=1500)
4 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
About
------
- XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net/utilities.php)
No comments:
Post a Comment