AWR - Buffer Hit Ratio в разрезе снапшотов (f2caba)

AWR - Buffer Hit Ratio в разрезе снапшотов

with t as (
SELECT s.BEGIN_INTERVAL_TIME start_dt,
       trunc(
         (1-((physical_reads)/(db_block_gets+consistent_gets)))*100
         ,2) buffer_hit_ratio ,
       physical_reads ph_r,lead(physical_reads) over (order by s.SNAP_ID) ph_r_next, lead(physical_reads) over (order by s.SNAP_ID) - physical_reads ph_r_delta,
       db_block_gets gets, lead(db_block_gets) over (order by s.SNAP_ID) gets_next,lead(db_block_gets) over (order by s.SNAP_ID) - db_block_gets gets_delta,
       consistent_gets c_gets, lead(consistent_gets) over (order by s.SNAP_ID) c_gets_next, lead(consistent_gets) over (order by s.SNAP_ID)- consistent_gets c_gets_delta
FROM dba_hist_buffer_pool_stat t
     join dba_hist_snapshot s on s.SNAP_ID = t.SNAP_ID
where trunc(BEGIN_INTERVAL_TIME) >= to_date('25.03.2024','dd.mm.yyyy')
order by 1
)
SELECT t.start_dt,buffer_hit_ratio hit_g, trunc(
         (1-((ph_r_delta)/(gets_delta+c_gets_delta)))*100
         ,2) hit_l FROM t