[오라클] Redo Log 튜닝 방법

log buffer space

---

리두 버퍼에 리두 레코드를 기록하려는 프로세스는 리두 버퍼 내에 필요한 공간을 확보하기 위해 redo allocation 래치를 획득해야 합니다. redo allocation 래치를 획득한 상태에서 리두 버퍼에 공간을 확보하려는 순간에 적절한 여유공간이 없는 경우, 공간이 확보되기를 기다려야합니다. 이 때, 경우에 따라 두가지 종류의 이벤트를 대기하게 됩니다. 만일 현재 사용 중인 리두 로그 파일이 꽉 차서 더이상 여유 공간을 확보할수 없다면, LGWR 프로세스는 로그 파일 스위치를(log file swtich)를 수행하고, 서버 프로세스는 log file switch completion 이벤트를 대기합니다. 그외의 경우에는 log buffer space 이벤트를 대기하게 됩니다. 전자의 경우, 로그 파일 스위치가 끝난 직후 log buffer space 대기가 한번에 증가하는 현상이 생길 수 있습니다. 이는 리두 버퍼에 기록을 하려는 다수의 세션들이 로그 파일 스위치가 끝나기를 기다렸다가 한번에 리두 버퍼에 기록을 하기 위해 경쟁하기 때문입니다.

* Wait Time

일반적으로 1초를 대기하나 log file switch가 발생하는 경우 5초를 대기한다.

* Parameter

log buffer space 대기 이벤트는 대기 파라미터를 사용하지 않는다.

* Common Causes and Actions

 - 원인: 생성되는 리두의 양에 비해 로그버퍼의 크기가 작은 경우

 - 진단 방법: Redo Size 지표 모니터링 및 LOG_BUFFER 파라미터 Size 확인

 - 개선 방법: LOG_BUFFER 크기를 충분히 크게 한다.

Oracle Version에 따른 MView 사용과 Redo 발생량

10g 이상 버전에서 Materialized View(MView)의 Refresh 방식으로 COMPLETE를 사용한다면 DBMS_MVIEW.REFRESH Procedure 호출 Argument에 대한 확인이 필요합니다. COMPLETE 방식의 Refresh의 경우 데이터를 모두 삭제한 후 다시 쿼리한 데이터를 적재하는 방식으로 수행됩니다. 이때 데이터 삭제방법으로 10g 이하의 버전에서는 기본적으로 Delete가 아닌 Truncate 방식을 사용하면서 Redo 발생량을 억제하였습니다. 하지만 10g 버전에서부터 DBMS_MVIEW.REFRESH Procedure의 Argument 중 ATOMIC_REFRESH Argument의 Default값이 TRUE로 변경되면서 Default 값으로 Refresh를 수행할 경우, 데이터 삭제 Operation이 Delete 방식으로 수행되면서 Redo 발생량이 증가할 수 있습니다.

이에, COMPLETE 방식을 사용하는 MView에 대해서 Redo 발생과 관련된 문제를 경험한다면 ATOMIC_REFRESH Argument에 대한 확인이 필요합니다.

log file parallel write

---

log file parallel write 이벤트는 LGWR 프로세스에서만 발생하는 대기 이벤트입니다. LGWR 프로세스는 리두 버퍼의 내용을 리두 로그 파일에 기록하기 위해 필요한 I/O 콜을 수행한 후 I/O 작업이 완료되기를 기다리는 동안 log file parallel write 이벤트를 대기하게 됩니다.

만일 비동기식 I/O가 사용되는 환경에서 하나의 리두로그 그룹에 설정된 로그 멤버(member)가 두 개 이상이라면 병렬(parallel)로 기록합니다. 동기식 I/O가 사용된다면 각각의 로그 멤버에 대해서 순차적으로 기록합니다. 해당 이벤트는LGWR 프로세스의 정상적인 활동에서도 발생됩니다. 하지만 대기시간이 길다면 리두 로그파일이 위치한 디스크의 성능이 좋지 않거나 경합현상이 발생하고 있다고 볼 수 있습니다.

