某条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;

 

----统计某条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, 'WW') 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, 'WW')
 )N
-- select sysdate,to_char(sysdate,'ww'),to_char(sysdate,'YYYY-MM') from dual;

 

 

----统计某条sql的buffer_gets 月度变化----适用于10g库--

 select m.*,
round((m.avg_buffer_gets - lag(m.avg_buffer_gets) over (order by m.stime))/m.avg_buffer_gets *100,2)  buffer_gets_perc
from
(
select s.sql_id,
       to_char(sn.begin_interval_time, 'YYYYMM') stime,
       sum(s.buffer_gets_delta) buffer_gets_delta,
       sum(s.executions_delta) executions_delta,
       --round(s.buffer_gets_delta/s.rows_processed_delta,2) avg_rows_per_buffergets,
       round(sum(s.buffer_gets_delta) /sum(s.executions_delta), 2) avg_buffer_gets
  from dba_hist_snapshot sn, sys.WRH$_SQLSTAT s
where sn.snap_id = s.snap_id
  and s.sql_id = '32gwz557f2fj3'
/*
('32gwz557f2fj3',
'1ydby3vmrrc2a',
'2c93jk7wgr4vu',
'8ufxhfhg5p2gv',
'3x30pc8mjrbdb',
'axnpu3jgp2a51'
)
*/
and s.executions_delta > 0
--and  sn.begin_interval_time between to_date('2012-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-09-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by sql_id,to_char(sn.begin_interval_time, 'YYYYMM')
order by 2
)  M
order by a.sql_id,a.stime

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22578826/viewspace-745208/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22578826/viewspace-745208/