Home A - Z
Menu

Blame

f2caba feagor 2025-12-02 12:10:24
1 
## AWR - Buffer Hit Ratio в разрезе снапшотов
2
3
```oraclesql
4
with t as (
5
SELECT s.BEGIN_INTERVAL_TIME start_dt,
6
trunc(
7
(1-((physical_reads)/(db_block_gets+consistent_gets)))*100
8
,2) buffer_hit_ratio ,
9
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,
10
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,
11
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
12
FROM dba_hist_buffer_pool_stat t
13
join dba_hist_snapshot s on s.SNAP_ID = t.SNAP_ID
14
where trunc(BEGIN_INTERVAL_TIME) >= to_date('25.03.2024','dd.mm.yyyy')
15
order by 1
16
)
50e86d feagor 2025-12-02 12:11:31
17
SELECT t.start_dt,
18
buffer_hit_ratio hit_g,
19
trunc((1-((ph_r_delta)/(gets_delta+c_gets_delta)))*100,2) hit_l
20
FROM t
21
f2caba feagor 2025-12-02 12:10:24
22
```