查询oracle历史执行语句,查看某个语句的历史执行计划

(1)：语句在快照之间

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS',tabname=>'TEST_PLAN',method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);

exec dbms_workload_repository.create_snapshot();

(2)：根据sid找出对应的sql_id

select sid,SQL_HASH_VALUE,SQL_ID,PREV_HASH_VALUE,PREV_SQL_ID from v$session where sid=542;

(3):根据sql_id找到对应的plan_hash_value

select sql_id,

plan_hash_value,

id,

operation,

options,

object_owner,

object_name,

depth,

cost,

timestamp

from DBA_HIST_SQL_PLAN

where sql_id = '36qmvdf7f2xz0'

order by 2, 3;

(4)：根据sql_id 找出执行计划变化的时间点

select a.INSTANCE_NUMBER,

a.snap_id,

a.sql_id,

a.plan_hash_value,

b.begin_interval_time

from dba_hist_sqlstat a, dba_hist_snapshot b

where sql_id = '36qmvdf7f2xz0'

and a.snap_id = b.snap_id

order by instance_number, snap_id;

(5)：想办法固定最优的执行计划，结合等待事件来优化。

阅读(1238) | 评论(0) | 转发(0) |