Home A - Z
Menu
Page Index

Commit 8ad17b

2025-12-02 12:12:57 feagor: -/-
oracle/awr и ash/AWR - Buffer Hit Ratio в разрезе снапшотов.md ..
@@ 2,17 2,21 @@
```oraclesql
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 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,
0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9