꿈꾸는 개발자, DBA 커뮤니티 구루비

#언두 조사하기

(on) 무엇이 가장 많거나 적은 언두를 생성하는가?

INSERT [가장적은언두] - DELETE 대상 ROWID 만 저장
UPDATE [둘쨰적은언두] - 변경된 바이트 저장 (대개 일부 컬럼만 변경)
DELETE [가장많은언두] - 대상 ROW의 전체 이미지 저장

Demo#1 (언두측정)
{code:sql	borderStyle=solid} SQL> create table t 2 as 3 select object_name unindexed, 4 object_name indexed 5 from all_objects 6 /

Table created.

SQL> create index t_idx on t ( indexed );

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

SQL> update t set unindexed = lower(unindexed);

61513 rows updated.

SQL> select used_ublk
2 from v$transaction
3 where addr = (select taddr from v$session where sid = (select sid from v$mystat where rownum = 1))
4 /

USED_UBLK

--
1090

SQL> commit;

Commit complete.

SQL> update t set indexed = lower(indexed);

61513 rows updated.

SQL> select used_ublk
2 from v$transaction
3 where addr = (select taddr from v$session where sid = (select sid from v$mystat where rownum = 1))
4 /

USED_UBLK

--
2464
-- 복잡한 인덱스 구조로 인함

|

h3. (on) ORA-01555 : snapshot too old Error
bq. 오라클은 쿼리가 시작한 이후로 변경된 블록을 롤백하기 위해 언두 세그먼트를 사용함으로써 데이터베이스의 스냅샷을 일관적인 시점을 기준으로 제공한다.
bq. ORA-01555 는 데이터 손실과 관계 없는 '안전한' 오류다

||SQL||읽기 일관성 제공||트랜잭션 롤백 제공||
|update t set x = 5 where x = 2;|X = 2 인 ROW 를 찾기 위해 T 를 읽는다|T 를 변경 한다|
|insert into t select * from t where x = 2;|X = 2 인 ROW 를 찾기 위해 T를 읽는다|T 에 입력 한다|
bq. UNDO 는 위의 두가지 역할을 한다

h6. 원인
 * 언두 세그먼트가 너무 작다.
 * 데이터 FETCH 하는 중간에(LOOP) COMMIT 한다
 * 지연된 블록 클린아웃

h6. 해결책
 * UNDO_RETENTION 설정 : 가장 오래 수행하는 트랜잭션에 걸리는 시간보다 많게 (V$UNDOSTAT)
 * 수동 언두 관리(언두 세그먼트 크기 증가) => 자동 언두 관리 사용
 * 쿼리 수행 시간 단축 (튜닝)
 * 관련 객체 통계 정보 수집

h6. Demo#1 (장시간 실행하는 쿼리의 타임라인)

||순번||시간(분:초)||세션||진행사항||
|1|00:00|A|쿼리 시작|
|2|00:01|B|블록 100 을 UPDATE (언두 정보가 언두 세그먼트에 기록)|
|3|00:01|B|COMMIT (순번2 에서 발생한 언두 정보는 언제든 덮어씌워질 수 있음)|
|4|01:00|A|쿼리 진행중 (블록 20 도달)|
|5|01:01|*|지속적인 언두 정보 발생|
|6|03:00|A|쿼리 진행중 (블록 60 도달)|
|7|04:00|*|순번2 에서 발생한 언두 정보가 덮어씌워짐|
|8|05:00|A|쿼리 진행중 블록 100 에 도달 했으나, 언두 세그먼트에 읽기 일관성을 위한 언두 정보가 없음, ORA-01555 발생|

h6. 언두 관리
 * 자동 언두 관리 | 수동 언두 관리
 * UNDO_RETENTION 설정, 권장, 데이터베이스가 언두 세그먼트 익스텐트 재할당 | DBA 가 언두 세그먼트 관리 (수량/크기)
 * UNDO_RETENTION 지정 시간 만큼 언두를 보존 하려고 함 (언두 세그먼트를 재사용 하지 않고 공간을 확장 / NOGURANTEE)

||Demo#2 (아주 작은 언두 테이블스페이스)||
|{code:sql|borderStyle=solid}
-- SESSION SYS
SQL> create undo tablespace undo_small
  2     datafile '/oradata1/small_undo.dbf' size 2m
  3     autoextend off
  4  /

Tablespace created.

SQL> alter system set undo_tablespace = undo_small;

System altered.

-- SESSION A
SQL> create table t
  2  as
  3  select *
  4    from all_objects
  5   order by dbms_random.random;

Table created.

SQL> alter table t add constraint t_pk primary key (object_id)
  2  /

Table altered.

SQL> exec dbms_stats.gather_table_stats( user, 'T', cascade => true );

PL/SQL procedure successfully completed.

-- SESSION A (QA1) - QA1 & QB1 동시 실행
SQL> begin
  2     for x in ( select rowid rid from t )
  3     loop
  4        update t set object_name = lower(object_name) where rowid = x.rid;
  5        commit;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

-- SESSION B (QB1) - QA1 & QB1 동시 실행
SQL> declare
  2     cursor c is
  3     select /*+ first_rows */ object_name
  4       from t
  5      order by object_id;
  6
  7     l_object_name t.object_name%type;
  8     l_rowcnt      number := 0;
  9  begin
 10     open c;
 11     loop
 12        fetch c into l_object_name;
 13        exit when c%notfound;
 14        dbms_lock.sleep( 0.01 );
 15        l_rowcnt := l_rowcnt + 1;
 16     end loop;
 17     close c;
 18  exception
 19     when others then
 20        dbms_output.put_line( ' rows fetched = ' || l_rowcnt );
 21        raise;
 22  end;
 23  /
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 25 with name
"_SYSSMU25_2375957501$" too small
ORA-06512: at line 21