log file parallel write 대기는 I/O 관련 대기현상에서 다룬바 있는 db file parallel write 대기와 그 속성이 거의 유사합니다. 이 두 개의 대기는 근본적으로 I/O 시스템의 성능문제와 많은 관련이 있습니다. 여기에 성능문제를 바라보는 약간의 철학적 관점이 필요한데, 가령 이런 것입니다. I/O 시스템의 성능에는 문제가 없는데도 더티 버퍼의 양이 지나치게 많은 경우 db file parallel write 대기가 증가할 수 있습니다. 마찬가지로 I/O 시스템의 성능에는 아무런 이상징후가 없는데도 리두 데이터 양이 지나치게 많은 경우 log file parallel write 대기가 증가할 수 있습니다. 이 경우의 성능 문제는 지나치게 많은 데이터를 생성하는 애플리케이션의 문제인지, 아니면 더욱 빠른 속도로 데이터를 처리하지 못하는 I/O 시스템의 문제인지 알 수 없습니다. 이때, 일차적으로는 애플리케이션에서 문제와 해결책을 찾되, 더 이상의 해결책이 보이지 않을 때 I/O 시스템의 문제로 생각할 것을 권유합니다. 특히 경제적인 관점에서라도 이 방법을 따를 필요가 있습니다.

* Wait Time

모든 I/O 요청이 완료될 때까지 소요된 실질적인 시간을 나타낸다. 비동기식 I/O의 경우 병렬로 기록될 수 있지만, 마지막 I/O 수행이 완료될 때까지는 완료된 것이 아니기 때문이다.

* Parameter

P1(File Count(member count)), P2(Block count(redo log block count)), P3(I/O requests)

* Common Causes and Actions

 - 원인: Redo Log File이 위치한 File System의 I/O 성능에 문제가 있다.

 - 진단 방법: 쓰기 성능 관련 지표 확인(redo writes/redo blocks written/redo write time)

 - 개선 방법

: Redo Log Member를 Disk I/O 성능이 좋은 곳으로 옮긴다.

: RAID 5 구성 같은 경우 I/O 성능에 적합하지 않은 경우가 많다.

 - 원인: Redo 발생량이 많다.

 - 진단 방법: 발생량 관련 지표 확인 (redo size / redo entries)

 - 개선 방법

: Tablespace가 Hot Backup 상태가 아닌지 확인한다.

: Redo 발생량을 줄이기 위한 NOLOGGING / UNRECOVERABLE 옵션을 고려한다.

Redo

REDO는 오라클 SGA에 있는 Redo Buffer와 Redo Log File로 이루어져 있는데 이는 Database의 복구를 목적으로 하고 있습니다. 이 REDO는 Database에 적용된 모든 변경사항에 대한 이력을 저장합니다. 여기에 포함되는 정보는 DML, DDL, Recursive SQL에 의해 변경된 모든 Data의 이력들이며 DDL의 경우는 SQL Text도 저장됩니다. 그러나 NOLOGGIG Option을 부여한 상태에서의 Data 변경 이력과 DML SQL Text는 저장되지 않습니다.

Redo Log File은 Redo Buffer의 내용을 그대로 저장하게 되는데 이 작업은 LGWR 프로세스에 의해 이루어집니다. Redo Buffer의 내용이 Redo Log File에 기록하는 시점은 보통 4가지 경우입니다.

