Home A - Z
Menu
Page Index

Blame

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