오라클 성능 고도화 원리와 해법 I (2012년)
참고 자료
Snapshot too old( ORA-01555)
- 1. 데이터를 읽어 내려가다가 쿼리 SCN이후에 변경된 블록을 만나( 이미 앞서 읽었던 블록을 다시 방문하는 경우 일 수도 있음)
- 과거 시점으로 롤백한 'Read Consistent' 이미지를 얻으려고 하는데, UNDO 블록이 다른 트랜잭션에 의해 이미 재사용되 필요한 Undo 정보를 얻을 수 없는 경우다.
- 프로그램 코딩 패턴에 문제가 없다면 Undo 세그먼트가 너무 작다는 신호일 수 있다.
- 2. 커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인 할 수 없는 경우로서, Undo 세그먼트 개수가 적다는 신호일 수 있다.
(1) Undo 실패
예제 1 ( p.80 )
- 쿼리문에 ORDERED USE_NL 힌트를 사용한 것에 주목하자. 대량의 데이터를 기준( driving 테이블, 여기서는 과금 테이블)으로 NL 조인을 하면 쿼리 수행시간이 오래 걸릴 뿐 아니라 inner 테이블( 수납 )에 대해 같은 블록을 반복해서방문할 가능성이 커진다.
고객 미납금액을 계산하는 아래 쿼리가 1시간쯤 걸린다고 가정하자
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
- 1) SCN 123 시점에서 이 쿼리가 시작되었다.
- 2) 쿼리가 진행되는 동안 은행으로부터 고객의 입금내역을 전송받아 일괄 처리하는 배치 프로그램이 수행되었는데, 프로그램은 각 건별로 커밋하도록 작성되었다.( 루프문안에 COMMIT; )
- 이 프로그램이 돌기 시작한지 얼마 지나지 않아 다른 트랜잭션에 의해 홍길동 고객의 수납액이 10,000원에서 20,000 원으로 변경되고 나서 커밋되었다.
- 이때, 블록 SCN은 129로 변경되었다.
- 3) 시간이 흘러 홍길동 고객의 수납액 변경내역( 수납액 10,000 -> 20,000 )을 담은 Undo블록이 다른 트랜잭션에 의해 재사용되었다.
- 4) 위 쿼리를 수행 중인 프로세스가 홍길동 고객의 수납액이 담긴 블록에 도달했을 때, 블록 SCN이 자신의 쿼리 SCN 123보다 큰 129임을 확인하고, 변경된 UNDO레코드를 찾으려고, ITL 엔트리에 기록된 UBA를 읽어 Undo 세그먼트 블록을 찾아간다.( 그림 1- 16 )
- 5) 하지만 Undo블록은 이미 다른 트랜잭션에 의해 재사용된 상태이므로 ORA-01555에러를 발생시키며 진행을 멈춘다.
 |
