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

Blame

f2caba	feagor	2025-12-02 12:10:24

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

```oraclesql

with t as (

8ad17b	feagor	2025-12-02 12:12:57

  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

f2caba	feagor	2025-12-02 12:10:24

)

50e86d	feagor	2025-12-02 12:11:31

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

f2caba	feagor	2025-12-02 12:10:24

```