某條sql的buffer gets 的歷史月度變化

----統計某條sql的buffer_gets 月度變化----適用於9i庫--

select n.hash_value,n.stime,n.executions_delta_sum,n.buffer_gets_delta_sum,n.avg_buffer_gets,
round ((n.avg_buffer_gets - lag(n.avg_buffer_gets) over (order by n.stime ))/n.avg_buffer_gets*100,2) buffer_get_perc
from
(
select m.hash_value,
       to_char(m.snap_time, 'YYYY-MM') stime,
       sum(m.executions_delta) executions_delta_sum,
       sum(m.buffer_gets_delta) buffer_gets_delta_sum,
       round (sum(m.buffer_gets_delta)/sum(m.executions_delta),2) avg_buffer_gets
  from (select a.hash_value,
               b.snap_time,
               round(a.executions - lag(a.executions)
                     over(order by a.snap_id)) executions_delta,
               round(a.buffer_gets - lag(a.buffer_gets)
                     over(order by a.snap_id)) buffer_gets_delta
          from stats$sql_summary a, stats$snapshot b
         where a.hash_value = '3197777015'
           and a.snap_id = b.snap_id
           and b.snap_time BETWEEN
               TO_DATE('2012/04/01 00:00:05', 'yyyy/mm/dd hh24:mi:ss') AND
               TO_DATE('2012/07/01 00:00:10', 'yyyy/mm/dd hh24:mi:ss')
         order by a.snap_id) M
 where m.executions_delta > 0
   and m.buffer_gets_delta > 0
 group by m.hash_value, to_char(m.snap_time, 'YYYY-MM')
 )N
-- select sysdate,to_char(sysdate,'ww'),to_char(sysdate,'YYYY-MM') from dual;