Delayed block cleanout 발생과 해결법

블록 클린아웃은 트랜잭션에 의해 설정된 로우 락을 해제하고 블록 헤더에 커밋정보를 기록하는 오퍼레이션입니다.

사용자가 트랜잭션을 커밋했을 때 블록 클린아웃까지 완료해야 완전한 커밋이라고 할 수 있습니다.

DB의 안정적인 사용을 위해서 대량의 갱신작업이 있고나서는 커밋정보를 트랜잭션 테이블에만 기록하고 빠르게 커밋을 끝내버립니다. 블록헤더에는 아직 커밋정보가 기록되지 않은 상태입니다. 해당 블록은 나중에 처음 액세스되는 시점에 다시 커밋정보를 기록하고 클린아웃이 됩니다.

무조건적으로 이 방식으로 작동하는것은 아니고, 일반적으로 변경량에 따라 정해지게 됩니다.

 

종종 업무중에 배치작업이 늦어서 확인을 요청하는 경우가 있습니다. DB에서는 배치정보를 가지고 현재 DB에서 수행중인 상태를 조회해보면 특정부분에서 처리되지 않고(대부분, Table scan이나 index scan라인) Undo영역을 읽으며 더디게 수행되는 모습을 보입니다.

 

해당 현상에 대해 좀더 자세히 알고 정리하고자 합니다.

 

먼저 나오는 용어에 대한 정리를 하려고 합니다.

 

 

오라클의 data blcok은 아래의 구조를 가지고 있습니다.

 

+-------------------------------------------------------+
| Block Header (블록 헤더)                              |
|  - Block Address (블록 주소)                          |
|  - Segment Type (세그먼트 타입)                       |
|  - ITL (Interested Transaction List, 트랜잭션 목록)   |
|  - Commit SCN (System Commit Number, 커밋 SCN)        |
|  - Checksum 등 기타 메타데이터                        |
+-------------------------------------------------------+
| Table Directory (테이블 디렉터리)                     |
|  - 이 블록에 저장된 테이블/클러스터 정보             |
+-------------------------------------------------------+
| Row Directory (행 디렉터리)                           |
|  - 각 Row가 블록 내 어디에 있는지 가리키는 포인터    |
|  - Rowid의 \"row 번호\"가 여기 슬롯을 가리킴          |
+-------------------------------------------------------+
|                       Free Space                      |
|   (Row Data가 아래에서 위로 자라 올라오는 여유 공간) |
+-------------------------------------------------------+
| Row Data (행 데이터 영역, 아래에서 위로 저장)         |
|  - Row 1 (Row Piece 1)                                |
|  - Row 2                                              |
|  - ...                                                |
|  - Chained / Migrated Row Piece들                     |
+-------------------------------------------------------+

출처 : https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/logical-storage-structures.html

 

이 중 블록헤더에 있는ITL(Interested Transaction List)의 커밋정보의 업데이트가 언제 이루어지냐에 따라 fast / delayed로 나누어집니다. 트랜잭션이 갱신한 블록개수가 버퍼캐시블록의 1/10을 초과하지 않을 시에는 커밋시에 곧바로 블록 클린아웃(fast)를 시도합니다.

 

이는 해당사항이 없고 delayed block cleanout의  테이블의 경우(트랜잭션이 갱신한 블록개수가 총 버퍼 캐시 블록개수의 1/10을 초과할 때) 다른곳에서 select할 때 아래의 과정을 거치게 됩니다.

 

즉, 제대로 block cleanout이 되었다면, undo를 거칠일 없이 데이터블록을 그래도 읽으면 됩니다. 

하지만 delayed block cleanout 상태에서 블록을 읽으려 접근하면 아직 사용중이라고 되어있기 때문에 undo로 찾아가 이전의 데이터를 찾게 되는것입니다.

 

해결법?

 

1) Full Table Scan(Conventional Path). direct path read는 안됨.

데이터블록을 읽어 메모리에 올리기 위해 Full Table Scan을 conventional path로 읽어야 합니다.

direct path read는 메모리를 경유하지 않으므로 효과가 없습니다.

11g 이후에는 큰테이블을 읽을경우에는 direct path read를 사용하게 파라미터 설정이 되어 있게 됩니다.

그리고 세션은 상관없이 테이블을 조회하는것이 중요하므로 굳이 DML이 발생한 세션에서 수행하지 않아도 됩니다.

 

관련내용은 아래를 참고하세요.

https://bae9086.tistory.com/418

Full Table Scan 시 Direct Path Read ?

 

 사용법

 alter session set "_serial_direct_read"=never;
 
 select /*+ FULL(A) */ count(*) from T A;

 

 

2) 통계정보 재갱신

통계정보를 갱신하게되면 건수확인 및 데이터 분포를 확인하기 위해서 내부적으로 Full Table Scan을 수행하게 됩니다.

 

사용

exec DBMS_STATS.GATHER_TABLE_STATS('YSBAE','EMP');

 

 

두개방법에 대한 정리는 아래 표와 같습니다.

 

왜 블록을 메모리에 올리는것이 중요할까요?

블록의 내용을 수정하려면 메모리에 올려야 하기 때문입니다. 디스크는 단순 저장소로써 수정하는 기능은 없습니다.

메모리를 dbwr 프로세스가 디스크로 쓰는 것이죠. 

1.SELECT를 위해 블록을 버퍼캐시에 올리고

2.만약 블록헤더가 delayed block cleanout이 필요한상태인것을 발견한다면

3.Undo 세그먼트에서 해당 트랜잭션이 커밋되었는지 확인하고

4.커밋이 확인되면 버퍼캐시에 있는 블록헤더를 수정하게됩니다.

그럼 블록은 dirty block상태로 변하게 되고 dbwr이 디스크에 내려쓸때 block이 cleanout되는 것입니다.

 

모니터링방법

delayed block cleanout이 발생한다면 저의 경험적인 방법과 새로 알게된 방법 두가지를 소개하려고 합니다.

 

1. v$session_wait에서 event와 p1의 값 확인하여 어떤 datafile을 조회하는지 확인

해당 현상이 발생하면 db file sequential read 등 read event가 발생하고 p1의 file_id를 조회합니다.

UNDO를 읽으면 delayd block cleanout이 발생했다고 판단합니다.

 

2.v$sesstat의 value를 확인합니다.

위의 값 중 transaction tables consistent reads - undo reads applied 값이 비정상적으로 높아지면 판단할 수 있습니다.

 

'transaction tables consistent reads - undo reads applied : 트랜잭션의 종류여부(커밋여부)를 확인하기 위해 undo 세그먼트에 있는 transaction table 정보를 과거로 돌린 횟수
,'cleanout - number of ktugct calls' : 블록헤더의 ITL을 확인했는데 트랜잭션 상태를 몰라 UNDO 세그먼트까지 가서 확인한 횟수
,'commit cleanouts' : cleanout이 필요할 때 해당 블록이 버퍼캐시에 있어 즉시 처리된횟수
,'commit cleanouts successfully completed' : cleanout이 성공적으로 완료된 횟수

select
from v$sesstat a,v$statname b
where a.statistic# = b.statistic#
and a.sid = 992
and b.name in (
'transaction tables consistent reads - undo reads applied'
,'cleanout - number of ktugct calls'
,'commit cleanouts'
,'commit cleanouts successfully completed'
)