Oracle檢視sql_id 的歷史執行計劃

可以透過以下三個檢視檢視歷史執行計劃：

DBA_HIST_SQL_PLAN

DBA_HIST_SQLSTAT

DBA_HIST_SNAPSHOT

透過以下sql 能查出對應sql_id 的歷史執行計劃及變化時間：

select distinct SQL_ID,

PLAN_HASH_VALUE,

to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP

from dba_hist_sql_plan

where SQL_ID = '7wh3566763q49'

order by TIMESTAMP;

透過以下sql 能查出執行計劃哪些地方出現了變化：

col options for a15

col operation for a20

col object_name for a20

select distinct SQL_ID,

PLAN_HASH_VALUE,

to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP

from dba_hist_sql_plan

where SQL_ID = '7wh3566763q49'

order by TIMESTAMP;