select PLAN_HASH_VALUE,FIRST_LOAD_TIME from v$sql where sql_id='bksyj647thx15'
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='3w2x40159yksh' order by TIMESTAMP;
历史统计信息变化：
select * from dba_TAB_STATS_HISTORY where TABLE_NAME='DWE_TASKS'; ----------统计信息历史
select OWNER,TABLE_NAME,LAST_ANALYZED,NUM_ROWS from dba_tables where TABLE_NAME='DWE_TASKS';
select OBJ# ,ROWCNT,BLKCNT,AVGRLN,SAMPLESIZE,ANALYZETIME,SAVTIME from sys.WRI$_OPTSTAT_TAB_HISTORY where obj#=3459727;
示plan_hash_value的执行计划：
select * from table(DBMS_XPLAN.DISPLAY_AWR('3w2x40159yksh',format=>'all'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('3w2x40159yksh','2105950411'));
select * from table(dbms_xplan.display_cursor('3w2x40159yksh'));
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/