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.
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.
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.
STATISTICS_LEVEL
ALL
GATHER_PLAN_STATISTICS
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;
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
NULL
BASIC, TYPICAL, ALL, ALLSTATS
'ALLSTATS'
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.
'ALLSTATS LAST'
FORMAT
-- 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'));
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.
'+outline'
-- 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
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.
'+peeked_binds'
-- Display the execution plan with bind variable values SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3x8fmwtfybtsr', NULL, 'ALLSTATS LAST +peeked_binds'));
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.
DBMS_XPLAN.DISPLAY_AWR
-- Display the execution plan using AWR select * from table(dbms_xplan.display_awr('SQL_ID',null,DBID,'ALL'));
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.