Oracle Explain Plan | Understand & Analyze Oracle Execution Plans with DBMS_XPLAN
Introduction
Understanding an Oracle Explain Plan of SQL queries is critical for database optimization. One powerful tool available in Oracle Database is DBMS_XPLAN.DISPLAY_CURSOR,
which provides detailed insights into the execution plans of SQL statements. This blog post explores how to use DBMS_XPLAN.DISPLAY_CURSOR to analyze execution plans
effectively.
What is DBMS_XPLAN.DISPLAY_CURSOR?
DBMS_XPLAN.DISPLAY_CURSOR is a PL/SQL package function that displays the Oracle Explain Plan (execution plan) of a SQL statement that has been executed recently.
This function can help database administrators and developers understand how Oracle processes SQL queries, making it easier to optimize and troubleshoot performance issues.
Using DBMS_XPLAN.DISPLAY_CURSOR
1. Enable Statistics Collection
Before executing your SQL query, ensure that the collection of execution statistics is enabled. This can be done by setting the
STATISTICS_LEVEL parameter to ALL or by using the GATHER_PLAN_STATISTICS hint in your SQL statement.
2. Execute the SQL Query
Run the SQL query that you want to analyze. The execution details will be stored in Oracle’s memory.
-- Execute the complex query with plan statistics
SELECT /*+ GATHER_PLAN_STATISTICS */
d.department_name,
e.first_name,
e.last_name,
e.salary,
p.project_name,
p.start_date,
p.end_date
FROM
departments d
JOIN
employees e ON d.department_id = e.department_id
JOIN
projects p ON e.employee_id = p.employee_id
WHERE
e.salary > 50000
AND p.end_date > SYSDATE - 180
ORDER BY
d.department_name, e.last_name, p.start_date;
3. Retrieve the Oracle Explain Plan from Cursor
After executing the query, use the DBMS_XPLAN.DISPLAY_CURSOR function to retrieve and display the execution plan.
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
This command displays the execution plan along with all the available statistics, such as actual row counts and execution times.
SQL_ID 3x8fmwtfybtsr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ d.department_name,
e.first_name, e.last_name, e.salary, p.project_name,
p.start_date, p.end_date FROM departments d JOIN
employees e ON d.department_id = e.department_id JOIN projects p
ON e.employee_id = p.employee_id WHERE e.salary > 50000 AND
p.end_date > SYSDATE - 180 ORDER BY d.department_name,
e.last_name, p.start_date
Plan hash value: 1773745029
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 39 | | | |
| 1 | SORT ORDER BY | | 1 | 510 | 50 |00:00:00.01 | 39 | 120K| 120K| |
|* 2 | HASH JOIN | | 1 | 510 | 383 |00:00:00.01 | 39 | 1506K| 1506K| 1227K (0)|
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 10 | 10 |00:00:00.01 | 7 | | | |
|* 4 | HASH JOIN | | 1 | 510 | 383 |00:00:00.01 | 32 | 1106K| 1106K| 1388K (0)|
|* 5 | TABLE ACCESS FULL| PROJECTS | 1 | 510 | 510 |00:00:00.01 | 16 | | | |
|* 6 | TABLE ACCESS FULL| EMPLOYEES | 1 | 763 | 763 |00:00:00.01 | 16 | | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
4 - access("E"."EMPLOYEE_ID"="P"."EMPLOYEE_ID")
5 - filter("P"."END_DATE">SYSDATE@!-180)
6 - filter("E"."SALARY">50000)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
DBMS_XPLAN.DISPLAY_CURSOR Parameters
1. SQL_ID
- Description: This parameter specifies the SQL ID of the cursor you want to examine. The SQL ID is a unique identifier for a SQL statement in the shared pool.
- Value in Query:
NULL
- Effect: When set to NULL, Oracle will use the last executed SQL statement in the current session. This is useful when you want to immediately analyze the execution plan of a recently run query without explicitly specifying the SQL ID.
2. CHILD_NUMBER
- Description: This parameter identifies the child cursor of the SQL statement. Child cursors are different execution plans generated for the same SQL text due to differences in execution context, such as different bind variable values or optimizer environments.
- Value in Query:
NULL
- Effect: When set to NULL, Oracle will use the child cursor of the last executed SQL statement. This is useful when you want to analyze the default execution plan without specifying a particular child cursor.
3. FORMAT
- Description: This parameter controls the level of detail and the format of the execution plan output. It can take several values to customize the output, such as
BASIC, TYPICAL, ALL, ALLSTATS, etc.
- Value in Query:
'ALLSTATS'
- Effect: The
'ALLSTATS' option provides detailed statistics about the execution, including actual row counts, elapsed time, CPU time, and I/O statistics for each operation in the execution plan. This level of detail is crucial for performance tuning and understanding the resource consumption of the query.
Understanding Oracle Execution Costs
When using 'ALLSTATS LAST' for the FORMAT parameter, the default display does not include the estimated number of bytes (BYTES) and the estimated plan cost (COST).
However, you can readily include these columns by appending additional predicates to the FORMAT parameter. Each added predicate is preceded by a '+' sign.
-- Display the execution plan with cost and bytes
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3x8fmwtfybtsr', NULL, 'ALLSTATS LAST +cost +bytes'));
---------------------------------------------------------------------
SQL_ID 3x8fmwtfybtsr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ d.department_name,
e.first_name, e.last_name, e.salary, p.project_name,
p.start_date, p.end_date FROM departments d JOIN
employees e ON d.department_id = e.department_id JOIN projects p
ON e.employee_id = p.employee_id WHERE e.salary > 50000 AND
p.end_date > SYSDATE - 180 ORDER BY d.department_name,
e.last_name, p.start_date
Plan hash value: 1773745029
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 14 (100)| 50 |00:00:00.01 | 39 | | | |
| 1 | SORT ORDER BY | | 1 | 510 | 107K| 14 (8)| 50 |00:00:00.01 | 39 | 120K| 120K| |
|* 2 | HASH JOIN | | 1 | 510 | 107K| 13 (0)| 383 |00:00:00.01 | 39 | 1506K| 1506K| 1227K (0)|
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 10 | 400 | 3 (0)| 10 |00:00:00.01 | 7 | | | |
|* 4 | HASH JOIN | | 1 | 510 | 89760 | 10 (0)| 383 |00:00:00.01 | 32 | 1106K| 1106K| 1388K (0)|
|* 5 | TABLE ACCESS FULL| PROJECTS | 1 | 510 | 42330 | 5 (0)| 510 |00:00:00.01 | 16 | | | |
|* 6 | TABLE ACCESS FULL| EMPLOYEES | 1 | 763 | 70959 | 5 (0)| 763 |00:00:00.01 | 16 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
4 - access("E"."EMPLOYEE_ID"="P"."EMPLOYEE_ID")
5 - filter("P"."END_DATE">SYSDATE@!-180)
6 - filter("E"."SALARY">50000)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
You can also eliminate columns from the plan table or other details from the output by incorporating additional predicates to the FORMAT
parameter, preceded by a '-' sign. For instance, the following command removes the E-Rows column and predicate information from below the plan.
-- Display the execution plan with cost and bytes
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3x8fmwtfybtsr', NULL, 'ALLSTATS LAST -rows -predicate'));
Determining the join order using Plan Tree
With DBMS_XPLAN.DISPLAY_CURSOR, the execution plan is presented solely as a table. While it's feasible to deduce the join order by examining
the indentation of tables in the operation column, I prefer utilizing the FORMAT parameter to exhibit the outline information for the plan,
as it offers a clearer view of the join order.
Appending the '+outline' predicate to the FORMAT parameter will yield the outline, which comprises the full set of hints required to replicate the SQL statement.
-- Display the execution plan with Plan Tree
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3x8fmwtfybtsr', NULL, 'ALLSTATS LAST +outline'));
--------------------------------
SQL_ID 3x8fmwtfybtsr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ d.department_name,
e.first_name, e.last_name, e.salary, p.project_name,
p.start_date, p.end_date FROM departments d JOIN
employees e ON d.department_id = e.department_id JOIN projects p
ON e.employee_id = p.employee_id WHERE e.salary > 50000 AND
p.end_date > SYSDATE - 180 ORDER BY d.department_name,
e.last_name, p.start_date
Plan hash value: 1773745029
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 39 | | | |
| 1 | SORT ORDER BY | | 1 | 510 | 50 |00:00:00.01 | 39 | 120K| 120K| |
|* 2 | HASH JOIN | | 1 | 510 | 383 |00:00:00.01 | 39 | 1506K| 1506K| 1227K (0)|
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 10 | 10 |00:00:00.01 | 7 | | | |
|* 4 | HASH JOIN | | 1 | 510 | 383 |00:00:00.01 | 32 | 1106K| 1106K| 1388K (0)|
|* 5 | TABLE ACCESS FULL| PROJECTS | 1 | 510 | 510 |00:00:00.01 | 16 | | | |
|* 6 | TABLE ACCESS FULL| EMPLOYEES | 1 | 763 | 763 |00:00:00.01 | 16 | | | |
-------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('21.1.0')
DB_VERSION('21.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$9E43CB6E")
MERGE(@"SEL$58A6D7F6" >"SEL$3")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$9E43CB6E" "P"@"SEL$2")
FULL(@"SEL$9E43CB6E" "E"@"SEL$1")
FULL(@"SEL$9E43CB6E" "D"@"SEL$1")
LEADING(@"SEL$9E43CB6E" "P"@"SEL$2" "E"@"SEL$1" "D"@"SEL$1")
USE_HASH(@"SEL$9E43CB6E" "E"@"SEL$1")
USE_HASH(@"SEL$9E43CB6E" "D"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$9E43CB6E" "D"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
4 - access("E"."EMPLOYEE_ID"="P"."EMPLOYEE_ID")
5 - filter("P"."END_DATE">SYSDATE@!-180)
6 - filter("E"."SALARY">50000)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
Determining the Bind Variables values
Although my SQL statement lacks bind variables, you can easily display the bind variable values using DBMS_XPLAN.DISPLAY_CURSOR.
Simply include the '+peeked_binds' predicate in the FORMAT parameter.
-- Display the execution plan with bind variable values
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3x8fmwtfybtsr', NULL, 'ALLSTATS LAST +peeked_binds'));
Execution Plan Retrieval: Shared Pool vs AWR
What you can achieve with the shared pool, involves retrieving and displaying execution plans for SQL statements using the DBMS_XPLAN.DISPLAY_CURSOR function.
Similarly, the second option, using AWR (Automatic Workload Repository), provides a means to access and present execution plans for SQL statements, but through the DBMS_XPLAN.DISPLAY_AWR function.
Both methods enable users to examine and analyze execution plans, offering insights into SQL performance and aiding in performance tuning efforts.
-- Display the execution plan using AWR
select * from table(dbms_xplan.display_awr('SQL_ID',null,DBID,'ALL'));
Conclusion
Oracle Explain Plan, with DBMS_XPLAN.DISPLAY_CURSOR is a powerful way to gain insights into SQL execution plans and optimize query performance. By understanding the detailed statistics
provided by this tool, you can make informed decisions to improve the efficiency of your Oracle database operations.
Related content