Commit ec3260
2025-12-02 12:17:21 feagor: -/-| /dev/null .. oracle/awr и ash/AWR - Время и количество чтений в разрезе снапшотов.md | |
| @@ 0,0 1,32 @@ | |
| + | ## AWR - Время и количество чтений в разрезе снапшотов |
| + | |
| + | ```oraclesql |
| + | with t as ( |
| + | select |
| + | btime, event_name, |
| + | trunc((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),6) avg_ms, |
| + | (count_end-count_beg) ct |
| + | from ( |
| + | select |
| + | e.event_name, |
| + | cast(s.BEGIN_INTERVAL_TIME as date) btime, |
| + | total_waits count_end, |
| + | time_waited_micro/1000 time_ms_end, |
| + | Lag (e.time_waited_micro/1000) |
| + | OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg, |
| + | Lag (e.total_waits) |
| + | OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg |
| + | from |
| + | DBA_HIST_SYSTEM_EVENT e, |
| + | DBA_HIST_SNAPSHOT s |
| + | where s.snap_id=e.snap_id |
| + | and e.event_name in ( 'db file sequential read','db file scattered read') |
| + | --and e.dbid=&DBID |
| + | and e.dbid=s.dbid |
| + | order by e.event_name, begin_interval_time) |
| + | where (count_end-count_beg) > 0 |
| + | and trunc(btime) >= to_date('01.06.2021','dd.mm.yyyy') |
| + | ) |
| + | select * from t pivot( max(avg_ms) as ms,max(ct) as cnt for event_name in ('db file scattered read' as SCAT_RD, 'db file sequential read' as SEQ_RD)) |
| + | order by btime desc |
| + | ``` |