Commit f2caba
2025-12-02 12:10:24 feagor: -/-| /dev/null .. oracle/awr и ash/AWR - Buffer Hit Ratio в разрезе снапшотов.md | |
| @@ 0,0 1,20 @@ | |
| + | ## AWR - Buffer Hit Ratio в разрезе снапшотов |
| + | |
| + | ```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 t.start_dt,buffer_hit_ratio hit_g, trunc( |
| + | (1-((ph_r_delta)/(gets_delta+c_gets_delta)))*100 |
| + | ,2) hit_l FROM t |
| + | ``` |