8. 블록 클린 아웃
블록 클린 아웃이 뭐죠?
블록 클린아웃은 트랜잭션에 의해 설정된 로우 Lock을 해제하고 블록 헤더에 커밋 정보를 기록하는 오퍼레이션이다. 오라클에서 로우 단위 Lock은 레코드의 속성(Lock Byte)으로 관리되며, 로우 헤더로부터 블록 헤더에 있는 ITL 엔트리를 가르키는 포인터이다.
그렇다면 오라클이 블록을 클린 아웃하는 시점은 언제일까?
오라클은 대량의 갱신 작업이 있고 나서는 커밋 정보를 트랜잭션 테이블에만 기록하고 빠르게 커밋을 끝내 버린다. 나중에 해당 블록이 처음 액세스되는 시점이다. 오라클은 Delayed 블록 클린아웃과 커밋 클린아웃 두 가지 매커니즘을 사용한다.
1) Delayed 블록 클린아웃
- 트랜잭션이 갱신한 블록 개수가 총 버퍼 캐시 블록 개수의 1/10을 초과할 때 사용
- 커밋 이후 해당 블록을 애세스하는 첫 번째 쿼리에 의해 클린아웃이 이루어짐
- 아래 작업 수행
- ITL 슬롯에 커밋 정보 저장 -> Active 상태의 블록만
- 레코드에 기록된 Lock Byte 해제
- Online Redo에 Logging, 블록 SCN도 변경
2) 커밋 클린아웃 = Fast 블록 클린아웃
- 트랜잭션이 갱신한 블록 개수가 버퍼 캐시 블록 개수의 1/10을 초과하지 않을 때는 커밋 시점에 곧바로 블록 클린아웃을 수행한다.
- 커밋 시점에 ITL 슬롯에 커밋 정보만 저장하고 로우 헤더에 기록된 Lock Byte는 해제하지 않음
커밋 클린아웃에서는 왜 Lock Byte를 해제하지 않을까?
=> 로깅을 수행하지 않기 위해서
즉, 커밋 클린아웃 시에는 Online Redo에 로그를 남기지 않고, 로깅 시점을 뒤로 미룬다
그리고 해당 블록을 갱신하려고 Current모드로 읽는 시점에 Lock Byte를 해제하고 완전한 클린아웃을 수행하고 그 내역을 Online Redo 로깅한다. 블록 클린 아웃정보를 나중에 로깅한다는 의미에서 'Delayed 로킹 블록 클린아웃'이라고도 부름
9. Snapshot too old
Snapshot too old의 발생원인은?
- 데이터를 읽어 내려나가다가 쿼리 SCN 이후에 변경된 블록을 만나 과거 시점으로 롤백한 'Read Consistent' 이미지를 얻으려고 하는데, Undo 블록이 다른 트랜잭션에 의해 이미 재사용돼 필요한 Undo 정보를 얻을 수 없는 경우
- 커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인할 수 없는 경우
Snapshot too old 회피 방법은?
사실상 이 에러는 재현이 어려워 문제의 발생 원인을 찾는 것부터 쉽지 않고, 잘 알려진 조치방법들도 확률을 낮출뿐이며 그 가능성을 0%로 만드는 것이 아니다. Undo 메커니즘을 도입한 배경으로 거슬러 올라가면 결국 Lock에 의한 동시성 저하를 방지하려는 것인데, 동시성을 높인 대신 얻은 부작용정도로 이해해야 할 것이다.
과거 버전에는 Undo 세그먼트를 수동으로 관리했기 때문에 Snapshot too old 에러 발생을 최소화하기 위해 Undo 튜닝을 DBA가 직접 관리했다. 9i부터는 AUM이 도입돼 오라클이 트랜잭션 발생 상황에 따라 Undo 세그먼트 크기와 개수를 동적으로 자동 조절해 주기 때문에 DBA의 역할이 줄었다. 하지만 AUM 하에서 충분한 크기의 Undo Space를 할당하는 것 만으로 Snapshot too old 에러가 완전히 해결된 것은 아니다.
그 가능성을 줄이기 위한 몇 가지 솔루션들
1. 불필요하게 커밋을 자주 수행하지 않는다
2. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다
-> ANSI 표준에 따르면 커밋 이전에 열로 있던 커서는 더는 Fetch 하면 안 된다.
3. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 시간을 조정한다.
4. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다.
-> Snapshot too old 발생 가능성을 줄일 뿐 아니라 문제가 발생했을 때 특정 부분부터 다시 시작할 수도 있어 유리하다. 물론 그렇게 해도 읽기 일관성에 문제가 없을때에만 적용한다.
5. 오랜 시간에 걸쳐 블록을 여러 번 방문하는 NL조인문이나 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고, 이를 회피한 조인 메소드 변경, FULL TABLE SCAN 방법들을 사용한다.
6. 소트 부하를 감수하더라도 order by등을 강제로 삽입해 소트연산이 발생하도록 한다.
-> 많은 데이터를 오랜 시간에 걸쳐 Fetch 하는 동안 Undo 정보를 지속적으로 참조하기 때문에 문제가 발생하는 것으로, 서버 내에서 빠르게 데이터를 읽어 Temp 세그먼트에 저장하는 데에만 성공하면 이후에는 같은 블록을 아무리 재방문하더라도 에러가 발생하지 않는다.
7. 만약 Delayed 블록 클린아웃에 의해 Snapshot too old가 발생하는 것으로 의심되면 대량 업데이트 후에 곧바로 해당 테이블에 대해 Full Scan 하도록 쿼리를 날리는 것도 방법이다.(옵티마이저 힌트 사용)
SELECT /*+ FULL(t) */ COUNT(*)
FROM TABLE_NAME T;만약 인덱스 블록에서 문제가 발생한다고 판단되면 인덱스 리프 블록을 모두 스캔하도록 쿼리한다. 예를 들어, index_column이 숫자형 컬럼이고 0보다 큰 값을 갖는다면 대량 업데이트 후에 아래와 같은 쿼리를 날린다.
SELECT COUNT(*)
FROM TABLE_NAME T
WHERE index_column > 0;