첫번째는 매 3초마다 자동적으로 기록되고, 두번째는 _LOG_IO_SIZE의 설정 값에 이르렀을 때입니다. 이 히든 파라미터는 Redo Buffer의 사이즈에 의해 자동으로 설정되는데 보통 LOG_BUFFER의 1/3 또는 1MB로 설정됩니다. 세번째는 사용자의 Commit 또는 Rollback이라는 명시적인 명령에 의해 강제적으로 기록되는 경우인데 이를 Log Force at Commit으로 부르는 경우도 있습니다. 이 경우 해당 Transaction과 관계된 모든 Redo 정보를 Log File에 저장하고 나서 Commit을 완료하게 됩니다. 마지막으로 DBWR 프로세스에 의해 신호를 받았을 경우입니다. 이 경우 Write Ahead Log라고도 하는데 DBWR 프로세스가 Data Buffer Block을 Disk로 기록하기 전에 Redo Buffer Log를 먼저 기록하기 때문에 발생하는 경우입니다. 

Redo의 쓰기 성능

select 	round((a.value/b.value) + 0.5, 0) as avg_redo_blks_per_write,
	round((a.value/b.value) + 0.5, 0) * c.lebsz as avg_io_size
from 	v$sysstat a,
	v$sysstat b,
	x$kccle c
where	c.lenum = 1
and	a.name = 'redo blocks written'
and	b.name = 'redo writes';

Redo의 쓰기 성능을 판별하기 위한 지표로는 Redo Writes와 Redo Blocks Written이 존재합니다. 위 스크립트는 두 Statistics의 지표와 Redo Block 단위(대부분 512B)를 이용하여 1회 쓰기에 몇 블록을 처리하였는지 확인할 수 있으며, 이를 이용하여 Redo File이 존재하는 디스크의 쓰기 성능을 확인할 수 있습니다.

log file switch completion

---

서버 프로세스가 리두 버퍼에 리두 레코드를 기록하려는 시점에 리두 로그 파일이 꽉 차서 더이상 쓰기를 할 수 없으면 프로세스는 LGWR 프로세스에게 로그 파일 스위치를 수행할 것을 요청합니다. 서버 프로세는 LGWR 프로세스에 의해 로그 파일 스위치가 끝날때까지 log file switch completion 이벤트를 대기하게 됩니다.

해당 대기 이벤트의 발생 원인은 트랜잭션이 생성하는 리두 데이터의 양에 비해 리두 로그 파일의 크기가 지나치게 작다는 것입니다. 따라서 해결책은 리두 로그 파일의 크기를 충분히 키워주는 것입니다. 더불어 Direct Load Operation이나 Nologging 옵션을 사용하여 리두 데이터의 양을 줄이는 것도 도움이 됩니다.

* Wait Time

1초

* Parameter

log file switch completion 대기 이벤트는 대기 파라미터를 사용하지 않는다.

* Common Causes and Actions

 - 원인: 생성되는 리두의 양에 비해 로그버퍼의 크기가 작은 경우

 - 진단 방법: Redo Size 지표 모니터링 및 LOG_BUFFER 파라미터 Size 확인

 - 개선 방법: 로그 버퍼의 크기를 충분히 크게 한다.

log file switch completion 관련 event

로그 파일 스위치가 끝나느 시점에, 새로 사용할 리두 로그 파일에 대해 아직 종료되지 않은 작업이 이싿면, 아래와 같이 추가적으로 이벤트를 대기해야 합니다. 새로 사용할 리두 로그 파일에 대한 체크 포인트 작업이 아직 끝나지 않았다면 프로세스는 DBWR 프로세스에 의해 체크 포인트가 끝나기를 기다려야 합니다. 이 경우 프로세스는 log file switch(checkpoint incomplete) 이벤트를 대기하게 됩니다.

새로 사용할 리두 로그 파일에 대해 아카이브(Archive) 작업이 아직 끝나지 않았다면 프로세스는 ARCH 프로세스에 의해 아카이브 작업이 끝나기를 기다려야 합니다. 이 경우 프로세스는 log file switch(archiving needed) 이벤트를 대기하게 됩니다. 이 이벤트는 아카이브 모드의 데이터베이스에서만 발생합니다.

