关于执行计划的获取，在大家的工作中，多有交集。在我的博客中，也有关于执行计划的相关文章，介绍了执行计划的基本Oracle-执行计划的关键元素说明及获取方法Oracle-执行计划的关键元素说明。
对于历史执行计划，在这篇做一说明，以便以后与你我都有益。
1 dba_hist_sql_plan
基本就这么个用法，查询的字段呢，和你印象中执行计划基本一样。
select SQL_ID,
PLAN_HASH_VALUE,
ID,
OPERATION,
OBJECT_OWNER,
OBJECT_NAME,
DEPTH,
COST,
TIMESTAMP
from dba_hist_sql_plan
where sql_id = 'sd5f7z54yn623'
and TIMESTAMP >= date '2023-07-01'
and TIMESTAMP <= date '2023-07-27'
order by TIMESTAMP;2 awrsqrpt.sql
重点是它。这里，只举例生成当天的。
AWRSQRPT 是AWR和ASH的补充，可以看到执行计划的细节，oracle的执行计划会随着环境的改变而改变，也会随着数据改变而改变，所以会产生多个执行计划，而AWRSQRPT就是针对这种情况的报告，AWRSQRPT会有多个执行计划。
[oracle@11g000 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 27 19:13:07 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2023666 BAOYWDB 1 baoywdb
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2023666 1 BAOYWDB baoywdb 11g000
Using 2023666 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
baoywdb BAOYWDB 41834 27 Jul 2023 00:00 1
41835 27 Jul 2023 00:30 1
41836 27 Jul 2023 01:00 1
41837 27 Jul 2023 01:30 1
41838 27 Jul 2023 02:00 1
41839 27 Jul 2023 02:30 1
41840 27 Jul 2023 03:00 1
41841 27 Jul 2023 03:30 1
41842 27 Jul 2023 04:00 1
41843 27 Jul 2023 04:30 1
41844 27 Jul 2023 05:00 1
41845 27 Jul 2023 05:30 1
41846 27 Jul 2023 06:00 1
41847 27 Jul 2023 06:31 1
41848 27 Jul 2023 07:00 1
41849 27 Jul 2023 07:31 1
41850 27 Jul 2023 08:00 1
41851 27 Jul 2023 08:30 1
41852 27 Jul 2023 09:00 1
41853 27 Jul 2023 09:30 1
41854 27 Jul 2023 10:00 1
41855 27 Jul 2023 10:30 1
41856 27 Jul 2023 11:00 1
41857 27 Jul 2023 11:30 1
41858 27 Jul 2023 12:00 1
41859 27 Jul 2023 12:30 1
41860 27 Jul 2023 13:00 1
41861 27 Jul 2023 13:30 1
41862 27 Jul 2023 14:00 1
41863 27 Jul 2023 14:30 1
41864 27 Jul 2023 15:00 1
41865 27 Jul 2023 15:30 1
41866 27 Jul 2023 16:00 1
41867 27 Jul 2023 16:30 1
41868 27 Jul 2023 17:00 1
41869 27 Jul 2023 17:30 1
41870 27 Jul 2023 18:00 1
41871 27 Jul 2023 18:30 1
41872 27 Jul 2023 19:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 41834
Begin Snapshot Id specified: 41834
Enter value for end_snap: 41872
End Snapshot Id specified: 41872
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: bb5f3z90yn4t5
SQL ID specified: bb5f3z90yn4t5
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_41834_41872.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrsqlrpt_1_41834_41872.html
# 以下是生成HTML文件的过程，此处省略1万字。
...
