awrstat_timeseeen

col snap_id for a20
with awrstat as (
select a.instance_number inst_id, a.snap_id,'AWR' SOURCE, plan_hash_value , lag(plan_hash_value,1,0) over(order by a.snap_id) as pre_plan_hash_value, executions_delta Exe, round(buffer_gets_delta/decode(executions_delta,0,1,executions_delta),0) bufgets, ELAPSED_TIME_delta/decode(executions_delta,0,1,executions_delta)/(1000) ElaMs
from dba_hist_sqlstat a
where sql_id ='&&sql_id' and executions_delta > 0 union
select inst_id,null, 'Cursor cache' Source, PLAN_HASH_VALUE HASH_VALUE , null, executions Exe, buffer_gets /decode(executions,0,1,executions)/(1000) , ELAPSED_TIME/decode(executions,0,1,executions)/(1000) ElaMs
from gv$sqlarea
where sql_id ='&&sql_id' and executions > 0 order by 2,inst_id ), awrtop as (
select min(Elams) min_elams, max(Elams) max_elams, count(distinct plan_hash_value) plan_cnt
from awrstat )
select '&&sql_id' SQLID , inst_id ||' - ' || a.snap_id Snap_id , to_char(begin_interval_time, 'DD-MON-YY HH24:MI') TimeSeen, Plan_hash_value, pre_plan_hash_value, Exe, case when (Min_elams = ElaMs AND plan_cnt > 1 ) THEN 'Good-Plan' when ( Max_elams = ElaMs AND Plan_cnt > 1 ) THEN 'BAD Plan' ELSE ' ' END as COMMENTS, bufgets, round(ElaMs,3) ElaMs
from dba_hist_snapshot a , awrstat b, awrtop c
where a.snap_id = b.snap_id and Plan_hash_value !=pre_plan_hash_value order by a.snap_id