OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c redo 많이 발생한 시점 확인
본문에서는 오라클에서 redo가 많이 발생된 시점을 확인하는 쿼리를 설명함
로그스위치가 많이 발생한 시간대 확인 쿼리는 아래 게시물 확인
참고 : 로그 스위치가 시간대별로 몇번 이루어졌는지 확인하는 쿼리 ( https://positivemh.tistory.com/386 )
스냅샷별 redo 많이 발생된 시점 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
set lines 200 pages 1000
select
ss.snap_id,
to_char(sn.begin_interval_time, 'yyyy-mm-dd hh24:mi') begin_time,
to_char(sn.end_interval_time, 'yyyy-mm-dd hh24:mi') end_time,
ss.value - lag(ss.value) over (order by ss.snap_id) as redo_delta
from dba_hist_sysstat ss
join dba_hist_snapshot sn
on ss.snap_id = sn.snap_id
where ss.stat_name = 'redo size'
order by ss.snap_id;
SNAP_ID BEGIN_TIME END_TIME REDO_DELTA
---------- ---------------- ---------------- ----------
73 2025-11-12 22:00 2025-11-12 23:00
74 2025-11-12 23:00 2025-11-13 00:00 3704232
75 2025-11-13 00:00 2025-11-13 01:00 3851496
76 2025-11-13 01:00 2025-11-13 02:00 3754252
77 2025-11-13 02:00 2025-11-13 03:00 3765360
78 2025-11-13 03:00 2025-11-13 04:00 3644508
79 2025-11-13 04:00 2025-11-13 05:00 3365448
80 2025-11-13 05:00 2025-11-13 06:00 3556404
81 2025-11-13 06:00 2025-11-13 07:00 3569308
..
|
시간대별 redo 많이 발생된 시점 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL>
set lines 200 pages 1000
select
begin_time,
end_time,
sum(redo_delta) as redo_total
from (
select
sn.snap_id,
to_char(sn.begin_interval_time, 'yyyy-mm-dd hh24:mi') begin_time,
to_char(sn.end_interval_time, 'yyyy-mm-dd hh24:mi') end_time,
ss.value - lag(ss.value) over (order by sn.snap_id) as redo_delta
from dba_hist_sysstat ss, dba_hist_snapshot sn
where 1=1
and ss.snap_id = sn.snap_id
and ss.stat_name = 'redo size'
) t
group by begin_time, end_time
order by begin_time;
BEGIN_TIME END_TIME REDO_TOTAL
---------------- ---------------- ----------
2025-11-12 22:00 2025-11-12 23:00
2025-11-12 23:00 2025-11-13 00:00 3704232
2025-11-13 00:00 2025-11-13 01:00 3851496
2025-11-13 01:00 2025-11-13 02:00 3754252
2025-11-13 02:00 2025-11-13 03:00 3765360
2025-11-13 03:00 2025-11-13 04:00 3644508
2025-11-13 04:00 2025-11-13 05:00 3365448
2025-11-13 05:00 2025-11-13 06:00 3556404
2025-11-13 06:00 2025-11-13 07:00 3569308
..
|
테스트db는 스냅샷이 한시간 단위라 시간 단위로 표시되지만 스냅샷 간격이 n분 단위인 경우 더 세밀하게 확인 가능함
일별 redo 많이 발생된 시점 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
SQL>
set lines 200 pages 1000
select
day_key as day,
sum(redo_delta) as redo_total
from (
select
sn.snap_id,
to_char(sn.begin_interval_time, 'yyyy-mm-dd') day_key,
ss.value - lag(ss.value) over (order by sn.snap_id) as redo_delta
from dba_hist_sysstat ss, dba_hist_snapshot sn
where 1=1
and ss.snap_id = sn.snap_id
and ss.stat_name = 'redo size'
) t
group by day_key
order by day_key;
DAY REDO_TOTAL
---------- ----------
2025-11-12 3704232
2025-11-13 1008844924
2025-11-14 145788340
2025-11-15 235761860
2025-11-16 239406648
2025-11-17 -2.433E+09
2025-11-19 131985052
2025-11-20 181135084
2025-11-21 47257308
9 rows selected.
|
일별 최대 redo 발생된 시점 스냅샷 구간 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
SQL>
set lines 200 pages 1000
select *
from (
select
day_key,
to_char(begin_interval_time, 'yyyy-mm-dd hh24:mi') begin_time,
to_char(end_interval_time, 'yyyy-mm-dd hh24:mi') end_time,
redo_delta,
row_number() over (partition by day_key order by redo_delta desc) rn
from (
select
sn.snap_id,
to_char(sn.begin_interval_time, 'yyyy-mm-dd') day_key,
sn.begin_interval_time,
sn.end_interval_time,
ss.value - lag(ss.value) over (order by sn.snap_id) as redo_delta
from dba_hist_sysstat ss, dba_hist_snapshot sn
where 1=1
and ss.snap_id = sn.snap_id
and ss.stat_name = 'redo size') t
)
where rn = 1
order by day_key;
DAY_KEY BEGIN_TIME END_TIME REDO_DELTA RN
---------- ---------------- ---------------- ---------- ----------
2025-11-12 2025-11-12 22:00 2025-11-12 23:00 1
2025-11-13 2025-11-13 08:00 2025-11-13 09:00 866497104 1
2025-11-14 2025-11-14 21:00 2025-11-14 22:00 36955520 1
2025-11-15 2025-11-15 10:00 2025-11-15 11:00 40325420 1
2025-11-16 2025-11-16 05:00 2025-11-16 06:00 45494956 1
2025-11-17 2025-11-17 03:00 2025-11-17 04:00 4467320 1
2025-11-19 2025-11-19 22:00 2025-11-19 23:00 39659404 1
2025-11-20 2025-11-20 21:00 2025-11-20 22:00 67721180 1
2025-11-21 2025-11-21 00:00 2025-11-21 01:00 3836000 1
9 rows selected.
|
참고용
현재 redo를 많이 발생시킨 세션 찾는 쿼리
(현재 접속되어 있는 세션만(ACTIVE/INACTIVE) 보이고 과거에 수행했던 쿼리는 안나옴)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
SQL>
set lines 200 pages 1000
col username for a15
col program for a30
col module for a25
col machine for a25
select
s.sid, s.serial#, s.username,
round(st.value/1024/1024, 2) redo_mb,
nvl(i.block_changes, 0) block_changes,
s.status,
to_char(s.logon_time, 'yyyy-mm-dd hh24:mi') logon_time,
--s.type,
substr(s.program, 1, 30) program,
--s.module, s.machine,
s.sql_id, s.prev_sql_id
from v$session s, v$sesstat st, v$statname sn, v$sess_io i
where sn.name = 'redo size'
and sn.statistic# = st.statistic#
and st.sid = s.sid
and i.sid = s.sid
and st.value > 0
--and s.type = 'USER'
--and s.status = 'ACTIVE'
order by redo_mb desc;
SID SERIAL# USERNAME REDO_MB BLOCK_CHANGES STATUS LOGON_TIME PROGRAM SQL_ID PREV_SQL_ID
---------- ---------- --------------- ---------- ------------- -------- ---------------- ------------------------------ ------------- -------------
16 478 34.92 19689 ACTIVE 2025-11-19 16:21 oracle@ora19fs (MMON) aykvshm7zsabd
36 56088 IMSI 33.8 16297 INACTIVE 2025-11-21 13:23 sqlplus@ora19fs (TNS V1-V3) fv67xwssrfz0n
246 3538 .82 0 ACTIVE 2025-11-19 16:21 oracle@ora19fs (DBW0)
264 28459 .5 991 ACTIVE 2025-11-19 16:21 oracle@ora19fs (W003) 9x2prazfz86dz
272 45159 .5 185 ACTIVE 2025-11-21 11:40 oracle@ora19fs (M000) gjaap3w3qbf8c
280 33927 .1 1004 ACTIVE 2025-11-19 16:31 oracle@ora19fs (W007) 9x2prazfz86dz
39 42688 .08 771 ACTIVE 2025-11-19 16:31 oracle@ora19fs (W006) 2360z2hxanh3w
28 31964 .07 649 ACTIVE 2025-11-19 16:21 oracle@ora19fs (W004) 9x2prazfz86dz
250 52467 .06 548 ACTIVE 2025-11-19 16:21 oracle@ora19fs (W000) 9x2prazfz86dz
31 6186 .06 622 ACTIVE 2025-11-19 16:31 oracle@ora19fs (W005) 2360z2hxanh3w
24 22947 .06 543 ACTIVE 2025-11-19 16:21 oracle@ora19fs (W002) 9x2prazfz86dz
252 65097 .05 537 ACTIVE 2025-11-19 16:21 oracle@ora19fs (W001) 2360z2hxanh3w
248 5004 0 9 ACTIVE 2025-11-19 16:21 oracle@ora19fs (SMON) chsyr0gssbuqf
13 rows selected.
|
sql text 확인
|
1
2
3
4
5
|
SQL> select sql_fulltext from v$sql where sql_id = 'fv67xwssrfz0n';
SQL_FULLTEXT
--------------------------------------------------------------------------------
delete testtt
|
결론 :
본문 쿼리를 이용하여 redo가 많이 발생되었던 시점을 확인할 수 있음
참조 :
https://positivemh.tistory.com/386
https://positivemh.tistory.com/637
https://positivemh.tistory.com/714
'ORACLE > Sql' 카테고리의 다른 글
| 오라클 19c sql로 크리스마스 트리 만들기 (0) | 2025.12.24 |
|---|---|
| 오라클 19c 리스너 로그 집계 스크립트 (0) | 2025.11.11 |
| 오라클 19c Hugepages 설정 스크립트 최신버전 및 수동계산 (1) | 2025.09.28 |
| 오라클 19c 유저 100개, 테이블 100개, 인덱스 100개, 데이터 1000건 생성 (0) | 2025.08.10 |
| 오라클 19c 테스트용 테이블스페이스 및 데이터파일 여러개 생성 쿼리 (0) | 2025.07.28 |