sql執行計劃是否改變

xychong123發表於2017-01-10
SQL
select SQL_ID,SQL_PLAN_HASH_VALUE,SQL_EXEC_START from V$ACTIVE_SESSION_HISTORY;

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
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20747382/viewspace-2132165/，如需轉載，請註明出處，否則將追究法律責任。

相關文章