현상
DB 운영 중 간헐적으로 snapshot too old 오류가 발생하여 원하는 작업이 완료되지 않는 경우가 있습니다.
쿼리 수행 중 아래와 같은 에러 메시지가 출력됩니다.
[09-07T02:48:26.170817] [FRM-1203] [I] THROW. ec=ERROR_TX_SNAPSHOT_TOO_OLD(-21003) [ Snapshot is too old.] (csr_id:0) [tx_usgmt.c:2123:usgmt_snapshot_too_old]
SQL
복사
원인
해당 오류는 쿼리 수행 시간이 길어질 경우 주로 발생하며, Undo 블록에 저장된 정보가 다른 트랜잭션에 의해 재사용되었을 경우, 해당 Undo 블록에서 데이터를 참조할 수 없어 발생합니다.
이는 데이터의 읽기 일관성을 보장하기 위한 메커니즘 중 하나로, 필요한 Undo 정보가 사라졌을 때 snapshot too old 오류가 발생하게 됩니다.
해결
1. _USGMT_ONLINE_MAX , _USGMT_ONLINE_MIN 설정값 변경
_USGMT_ONLINE_MIN
online 상태의 노드별 usgmt 최소 갯수를 설정합니다.
Tibero 기동 이후에 설정값 만큼 Online 됨으로 초기 부하에 대처가 가능합니다.
세션 수 * 노드수 * 20% =
ex) 3000(세션 수) * 2(TAC 2node) * 0.2(20%) = 120
_USGMT_ONLINE_MAX
online 상태의 usgmt 노드전체 최대갯수를 설정합니다.
동시 수행 tx가 많은 경우 가능한 하나의 tx가 하나의 usgmt를 사용할 수 있도록 갯수를 늘려주는 것이 좋습니다. 예상되는 동시 수행 tx 갯수(세션수) 정도로 설정하는 것이 좋습니다.
세션 수 * 노드 수 = 3000 * 2 = 6000
ex) 3000(세션 수) * 2(TAC 2node)
티베로 파라미터 파일에 설정
$ vi $TB_HOME/config/$TB_SID.tip
_USGMT_ONLINE_MIN=1200
_USGMT_ONLINE_MAX=6000
SQL
복사
재기동 후 적용
$ tbdown immediate
$ tbboot
$ tbsql sys/tibero
SQL> select count(*) from DBA_UNDO_SGMTS;
SQL> select name, value from vt_parameter where name in ('_USGMT_ONLINE_MIN','_USGMT_ONLINE_MAX');
SQL
복사
참고
2. UNDO_RETENTION 변경
가장 오래 수행되는 트랜잭션이 소요되는 시간보다 길게 설정해야 합니다.
•
V$UNDOSTAT 뷰의 BEGIN_TIME, END_TIME 컬럼을 통해 장시간 수행된 쿼리의 시간을 확인할 수 있습니다.
•
UNDO_RETENTION은 기본 설정값인 900초 이상으로 설정하는 것 권장합니다. (단위: 초)
SQL> show param UNDO_RETENTION
NAME TYPE VALUE
---------------------------- -------- ----------------------------------------
UNDO_RETENTION INT32 900
- 동적 적용 가능
SQL> ALTER SYSTEM SET UNDO_RETENTION=1800;
- 영구 적용을 위해 티베로 파라미터 파일에 설정
$ vi $TB_HOME/config/$TB_SID.tip
UNDO_RETENTION=1800
SQL
복사
3. Undo tablespace 사이즈 증설
UNDO TABLESPACE를 증설하면 SQL이 실행되는 동안 UNDO SEGMENT가 덮어쓰여질 가능성을 줄일 수 있습니다.
아래는 UNDO TABLESPACE명이 UNDO0일 경우, 32GB 크기의 UNDO002.dtf 파일을 추가하는 예제입니다.
SQL> ALTER TABLESPACE UNDO0 ADD DATAFILE 'UNDO002.dtf' SIZE 32G AUTOEXTEND OFF;
- UNDO TABLESPACE 정보 조회
SQL> set linesize 140
SQL> set pagesize 100
SQL> set feedback off
SQL> col tablespace_name format a15
SQL> select ts_name as "TABLESPACE_NAME",
ROUND(total_size/1024/1024,2) as "TOTAL_SIZE(MB)",
ROUND((total_size - free_size) / 1024/1024 ,2)as "USED_SIZE(MB)",
ROUND(free_size/1024/1024,2) as "FREE_SIZE(MB)",
ROUND(( free_size / total_size) * 100,2) as "FREE_SIZE(%)"
from v$undo_free_space;
SQL
복사
4. SQL 수정
참고
해당 방법은 Application 업무 담당자 협조가 필요합니다.
•
DML Batch 처리의 경우, commit count를 조정하여 fast commit이 발생하도록 변경합니다.
•
쿼리 튜닝을 통해 SQL 수행 속도를 단축시킵니다.
•
쿼리 수행 시간이 짧아질수록 더 많은 UNDO SEGMENT가 필요한 상황을 줄일 수 있습니다.
•
평소 정상적으로 운영되던 시스템에서 SNAPSHOT_TOO_OLD 오류 발생 시, 해당 쿼리의 변경 이력이나 실행 계획 변경이 있었는지를 먼저 확인합니다.
•
또한, 일상적으로 수행되지 않았던 대용량 BATCH 작업이 실행되었는지도 함께 확인합니다.