새로 사용할 리두 로그 파일에 대해 Private Strands에 대한 플러쉬(flush) 작업이 아직 끝나지 않았다면 이 작업이 끝나기를 기다려야 합니다. 이 경우 프로세스는 log file switch(private strand flush incomplete) 이벤트를 대기하게 됩니다. 이 이벤트는 오라클 10g R2에서 추가된 것이며, Private redo strands 기능을 사용할 경우에만 발생합니다.

위의 3가지 대기현상은 리두 로그 파일이 순환적으로(Circular) 사용되는 상황에서, 아주 많은 리두 데이터가 생성되기 때문에 이전 작업을 다 끝내기도 전에 다시 재사용할 경우에 발생하게 됩니다. 따라서 이들은 log file switch completion 대기현상과 항상 같이 나타납니다. 정확하게 말하면, 서버 프로세스는 먼저 log file switch completion 이벤트를 대기하고 특수한 경우 다시 log file switch(checkpoint incomplete)나 log file switch(archiving needed), log file switch(private strand flush incomplete) 이벤트를 대기하게 됩니다.

log file sync

---

사용자가 Commit 또는 Rollback 명령을 요청하면 Server Process는 LGWR 프로세스에게 요청을 전달합니다. LGWR 프로세스는 Redo Buffer에서 가장 마지막에 기록이 이루어진 이후 시점부터 Commit 지점까지의 모든 Redo Entry를 Redo Log File에 기록합니다. 이것을 "sync write"라고 부르며 redo sync writes 통계값을 통해 조회 가능합니다. Server process는 Commit 명령을 내린 후 LGWR 프로세스가 성공적으로 기록할 때까지 기다리게 되는데, 이때 log file sync 이벤트를 대기하게 됩니다.

다시 말해 로그 동기화(log synchronization)를 수행하는 동안, LGWR 프로세스는 "sync write"가 완료할 때까지 log file parallel write 대기 이벤트를 대기하게 되고, Server Process는 log file sync 대기 이벤트를 대기하는 것입니다.

Server Process는 2가지 이유로 인해 log file sync 대기 이벤트를 겪게 됩니다. 하나는 로그 동기화가 완료되기를 대기하는 것입니다. 다른 하나는 대기가 타임아웃(일반적으로 1초)되는 경우입니다. 타임아웃이 발생하면 Server Process는 Commit 정보가 디스크에 기록되었는지를 확인하기 위해 현재 로그 SCN(System Change Number)을 확인합니다. 만일 Commit 정보가 디스크에 기록되었다면 대기를 그만두고 작업을 진행하며, 그렇지 않을 경우 계속 log file sync 대기 이벤트를 지속하게 됩니다.

만일 세션이 동일한 buffer#를 대기한다면 V$SESSION_WAIT의 SEQ# 칼럼의 값은 매초마다 증가합니다. SEQ# 컬럼의 값이 증가할 경우의 블로킹 프로세스는 LGWR입니다. 이러한 경우 LGWR 프로세스가 다른 이유로 인해 대기 중인지를 확인해야 합니다.

일반적으로, log file sync는 매우 평범한 대기 이벤트입니다. 사용자가 인식하기 힘든만큼 매우 단시간에 발생합니다. 하지만, log file sync 대기 이벤트의 과다 현상은 응답시간을 상당히 지연시키게 되며, V$SYSTEM_EVENT 및 V$SESSION_EVENT 뷰에서 눈에 띌만한 대기통계정보를 나타내게 됩니다.

* Wait time

  1초

* Parameter

