Summary of Oracle execution plan viewing methods and detailed explanation of advantages and disadvantages

In Oracle databases, viewing execution plans is an important tool for optimizing SQL statement performance. The following are several commonly used methods to view execution plans and their advantages and disadvantages:

1. Use EXPLAIN PLAN FOR and DBMS_XPLAN.DISPLAY

method

implementEXPLAIN PLAN FORStatement：

EXPLAIN PLAN FOR
SELECT * FROM your_table WHERE your_column = 'some_value';

View execution plan：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

advantage

• Simple and easy to use: Suitable for most cases, easy to operate.
• Details: Provide detailed execution plan information, including operation type, cost, number of rows, etc.

shortcoming

• Does not reflect actual execution：EXPLAIN PLANIt is just a simulated execution plan and does not necessarily reflect the actual execution.
• Permissions required:needEXPLAIN PLANPermissions.

2. Use DBMS_XPLAN.DISPLAY_CURSOR

method

Execute SQL statements：

SELECT * FROM your_table WHERE your_column = 'some_value';

View execution plan：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

advantage

• Reflect actual execution: Check out the actual execution plan, which can better reflect the actual performance.
• Detailed statistics: It can provide actual execution statistics, such as the number of I/O times, CPU time, etc.

shortcoming

• SQL execution is required: SQL statements must be executed before viewing the execution plan.
• Depend on shared pool: Only the execution plan of SQL statements in the shared pool can be viewed.

3. Use AUTOTRACE (SQL*Plus only)

method

Enable AUTOTRACE：

SET AUTOTRACE ON EXPLAIN;

Execute SQL statements：

SELECT * FROM your_table WHERE your_column = 'some_value';

Disable AUTOTRACE：

SET AUTOTRACE OFF;

advantage

• Integrated in SQL*Plus: Suitable for SQL*Plus users, easy to operate.
• Instant feedback: Display execution plan immediately when executing SQL statements.

shortcoming

• SQL*Plus only: Only used in SQL*Plus.
• Limited functions: Not as good asDBMS_XPLAN.DISPLAYThe information provided is detailed.

4. Use the V$SQL_PLAN view

method

Find SQL_ID of SQL statement：

SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%your_sql_statement%';

Query the execution plan：

SELECT * FROM v$sql_plan WHERE sql_id = 'your_sql_id';

advantage

• High flexibility: You can query the views directly, with high flexibility.
• Real-time information: You can view the execution plan of the currently executing SQL statement.

shortcoming

• Complexity: You need to manually query the view, and the operation is relatively complicated.
• Information redundancy: There is a lot of information returned, and you need to filter out useful parts.

5. Using Oracle Enterprise Manager (OEM)

method

• Log in OEM。
• Navigate to the SQL Performance Page。
• Enter SQL statements and view execution plan。

advantage

• Graphic interface: Provides a graphical user interface that is easy to understand and operate.
• Comprehensive information: You can view a variety of performance metrics, not just execution plans.

shortcoming

• Requires OEM: Oracle Enterprise Manager needs to be installed and configured.
• Resource consumption: The graphical interface may consume more system resources.

6. Use DBMS_XPLAN.DISPLAY_AWR

method

Find SQL_ID and PLAN_HASH_VALUE of SQL statements：

SELECT sql_id, plan_hash_value FROM dba_hist_sqlstat WHERE sql_text LIKE '%your_sql_statement%';

Query the execution plan：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('your_sql_id', 'your_plan_hash_value'));

advantage

• Historical information: You can view historical execution plans in AWR, which helps with long-term performance analysis.
• Detailed statistics: Provide detailed execution statistics.

shortcoming

• Requires AWR: The AWR function is required to be enabled and the corresponding permissions are required.
• Complexity: The operation is relatively complicated, and it is necessary to find SQL_ID and PLAN_HASH_VALUE.

7. Use Event 10046 Tracking

method

Enable Event 10046 Tracking：

For the current session:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

For a specific session (assuming SID is 123 and SERIAL# is 456):

EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, TRUE);

Execute SQL statements：

SELECT * FROM your_table WHERE your_column = 'some_value';

Disable Event 10046 Tracking：

For the current session:

ALTER SESSION SET EVENTS '10046 trace name context off';

For a specific session (assuming SID is 123 and SERIAL# is 456):

EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, FALSE);

View tracking files：

Find the location of the trace file, usually in

user_dump_dest

Parameters specified in the directory.

use

tkprof

Tool formats tracking files:

tkprof trace_file.trc output_file.txt explain=your_username/your_password

View generatedoutput_file.txtFile containing detailed execution plan and performance information.

advantage

• Details: Provide detailed execution plan, execution time and waiting events and other information, which helps to deeply analyze performance issues.
• flexibility: Tracking can be enabled for a specific session or for the current session.
• Historical information: It can retain long-term tracking information for easy subsequent analysis.

shortcoming

• Performance overhead: Enable tracking increases system overhead, especially in high load situations.
• Complexity: The operation is relatively complex, and it requires manual activation and disabling of tracking, as well as the usetkprofFormat the tracking file.
• File Management: The generated trace files need to be managed and cleaned to avoid taking up too much disk space.

8. Use STATISTICS_LEVEL=ALL

method

Set the statistics level to ALL：

ALTER SESSION SET STATISTICS_LEVEL=ALL;

Execute SQL statements：

SELECT * FROM your_table WHERE your_column = 'some_value';

View execution plan and statistics：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

advantage

• Detailed statistics: It can provide detailed execution plan and statistical information, including I/O times, CPU time, etc.
• Reflect actual execution: Check out the actual execution plan, which can better reflect the actual performance.
• Easy to operate: Just set the statistics level and execute the SQL statement.

shortcoming

• Performance overhead:set upSTATISTICS_LEVELforALLIt will increase the performance overhead of executing SQL statements.
• Temporary settings: Only valid for the current session and needs to be set manually in each session.

Summarize

Applicable scenarios

• EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY: Suitable for simple query optimization and quickly view execution plans.
• DBMS_XPLAN.DISPLAY_CURSOR: Applicable to SQL statements that have been executed, and you need to check the actual execution status.
• AUTOTRACE: Suitable for SQL*Plus users, requiring quick feedback.
• V$SQL_PLAN: Suitable for scenarios where flexible query execution plans are required.
• Oracle Enterprise Manager (OEM): Suitable for scenarios where graphical interfaces and comprehensive performance information are required.
• DBMS_XPLAN.DISPLAY_AWR: Suitable for scenarios where you need to view historical execution plans.
• Event 10046 Tracking: Suitable for scenarios where performance issues need to be analyzed in depth, especially involving execution time and waiting events.
• STATISTICS_LEVEL=ALL: Applicable to scenarios where detailed statistical information and reflect actual execution, it is simple to operate but has performance overhead.

The above is the detailed content of the summary of Oracle execution plan viewing methods and comparison of advantages and disadvantages. For more information about Oracle execution plan viewing methods, please pay attention to my other related articles!