-- ORA-01555 해결책
-- 1. SESSION B 의 읽기 프로세스에 충분한 UNDO_RETENTION 설정 (수행에 720 초 가정 => 900)
-- 2. UNDO TABLESPACE 의 자동증가 혹은 충분한 공간 설정 (자동증가 설정)

-- SESSION SYS
SQL> alter database datafile '/oradata1/small_undo.dbf' autoextend on next 1m maxsize 2048m;

Database altered.

-- QA1, QB1 동시 실행 (정상 종료)
SQL> begin
  2     for x in ( select rowid rid from t )
  3     loop
  4        update t set object_name = lower(object_name) where rowid = x.rid;
  5        commit;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

-- QB1
SQL> declare
  2     cursor c is
  3     select /*+ first_rows */ object_name
  4       from t
  5      order by object_id;
  6
  7     l_object_name t.object_name%type;
  8     l_rowcnt      number := 0;
  9  begin
 10     open c;
 11     loop
 12        fetch c into l_object_name;
 13        exit when c%notfound;
 14        dbms_lock.sleep( 0.01 );
 15        l_rowcnt := l_rowcnt + 1;
 16     end loop;
 17     close c;
 18  exception
 19     when others then
 20        dbms_output.put_line( ' rows fetched = ' || l_rowcnt );
 21        raise;
 22  end;
 23  /                                                                            

PL/SQL procedure successfully completed.

-- session S
SQL> select bytes/1024/1024 from dba_data_files
  2   where tablespace_name = 'UNDO_SMALL';

BYTES/1024/1024
---------------
             20

|

지연된 블록 클린아웃 으로 ORA-01555 발생 케이스

변경 후 커밋 했으나 자동 클린아웃 안됨(버퍼 캐시의 10%를 초과하는 변경 발생)
클린아웃 안된 블록 방문 세션이 장시간 없음
오랜 시간이 걸리는 쿼리가 시작됨 (클린아웃 안된 블록을 일부 읽음)
쿼리가 실행 되는 동안 커밋이 많이 발생
언두 세그먼트에서 트랜잭션 엔트리 / 세그먼트 익스텐트가 재사용 됨
언두 세그먼트에 기록된 가장 작은 SCN이 잦은 커밋으로 인해 읽기 일관성 필요 시점 SCN 보다 커짐

Demo#3 (지연된 블록 클린아웃 으로 ORA-01555 발생...)
{code:sql	borderStyle=solid} SQL> create undo tablespace undo_small 2 datafile '/oradata1/undo_small.dbf' size 4m reuse 3 autoextend off 4 /

Tablespace created.

SQL> create table big
2 as
3 select a., rpad('', 1000, '*') data
4 from all_objects a;

Table created.

SQL> alter table big add constraint big_pk primary key (object_id);

Table altered.

SQL> exec dbms_stats.gather_table_stats( user, 'BIG' );

PL/SQL procedure successfully completed.

SQL> create table small (x int, y char(500) );

Table created.

SQL> insert into small select rownum, 'x' from all_users;

95 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats( user, 'SMALL' );

PL/SQL procedure successfully completed.

-- 테스트시작
SQL> alter system set undo_tablespace = undo_small;

System altered.

SQL> update big
2 set temporary = temporary
3 where rowid in
4 (
5 select r
6 from (
7 select rowid r, row_number() over
8 (partition by dbms_rowid.rowid_block_number(rowid) order by rowid) rn
9 from big
10 )
11 where rn = 1
12 )
13 /

7054 rows updated.

SQL> commit; -- 많은 더티 블록 발생했으며 일부는 디스크로 플러시

Commit complete.

SQL> variable x refcursor
SQL> exec open :x for select * from big where object_id < 1000;
-- 데이터 조회 시점 고정
PL/SQL procedure successfully completed.

SQL> !./run.sh
SQL> print x
– ORA-01555 발생이... 재현안됨 (테스트 DB의 버퍼캐시 환경 때문인듯)

|
|{code:none|borderStyle=solid}
-- run.sh
$ORACLE_HOME/bin/sqlplus / @test2 1 &
$ORACLE_HOME/bin/sqlplus / @test2 2 &
$ORACLE_HOME/bin/sqlplus / @test2 3 &
$ORACLE_HOME/bin/sqlplus / @test2 4 &
$ORACLE_HOME/bin/sqlplus / @test2 5 &
$ORACLE_HOME/bin/sqlplus / @test2 6 &
$ORACLE_HOME/bin/sqlplus / @test2 7 &
$ORACLE_HOME/bin/sqlplus / @test2 8 &
$ORACLE_HOME/bin/sqlplus / @test2 9 &

|

{code:none

borderStyle=solid}
– test2.sql
begin
for i in 1 .. 5000
loop
update small set y = i where x = &1;
commit;
end loop;
end;
/
{code}

해결책

적절한 크기의 트랜잭션 여부 및 필요 이상 빈번한 커밋 여부 확인
객체 변경 후 혹은 읽기 전 DBMS_STATS 사용 (클린아웃시행)
UNDO_RETENTION 증가 및 UNDO TABLESPACE 확장
쿼리의 실행 시간 튜닝

언두 조사하기 (by openlsw) [2012.10.20]

#언두 조사하기

(on) 무엇이 가장 많거나 적은 언두를 생성하는가?

지연된 블록 클린아웃 으로 ORA-01555 발생 케이스

해결책