09. snapshot too old
<< 발생 원인 >>
1) 데이터를 읽어 내려가다가 쿼리 SCN 이후에 변경된 블록을 만나 과거 시점으로 rollback 한 'Read consistent' 이미지를 얻으려고 하는데,
Undo block이 다른 트랜잭션에 의해 이미 재사용돼 필요한 Undo 정보를 얻을 수 없는 경우,
프로그램 코딩 패턴에 문제가 없다면 Undo 세그먼트가 너무 작다는 신호일 수도...
2) 커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인할 수 없는 경우
Undo 세그먼트 개수가 적다는 신호일 수도...
(1) Undo 실패
Undo block을 찾을 수 없어 에러가 발생하는 경우
Select /*+ ordered use_nl(b) */ A.고객ID
, NVL(SUM(과금액), 0) 과금액
, NVL(SUM(과금액), 0) 수납액
, NVL(SUM(과금액), 0) - NVL(SUM(수납액), 0) 미납액
From 과금 A, 수납 B
Where A.과금년월 = :과금년월
And A.과금유형 = :과금유형
And A.고객ID = B.고객ID(+)
And A.과금년월 = B.수납년월(+)
Group by A.고객ID
a. SCN123 시점에 쿼리 시작
b. 쿼리 수행중 은행으로부터 고객 입금 내역을 전송받아 일괄처리하는 배치 수행(각 건별로 commit)
홍길동의 수납액을 10000 -> 20000원으로 변경 후 commit 수행 => Block SCN은 129로 변경됨
c. 홍길동의 수납액 변경내역(10000 -> 20000)을 담은 Undo block이 다른 트랜잭션에 의해 재사용
d. SCN 123 시점에 시작된 쿼리가 홍길동의 수납액이 담긴 block에 도달했을 때,
block SCN이 129임을 확인하고, 변경된 Undo record를 찾으려고 ITL 엔트리에 기록된 UBA를 읽어 Undo segment를 탐색
e. 찾으려는 Undo block(SCN123 시점 이전)은 이미 다른 트랜잭션에 의해 재사용된 상태라,
ORA-01555 error 발생시키며, 트랜잭션 중지됨
# /*+ ordered use_nl(b) */ 힌트
대용량의 테이블을 outer table(과금 테이블)로 하여 Nested loop join을 수행하면 쿼리 수행 시간도 오래 걸리고
inner table(수납 테이블)에 대해 같은 block을 반복해서 재방문할 가능성이 커짐
한 세션에서 독립적으로 아래의 프로시저를 수행하면 snapshot too old 에러 발생가능함.
for C in (Select /*+ ordered use_nl(b) */ A.고객ID
, NVL(SUM(과금액), 0) 과금액
, NVL(SUM(과금액), 0) 수납액
From 과금 A, 수납 B
Where A.입금일자 = trunc(sysdate)
And B.고객ID(+) = A.고객
And B.수납년월(+) = to_char(sysdate, 'yyyymm') )
loop
if C.수납액 IS NULL then
insert into 수납(고객ID, 수납년월, 수납액)
values(C.고객ID, to_char(sysdate, 'yyyymm'), C.입금액);
else
update 수납 set 수납액 = 수납액 + C.입금액
where 고객ID = C.고객ID
and 수납년월 = to_char(sysdate, 'yyyymm');
end if;
commit;
end loop;
a. SCN 100 시점에 cursor C 오픈
b. 수납 테이블에 대한 update 문에 의해 홍길동 수납액이 변경되고 commit 됨.
이때 500번 block SCN이 120으로 변경. loop문 내에서 commit 하기 때문에 insert와 update는 별개 트랜잭션
c. 트랜잭션이 반복되다가 홍길동의 수납액 before image가 담긴 Undo block이 다른 트랜잭션에 의해 재사용됨.
d. cursor C가 Fetch 하다가 홍길동 수납정보와 같은 500번 block에 저장된 김철수 수납정보에 도달함.
Nested loop join시 같은 block을 여러번 방문할 수 있음. 500번 block의 SCN이 120임을 확인하고,
쿼리 시작 시점(SCN100)으로 rollback하려고 undo block 탐색
e. Undo block은 이미 c 항목에서 재사용된 상태이므로 ORA-01555 에러 발생시키며 트랜잭션 중지
# fetch across commit
명시적으로 cursor를 open하여 row를 하나씩 Fetch하면서 값을 변경하고 루프 내에서 계속해서 커밋을 날리는 방식
ANSI 표준은 open된 cursor는 commit 시점에 무효화되어야 하므로 사용자는 계속해서 Fetch하면 안됨
(2) 블록 클린아웃 실패 (트랜잭션 테이블 슬롯이 재사용되는 경우)
대량 업데이트 후에 커밋된 트랜잭션은 변경했던 블록들을 모두 클린하지 않은 상태에서
자신이 사용하던 트랜잭션 테이블 슬롯을 Free 상태로 변경하고, 트랜잭션을 완료함.
Free 상태로 변경된 트랜잭션 테이블 슬롯은 다른 트랜잭션에 의해 재사용될 수 있음.
변경된 블록들이 읽혀야 하는 시점에 Delayed block cleanout을 위해 트랜잭션 테이블 슬롯을 찾아갔는데,
해당 슬롯이 다른 트랜잭션에 의해 이미 재사용되고 없다면 정상적인 block cleanout과 일관성 모드(consistent mode)읽기가 불가능해짐
오라클은 일반 데이터 블록과 마찬가지로 undo 세그먼트 헤더블록을 갱신한 내용도 undo record로서 기록함.
그래서 트랜잭션 테이블 슬롯이 덮어 쓰인 것을 발견하면 우선 undo 세그먼트 헤더 블록에 가해진 변경사항을 rollback 시도함.
다행히 찾고자 하는 트랜잭션에 대한 커밋 정보가 undo block에 남아있다면 현재 읽고자 하는 블록의 정확한 커밋 SCN을 가지고
블록 클린 아웃을 수행할 수 있음.
undo record를 뒤졌는데 그마저도 덮어쓰이고 없는 상태라면?
# snapshot too old 에러가 거의 발생하지 않는 이유
트랜잭션 슬롯이 필요해지면 커밋 SCN이 가장 낮은 트랜잭션 슬롯부터 재사용함.
그 슬롯에 기록돼 있던 커밋 SCN을 Undo 세그먼트 헤더에 '최저 커밋 SCN(low commit SCN)'으로서 기록해둠
트랜잭션 슬롯이 재사용되고 나면 그 슬롯을 사용하던 이전 트랜잭션의 정확한 커밋 SCN을 확인하는 것이 불가능해 지지만
Undo 세그먼트 헤더에 기록된 '최저 커밋 SCN'을 참조하여 블록 ITL 엔트리에 커밋 SCN으로 기록함으로써
block cleanout을 마무리하고, block SCN도 변경함.
쿼리가 진행되는 동안에 많은 트랜잭션이 한꺼번에 몰리지만 않는다면 '최저 커밋 SCN'이 갑자기 많이 증가하지는 않을 것이므로,
'최저 커밋 SCN'에 의해 추정된 블록 SCN은 대개 쿼리 SCN보다 작음.
따라서 쿼리가 시작된 이후에 해당 블록에 변경이 가해지지 않았음이 확인되므로, 정상적인 일관성 모드 읽기도 가능함.
# 'Snapshot too old' 에러 메세지가 발생하는 근본 원인
'최저 커밋 SCN'이 쿼리 SCN보다 높아질 정도로 갑자기 트랜잭션이 몰리는데 있음.
실제로 이 에러를 발생시킨 블록은 훨씬 오래전 시점에 커밋된 것일 수 있지만, 이를 확인할 방법이 없으며
그동안 한번도 읽히지 않다가 불행하게도 트랜잭션이 몰리는 시점에 읽히다 보니 문제를 유발하게 됨.
(3) snpshot too old 회피 방법
재현이 어려움
9i부터는 AUM(Automatic Undo management)이 도입됨
=> DBA는 Undo space가 충분한 크기인지만 고려하면 됨.
# application 측면에서의 고려 사항
a. 불필요하게 commit을 자주 수행하지 않음
b. fetch across commit 형태의 프로그램 작성을 다른 방식으로 구현.
다른 방식으로 구현이 어렵다면 commit 횟수라도 줄여봄
c. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 시간 조정 필요
d. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩
그렇게 해도 읽기 일관성에 문제가 없을 때에만 적용해야 함.
e. 오랜 시간에 걸쳐 같은 블록을 여러 번 방문하는 Nested loop 형태의 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크
조인 방식 변경 및 Full Table Scan으로 변경
f. sort 부하를 감수하더라도 order by 등을 강제로 삽입해 sort 연산이 발생하도록 한다.
많은 데이터를 오랜 시간에 걸쳐 Fetch하는 동안 Undo 정보를 지속적으로 참조하기 때문에 문제가 발생하는 것이므로,
서버 내에서 빠르게 데이터를 읽어 Temp 세크먼트에 저장하는 데에만 성공하면 이후에는 같은 블록을 아무리 재방문하더라도 더는
에러가 발생할 까 걱정하지 않아도 됨.
g. delayed block cleanout에 의해 Snapshot too old가 발생하는 것으로 의심되면 대량 update 후에 곧바로 해당 테이블에 대해
Full scan하도록 쿼리 수행
select /*+ full(t) */ count(*) from table_name t
만약 인덱스 블록에 문제가 발생한다고 판단되면 인덱스 리프 블록을 모두 스캔하도록 쿼리 수행
select count(*) from table_name where index_column > 0 (index_column이 숫자형 컬럼, 0보다 큰값을 갖을 경우)
10. 대기 이벤트
오라클내부에서 여러 프로세스들이 작동하면서 다른 프로세스로 인해 대기해야만 하는 상황이 발생할 때
이것을 대기 이벤트라고 함.
대기 이벤트 상태 정보를 파일 또는 SGA 메모리내에 저장해 둠
원래는 오라클 개발자들이 공유자원에 대한 경합이나 기타 원인에 의한 대기가 발생할 때마다
관련 로그를 생성하도록 커널 코드에 디버깅 용도로 추가해논 건데,
성능 관리 분야에서 OWI(Oracle Wait Interface)가 각광받게 되면서
대기 이벤트 항목들이 오라클 버전이 올라갈 수록 점점 증하가 됨
r b swpd free buff cache si so bi bo in cs us sy id wa
229 1 32764556 29532 1640 1266324 0 0 1220 0 1064 129357 1 99 0 0
578 2 32764556 29148 1648 1266724 0 0 1692 84 1579 183986 2 98 0 0
273 11 32764556 28444 1712 1267340 0 0 7216 0 1196 129370 1 99 0 0
240 1 32764556 28252 1672 1267652 0 0 2972 44 1513 158365 2 98 0 0
325 1 32764556 28892 1688 1266820 0 0 404 100 1167 135384 1 99 0 0
291 1 32764556 29468 1736 1266432 0 0 1620 40 1047 129388 2 98 0 0
259 1 32764556 29468 1732 1266372 0 4 1844 28 1334 148401 2 98 0 0
305 6 32764556 32156 1700 1263724 32 4 4920 40 1462 172506 2 98 0 0
333 4 32764556 28700 1756 1266536 64 8 20868 272 8960 1042298 2 98 0 0
355 9 32764556 30044 1748 1264980 0 0 624 16 1038 132929 1 99 0 0
279 4 32764556 28892 1900 1265876 0 0 6176 308 1744 211769 1 99 0 0
251 9 32764556 29596 1892 1265204 0 0 1864 32 1059 139595 1 99 0 0
373 8 32764556 28700 1936 1266372 0 0 5000 16 1327 160782 1 99 0 0
303 4 32764556 28316 1912 1266804 0 0 836 0 1032 118053 2 98 0 0
316 7 32764556 28828 1884 1266084 0 0 700 32 1133 137452 1 99 0 0
292 3 32764556 28508 1804 1266776 0 0 5828 68 1309 153603 2 98 0 0
327 5 32764556 29148 1772 1266060 0 0 1404 0 1071 135502 1 99 0 0
269 12 32764556 28124 1784 1266796 0 0 6936 52 1699 193483 2 98 0 0
(2) 대기 이벤트는 언제 발생할까?
SQL *Net Message from client
SQL *Net more data from client
서버 프로세스가 사용자의 명령이나 신호를 기다릴 때 나타나므로 무시해도 됨
기타 idle 대기 이벤트도 무시해도 됨
# 대기 이벤트가 발생하게 되는 상황
1. 자신이 필요로 하는 특정 리소스가 다른 프로세스에 의해 사용중일 때
buffer busy wait, latch free, enqueue 관련 대기 이벤트
2. 다른 프로세스에 의해 선행 작업이 완료되기를 기다릴 때
with complete waits, checkpoint completed, log file sync, log file switch 이벤트
3. 할 일이 없을 때 ( => idle 대기 이벤트)
SQL *Net message from client
PX Deq: Execution Msg
(3) 대기 이벤트는 언제 사라질까?
수면상태로 들어갈 때 타이머를 설정함타이머에 설정된 시간이 도래할 때마다 한번씩 깨어나 자신이 기다리던 리소스가 사용가능해졌거나
해야 할 일이 생겼는지 확인함.
DBWR와 LGWR간 상호 작용에 의한 대기 이벤트 발생시 타임아웃은 둘다 3초로 설정됨
log file sync 대기 이벤트 타임 아웃은 1초,
buffer busy wait 대기 이벤트도 1초,
endqueue 관련 lock 대기 이벤트의 타임아웃은 3초
1. 대기 상태에 빠진 프로세스가 기다리던 리소스가 사용 가능해짐
2. 작업을 계속 진행하기 위한 선행 작업 완료
3. 해야 할 일이 생겼을 때
<< 알티베이스 system 대기 이벤트 항목 >>
latch: buffer busy waits
latch: drdb B-Tree index SMO
latch: drdb B-Tree index SMO by other session
latch: drdb R-Tree index SMO
db file multi page read
db file single page read
db file single page write
enq: TX - row lock contention, data row
enq: TX - allocate TXSEG entry
latch free: drdb file io
latch free: drdb tbs list
latch free: drdb tbs creation
latch free: drdb page list entry
latch free: drdb transaction segment freelist
latch free: drdb LRU list
latch free: drdb prepare list
latch free: drdb prepare list wait
latch free: drdb flush list
latch free: drdb checkpoint list
latch free: drdb buffer flusher min recovery LSN
latch free: drdb buffer flush manager req job
latch free: drdb buffer bcb mutex
latch free: drdb buffer bcb read io mutex
latch free: drdb buffer buffer manager expand mutex
latch free: drdb buffer hash mutex
latch free: plan cache LRU List mutex
latch free: statement list mutex
latch free: others
replication before commit
replication after commit
11. shared pool
(1) dictionary cache
오라클 dictionary 정보를 저장해두는 캐시 영역, row단위로 읽고 쓰기 때문에 row cache라고도 불림테이블, 인덱스, 테이블스페이스, 데이터파일, 세그먼트, 익스텐트, 사용자, 제약, Sequence, DB link
# Sequence Cache 옵션
잦은 채번으로 row cache에 경합 발생 가능,
이를 해소하기 위해 sequence 생성시 cache 옵션을 사용해야 함.
동시 채번이 많이 발생하는 sequence 일수록 cache 사이즈를 크게 설정해야 함.
v$rowcache를 통해 hit ratio 조회 가능, 만일 수치가 낮으면 shared pool 크기 증가 고려
column hit_ratio format 990.99
select round((sum(gets - getmisses))/ sum(gets) * 100,2) hit_ratio
from v$rowcache;
HIT_RATIO
---------
80.55
select parameter, gets, getmisses, round((gets - getmisses) / gets * 100, 2) hit_ratio
, modifications
from v$rowcache
where gets > 0
order by hit_ratio desc;
PARAMETER GETS GETMISSES HIT_RATIO MODIFICATIONS
-------------------------------- ---------- ---------- --------- -------------
global database name 63 1 98.41 0
dc_users 1098 27 97.54 0
dc_tablespaces 1085 33 96.96 0
dc_awr_control 24 1 95.83 0
dc_objects 6332 666 89.48 116
dc_histogram_data 561 75 86.63 0
dc_rollback_segments 2551 356 86.04 32
dc_segments 952 249 73.84 0
dc_global_oids 85 24 71.76 0
dc_users 30 14 53.33 0
dc_histogram_defs 2278 1070 53.03 0
PARAMETER GETS GETMISSES HIT_RATIO MODIFICATIONS
-------------------------------- ---------- ---------- --------- -------------
dc_histogram_data 1113 536 51.84 0
outstanding_alerts 29 26 10.34 2
dc_object_grants 52 50 3.85 0
dc_users 2 2 0.00 0
dc_sequences 3 3 0.00 3
dc_profiles 1 1 0.00 0
dc_files 43 43 0.00 0
18 rows selected.
select *
from (select count(*) from v$rowcache where type = 'PARENT'),
(select count(*) from v$latch_children where name = 'row cache objects');
COUNT(*) COUNT(*)
---------- ----------
50 50
(2) library cache
DB buffer cache, redo log buffer cache, dictionary cache 등은 데이터 입출력을 빠르게 하기 위한 캐시 영역
library cache는 사용자가 던진 SQL과 그 실행 계획을 저장해두는 캐시영역
