一、
select sum(executions_DELTA) from dba_hist_sqlstat where SQL_ID='3m7n4vk1f8c43' and snap_id between 54601 and 54625
查询某一时间段的SQL执行次数信息。AWR取值也来自于此。可以进行比较
二、查询SQL执行计划的改变,有几次变化。具体改变的时间。提取plan_hash_value
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='3m7n4vk1f8c43' order by TIMESTAMP;
另外:
还可以这样:(和上面感觉没什么区别,仅作记录)
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 = '3m7n4vk1f8c43'
and a.snap_id = b.snap_id
order by instance_number, snap_id;
三、
通过代入plan_hash_value查出具体的执行计划情况
select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') xx
from DBA_HIST_SQL_PLAN
where sql_id ='3m7n4vk1f8c43'
and plan_hash_value in (2702355013,1401032440,2282877665)
order by xx,id