예제 1 해결책 (주관적 )
SELECT /* ORDERED USE_HASH( 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
ORDER BY A.고객ID
예제 2 ( p.82 )
1 FOR C IN (SELECT /*+ ORDERED USE_NL( B ) */ A.고객ID, A.입금액, B.수납액
2 FROM 은행입금 A, 순납 B
3 WHERE A.입금일자 = TRUNC( SYSDATE )
4 AND B.수납년월(+) = TO_CHAR( SYSDATE, 'YYYYMMDD' )
5 AND B.고객ID(+) = A.고객ID )
6 LOOP
7 IF C.수납액 IS NULL THEN
8 INSERT INTO 수납( 고객ID, 수납년월, 수납액 )
9 VALUES( C.고객ID, TO_CHAR( SYSDATE, 'YYYYMM' ), C.입금액 );
10 ELSE
11 UPDATE 수납 SET 수납액 = 수납액 + C.입금액
12 WHERE 고객ID = C.고객ID
13 AND 수납년월 = TO_CHAR( SYSDATE, 'YYYYMMM' );
14 END IF;
15 COMMIT;
16 END LOOP;
- 1) SCN 100시점에서 커서 C를 오픈한다.
- 2) 11 ~ 13번 라인 UPDATE 문에 의해 홍길동 고객의 수납액이 변경되고 15번 라인에 의해 커민된다.
- 이때, 변경된 레코드가 담긴500번 블록 SCN이 120으로 변경되었다.
- 프로그램 내에서 커밋이 반복하기 때문에 위 INSERT 문과 UPDATE문은 모두 개별 트랜잭션으로 처리된다.
- 3) 트랜잭션이 반복되다가 홍길동 고객의 수납액 Befoe Image가 담긴 Undo 블록이
- 다른 트랜잭션에 의해 재사용되었다. 물론 이 트랜잭션도 위 프로그램 내에서 수행된 INSERT 문 또는 UPDATE문일 것이다.
- 4) 커서 C가 Fetch하다가 고객 홍길동의 수납정보와 같이 500번 블록에 저장된 김철수 수납정보에 도달한다.
- 커서 C가 NL 방식으로 수행된다면 쿼리 수행도중 같은 블록을 여러 차례 재방문하는 일은 흔이 발생한다.
- 500번 블록 SCN이 120임을 확인하고 자신의 쿼리 SCN 100시점으로 롤백하려구 Undo 블록을 찾아간다.
- 5) 하지만 Undo 블록은 이미 앞선 트랜잭션에 의해 재사용된 상태이므로 ORA-01555에러를 발생시키며 진행을 멈춘다.
- 위 같은 코딩 패턴을 'fetch across commit'이라고 한다. 명시적으로 커서를 열어 로우를 하나씩 Fetch하면서 값ㅇ르 변경하고 루프 내에서 계속해서 커밋을 날리는 방식이다. ( ANSI 표준 : 커서는 커밋하는 시점에 무효화 됨, 오라클 : 허용 )
예제 2 해결책 (주관적 )
1 FOR C IN (SELECT /*+ ORDERED USE_NL( B ) */ A.고객ID, A.입금액, B.수납액
2 FROM 은행입금 A, 순납 B
3 WHERE A.입금일자 = TRUNC( SYSDATE )
4 AND B.수납년월(+) = TO_CHAR( SYSDATE, 'YYYYMMDD' )
5 AND B.고객ID(+) = A.고객ID )
6 LOOP
7 IF C.수납액 IS NULL THEN
8 INSERT INTO 수납( 고객ID, 수납년월, 수납액 )
9 VALUES( C.고객ID, TO_CHAR( SYSDATE, 'YYYYMM' ), C.입금액 );
10 ELSE
11 UPDATE 수납 SET 수납액 = 수납액 + C.입금액
12 WHERE 고객ID = C.고객ID
13 AND 수납년월 = TO_CHAR( SYSDATE, 'YYYYMMM' );
14 END IF;
15
16 END LOOP;
17 COMMIT;
(2) Delayed 블록 클린아웃 실패 : 거의 없지만 트랜잭션이 과다하게 발생하면 발생 가능성이 있음. p.83
- 1. 대량 업데이트 후에 커밋된 트랜잭션은 변경했던 블록들은 모두 클린아웃하지 않은 상태에서 자신이 사용하던 트랜잭션 테이블 슬롯은 Free( 9 ) 상태로 변경하고 트랜잭션을 완료한다.
이때부터 트랜잭션 테이블 슬롯은 다른 트랜잭션에 의해 재사용 될 수 있다.
- 2. 시간이 흘러 그 변경된 블록들을 읽어야 하는 시점에 하는 시점에 Delayed 블록 클린아웃을 위해 트랜잭션 테이블 슬롯을 찾아갔는데, 해당 슬롯이 다른 트랜잭션에 의해 이미 재사용되고
없다면 Undo 세그먼트 헤더 블록에 가해진 변경사항을 롤백하려고 시도한다. 다행이 찾고자 하는 트랜잭션에 대한 커밋 정보가 언두 블록( 레코드 ) 에 남아 있다면 현재 읽고자하는
블럭을 정확한 커밋 SCN을 가지고 블록 클린아웃을 수행한다.
- 3. 하지만 Undo 레코드를 뒤졌는데 그마저도 덮어 쓰이고 없다면.??
- 4. 트랜잭션 슬롯이 필요해지면 커밋 SCN이 가장 낮은 트랜잭션 슬롯 부터 재사용하는데, 그 슬롯에 기록돼 있던 커밋 SCN을 UNDO 세그먼트 헤더에 '최저 커밋 SCN'으로서 기록해둔다.
- 5. 트랜잭션 슬롯이 재사용되고 나면 그 슬롯을 사용하던 이전 트랜잭션의 정확한 SCN을 확인하는 것이 불가능해 지지만 UNDO 세그먼트 헤더에 기록돼 있는 '최저 커밋 SCN'이전에
커밋 되었다는 사실만큼은 짐작할 수 있다.
- 6. 따라서 아직 클린아웃되지 않은 블록을 큰린하려고 ITL이 가르키는 트랜잭션 테이블 슬롯을 찾아갔을 때 커밋정보가 이미 지워지고 없으면, UNDO 세그먼트 헤더에 있는
'최저 커밋 SCN'을 블록 ITL 엔트이에 커밋 SCN으로서 기록( 추정된 커밋 SCN임을 표기하기 위해 ITL 슬로 커밋 FLAG에 C--- 대신 C-U-라고 기록) 함으로써 블록 클린아웃을
마무리하고, 블록 SCN도 변경한다.
- 7. 문제는 클린아웃 시점에 일관성 모드 일기가 가능한지 여부일 텐데, 쿼리가 진행되는 동안에 많은 트랜잭션이 한꺼번에 몰리지만 않는다면 '최저 커밋 SCN'이 갑자기
많이 증가하지는 않ㅇ르 것이므로 '최저 커밋 SCN'에 의해 추정된 블록 SCN은 대개 쿼리 SCN보다 작다.
따라서 쿼리가 시작된 이후에 해당 블록에 변경이 가해지지 않았음이 확인되므로 정상적인 일관성 모드 읽기도 가능하다.
Delayed 블록 클린아웃 실패 결론
- Delayed 블록 클린아웃에 의해 Snapshot too old가 발생하는 원인은, '최저 커밋 SCN'이 쿼리 SCN보다 높아질 정도로 갑자기 트랜잭션이 몰리는데 있으며, 이때는 추정에 의한 블록 SCN이 쿼리 SCN보다 높아지게 된다.
- 실제로 이 에러를 발생시킨 블록은 오래전 시점( 1년 또는 10년 전 )d에 커밋된 것일 수 있지만 이를 혹인할 방법이 없으며, 그동안 한번도 읽히지 않다가 불행하게도 트랜잭션이 몰리는 시점에 읽히다 보니 문제를 유발하게 되는 것이다.
Delayed 블록 클린아웃으로 인한 Snapahot too ol Test
설정
SQL> create undo tablespace small_undo datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERM\S_UNDO.DBF' SI
ZE 10M AUTOEXTEND OFF;
테이블스페이스가 생성되었습니다.
SQL> alter system set undo_tablespace = 'SMALL_UNDO';
시스템이 변경되었습니다.
SQL> alter system set db_cache_size=1M scope=both ;
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 4M
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> show sga
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 113249156 bytes
Database Buffers 490733568 bytes
Redo Buffers 7135232 bytes
SQL> --Select Query의 속도를 제어하기 위해 Sleep 함수를 만든다.
SQL> create or replace function fsleep(v1 int, vsleep int)
2 return number
3 is
4 begin
5 dbms_lock.sleep(vsleep);
6
7 return 1;
8 end;
9 /
함수가 생성되었습니다.
SQL> drop table t1 purge;
테이블이 삭제되었습니다.
SQL> create table t1(c1 int, c2 varchar2(10));
테이블이 생성되었습니다.
SQL> insert into t1
2 select level, 'AAA'
3 from dual
4 connect by level < 10000
5 ;
9999 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> create index t1_n1 on t1(c1);
인덱스가 생성되었습니다.
SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno,
2 dbms_rowid.rowid_block_number(rowid)as blkno
3 from t1 where rownum = 1;
ROWID FNO BLKNO
------------------ ---------- ----------
AAAN/eAABAAAPBSAAA 1 61522
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
--SESSION 1 번 실행중에 실행
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
--SESSION 1 번 실행중에 실행
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
SESSION 1
select /*+ index(t1) */
c1, c2
from t1
where fsleep(c1,0.01) = 1
and c1 > 0;
...
---------- ----------
3356 AAA
3357 AAA
3358 AAA
3359 AAA
3360 AAA
ERROR:
ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 14 수에 "_SYSSMU14$" 이름으로 된 것이
너무 작습니다
SESSION 2
- Session #2에서는 다음과 같이 Table t1에 대해 DML을 과다하게 발생시킨다. AUM(Automatic Undo Management)에서는 Undo Segment가 Transaction마다 할당되기 때문에 하나의 Undo Segment만을 사용하도록 set transaction use rolllback segment 명령을 사용하는 기법에 유의한다.
-- get rollback segment name
col rollback_seg new_value v_rollback_seg
select '_SYSSMU'||max(segment_id)||'$' as rollback_seg
from dba_rollback_segs
where segment_name <> 'SYSTEM'
;
-- do very frequent commit on t1
begin
for idx in 1 .. 1000 loop
for idx2 in 1 .. 10000 loop
set transaction use rollback segment "&v_rollback_seg";
update t1 set c2 = 'dummy'||idx
where c1 = idx2;
commit;
end loop;
end loop;
end;
/
데이타 덤프
첫번째 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfde csc: 0x00.a750a5 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.02e.00000020 0x01400224.000a.10 --U- 1 fsc 0x0000.00a750a8
0x02 0x000d.025.00000020 0x0140020e.0007.18 --U- 1 fsc 0x0000.00a750a6
두번째 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfde csc: 0x00.a750a5 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.02e.00000020 0x01400224.000a.10 --U- 1 fsc 0x0000.00a750a8
0x02 0x000d.025.00000020 0x0140020e.0007.18 --U- 1 fsc 0x0000.00a750a6
세번재 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfde csc: 0x00.a83265 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e.02b.0000004d 0x0140003e.000d.36 C--- 0 scn 0x0000.00a83264
0x02 0x000d.023.00000066 0x0140002d.0012.21 --U- 1 fsc 0x0000.00a83266
- Session#1은 이미 Commit이 완료된 Table t1에 대해 Query를 수행한다.
- Session#2는 Table t1이 아닌 Table t2에 대해 과도한 DML과 commit을 수행한다.
- Session#1의 Select Query는 이미 Commit이 완료된 Table을 읽기 때문에 Rollback에 실패할 이유가 없다.
- 하지만!!! Session#1은 ORA-01555 에러를 만난다.
- 왜 이런 일이 발생하는가? Delayed Block Cleanout 때문이다.
- Table t1에 대해 Commit이 수행될 때 모든 Data Block에 대해 Commit 여부를 지정하지 않고(즉 일부에만 Commit 여부 지정) Undo Segment Header의 Transaction Table에만 Commit 여부를 지정한다. 이 정보가 완전히 덮어써져서 Rollback하지 못하면 ORA-01555 에러가 나게 된다.
- Block에 Commit 여부를 저장하는 것을 Block Cleanout이라고 부른다. Delayed Block Cleanout는 Block Cleanout를 Commit 시점에 하지 않고 미루겠다(Delay)는 것을 의미한다.
- 그렇다면 언제 Block Cleanout이 이루어지는가? 다른 Session이 해당 Block을 Access하는 시점에 이루어진다.
- 즉 위의 예에서 Session#1은 Delayed Block Cleanout를 수행하기 위해 Transaction Table을 읽을려고 했는데 다른 DML에 의해 해당 정보들이 완전히 덮어써져서 Rollback이 안되기 때문에 ORA-01555 에러를 발생시키는 것이다.
(3) Snapshot too old 회피 방법
- 1. 불필요한 커밋을 자주 수행하지 않는다.
- 2. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다. 다른 방식으로 구현하기 어렵다면 COMMIT 횟수를 줄인다.
- 3. 트랜잭션이 몰리는 시간때는 오래 걸리는 쿼리가 같이 수행되지 않도록 시간ㅇ르 조정한다.
- 4. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다.
- 5. 오랜 시간에 걸쳐 같은 블록을 여러번 방문하는 NL 형태의 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고,
이를 회피 할 수 있는 방법( 조인 메소드 변경, FTS )을 찾는다.
- 6. 소트 부하를 감수하더라도 ORDER BY 등을 강제로 삽입해 소트연산이 발생하도록 한다.
많은 데이터를 오랜 시간에 걸쳐 Fetch 하는 동안 Undo 정보를 지속적으로 참조하기 때문에 발생하는 것이므로
서버 내에서 빠르게 데이터를 읽어 템프 세그먼트에 저장하는 데에만 성공하면 이후에는 같은 블록을 아무리 재방문하더라도 더는 에러가 발생할까 걱정하지 않아도 된다.
- 7. 만약 delayed 블록 클린아웃에 의해 Snapshot too old가 발생하는 것으로 의심되면
대용량 업데이트 후 곧 바로 해당 테이블에 대해 Full Scan 하도록 쿼리를 날리는 것도 하나의 해결방법이 될 수 있다. ( 테이블 풀스캔 )
만약 인덱스 블록에 문제가 발생한다고 판단된다면 인덱스 리프 블록을 모두 스캔하도록 한다. ( 인덱스 플스캔 )
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3073
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.