P1(buffer#), P2(sync scn)

* Common Causes and Actions 

 - 원인: 과다한 Commit 횟수

 - 진단방법: User Commit 지표 확인 및 소스 내 Commit 수행 위치 확인

 - 개선 방법: 만일 세션이 배치 프로세스라면, 루프 내에서 반복적으로 Commit을 수행할 수 있습니다. 이 경우 모듈의 이름을 확인한 후 Commit의 횟수를 줄일 수 있는지를 점검하기 위해서 개발자에게 코드를 점검합니다.

 - 원인: I/O 시스템의 성능 저하

 - 진단 방법: LGWR 프로세스가 log file parallel write이벤트를 대기하는 평균시간이 높거나 전체 시스템에서 대기시간 대비 차지하는 비중이 높다면 Redo Log File이 위치한 I/O 시스템의 성능에 문제가 있다고 파악

 - 개선 방법: Redo Log File을 가장 빠른 디바이스에 위치시킵니다. 그리고 디스크 경합을 피ㅏ하기 위해 서로 다른 그룹의 Redo Log File은 서로 다른 디스크에 분산시켜야 합니다. Redo Log File을 데이터 파일이나 컨트롤 파일과 다른 디스크에 배치시키는 것 또한 필요합니다.

 - 원인: 불필요한 Redo 데이터 생성

 - 진단 방법: Redo Size 지표 모니터링 및 LOG_BUFFER 파라미터 Size 확인

 - 개선 방법

: Direct load 기능과 Create ..., Alter .. 류의 작업들은 대부분 Nologging 옵션을 제공합니다. 이런 기능들을 잘 활용하면 Redo 데이터의 양을 극적으로 줄일 수 있습니다. 

: SQL*Loader로 대량의 데이터를 적재시 Direct load option을 사용

: 임시 작업이 필요할 때는 임시 테이블(Temporary Table)을 사용
: 인덱스가 있는 테이블에 대해 Direct load 작업을 수행시 인덱스를 Unusable 상태로 변경하여 작업

: LOB 데이터의 경우 데이터의 크기가 크다면 가급적 Nologging 속성을 부여

 - 원인: Redo Buffer가 지나치게 크게 설정된 경우

 - 진단 방법: Redo Size 지표 모니터링 및 LOG_BUFFER 파라미터 Size 확인

 - 개선 방법

: Redo Buffer는 background writes 횟수를 감소시키고, LGWR 프로세스의 활동성을 낮추게 되므로 Redo Buffer에는 많은 양의 Redo Entry가 저장되는 현상을 야기시킵니다. 프로세스가 Commit을 수행할 때, LGWR 프로세스는 더 많은 양의 Redo Entry를 디스크에 기록해야 하므로, sync write 시간은 더 오래 소요되고 서버 프로세스는 log file sync 대기 이벤트를 경험하게 되므로 로그 버퍼 사이즈를 작게 변경합니다.

Sequence와 log file sync

Sequence를 생성할 때 NOCACHE 옵션을 주는 경우가 종종 있습니다. NOCACHE 속성의 시퀀스는 많은 성능 문제를 야기하는데 대표적인 경우가 row cache lock 대기를 증가시키는 것입니다. 재미있게도, NOCACHE 속성의 Sequence는 log file sync 대기를 유발하는 원인이 될수도 있습니다. NOCACHE 속성의 Sequence에 대해서 Sequence.nextval을 호출하면 매번 Dictionary 테이블의 정보를 갱신하고 commit을 수행해야 하기 때문입니다. Sequence를 사용할 때는 Transaction의 양을 고려하여 반드시 적절한 크기의 CACHE 속성을 부여해야 합니다.

PROCESS Parameter와 log file sync

PROCESSES 파라미터 수치를 크게 설정할 경우 log file sync 대기현상이 증가하게 됩니다. 모든 동기화 작업(sync operation)동안, LGWR 프로세스는 어떠한 세션들이 해당 이벤트를 대기하고 있고 어떠한 세션의 Redo를 디스크로 기록해야 하는지 확인하기 위해서 프로세스의 데이터 스트럭처를 스캔해야 합니다. PROCESSES 파라이커 수치를 작게 설정할 경우 log file sync 대기현상을 감소시키는데 도움이 됩니다. 적정한 값을 알기 위해서는 V$RESOURCE_LIMIT 뷰를 사용하면 도비니다.

출처: https://12bme.tistory.com/320?category=749950 [길은 가면, 뒤에 있다.]