Home A - Z
Menu
Page Index

Blame

ec3260 feagor 2025-12-02 12:17:21 1 
## AWR - Время и количество чтений в разрезе снапшотов
2
3
```oraclesql
4
with t as (
5
select
6
btime, event_name,
7
trunc((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),6) avg_ms,
8
(count_end-count_beg) ct
9
from (
10
select
11
e.event_name,
12
cast(s.BEGIN_INTERVAL_TIME as date) btime,
13
total_waits count_end,
14
time_waited_micro/1000 time_ms_end,
15
Lag (e.time_waited_micro/1000)
16
OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,
17
Lag (e.total_waits)
18
OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg
19
from
20
DBA_HIST_SYSTEM_EVENT e,
21
DBA_HIST_SNAPSHOT s
22
where s.snap_id=e.snap_id
23
and e.event_name in ( 'db file sequential read','db file scattered read')
24
--and e.dbid=&DBID
25
and e.dbid=s.dbid
26
order by e.event_name, begin_interval_time)
27
where (count_end-count_beg) > 0
28
and trunc(btime) >= to_date('01.06.2021','dd.mm.yyyy')
29
)
30
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))
31
order by btime desc
32
```