View SQL Execution Plan History in Oracle

2 years ago
Isabella Edwards
1 minute

To view the SQL execution plan history, you can use the following method:

  1. Using Oracle’s dynamic performance views: you can check the execution plan of SQL statements by querying the v$sql_plan or v$sql_plan_statistics_all views. To view past execution plans, you can use the following SQL statement:
SELECT * FROM v$sql_plan WHERE sql_id = 'your_sql_id';
  1. Utilize Oracle’s automated reporting tool: Oracle offers an automated reporting tool to view the execution plan of SQL statements. Generate automated reports using the following methods:
-- 设置自动报告
ALTER SYSTEM SET control_management_pack_access = 'DIAGNOSTIC+TUNING' SCOPE = BOTH;
-- 生成自动报告
SELECT dbms_sqltune.report_sql_monitor(report_level => 'ALL') FROM dual;
  1. Utilizing AWR Reports: If Oracle’s Automatic Workload Repository (AWR) is enabled, you can view the execution plan of SQL statements using AWR reports. AWR reports can be generated using the following methods.
-- 生成AWR报告
SELECT dbms_workload_repository.awr_report_text(begin_snap => your_begin_snap_id, end_snap => your_end_snap_id) FROM dual;

By following the above method, you can view the SQL historical execution plan and analyze for optimization.

bannerAds