Direct Path Insert와 Redo 데이터

[송지환]

테이블 재생성 또는 대량 데이터 INSERT 시 REDO LOG 발생 여부에 대한 내용

단순히 NOLOGGING 모드이면 로그가 안찬다고 생각했는데

착각하고 있었던 부분이네요.

출처: http://ukja.tistory.com/333 [오라클 성능 문제에 대한 통찰 - 조동욱]

출처: http://ukja.tistory.com/333 [오라클 성능 문제에 대한 통찰 - 조동욱]

출처: http://ukja.tistory.com/333 [오라클 성능 문제에 대한 통찰 - 조동욱]

출처: http://ukja.tistory.com/333 [오라클 성능 문제에 대한 통찰 - 조동욱]

의외로 많은 분들이 아직도 Direct Path Insert(INSERT /*+ APPEND */ ... 포함)와 Redo 생성량간의 관계에 대해서 헷갈려하시는 것 같습니다.

Direct Path Insert에서 Redo 생성량에 영향을 주는 기본적인 요소는 다음과 같습니다.

• 데이터베이스가 ARCHIVELOG 모드인가, NOARCHIVE LOG 모드인가?
• 테이블이 LOGGING 모드인가, NOLOGGING 모드인가?
• 테이블에 인덱스가 존재하는가?

위의 세가지 요소에 대해 Redo 생성량이 어떻게 되는지 아래와 같이 테스트해보겠습니다. (V$SESSTAT에서 redo size, redo entries를 수집하고 그 값을 비교하기 위해 티팩을 사용합니다)set echo on set pages 10000 set lines 200 set timing off set trimspool on set serveroutput off drop table t_log_no_idx purge; drop table t_log_with_idx purge; drop table t_nolog_no_idx purge; drop table t_nolog_with_idx purge; create table t_log_no_idx (c1 char(100)) logging; create table t_log_with_idx (c1 char(100)) logging; create table t_nolog_no_idx (c1 char(100)) nologging; create table t_nolog_with_idx (c1 char(100)) nologging; create index t_log_with_idx_n1 on t_log_with_idx(c1) ; create index t_nolog_with_idx_n1 on t_nolog_with_idx(c1) ; ---------------------------------------------- -- case 1: noarchive log mode + no_index select name, log_mode from v$database; -- NOARCHIVELOG truncate table t_log_no_idx; truncate table t_log_with_idx; truncate table t_nolog_no_idx; truncate table t_nolog_with_idx; exec tpack.begin_session_snapshot; insert into t_log_no_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; insert /*+ append */ into t_log_no_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; insert /*+ append */ into t_nolog_no_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; col type format a6 col item format a30 col deltas format a30 select type, item, deltas from table(tpack.session_snapshot_report) where type = 'STAT' and item in ('redo size', 'redo entries') ; ---------------------------------------------- -- case 2: noarchive log mode + with_index select name, log_mode from v$database; truncate table t_log_no_idx; truncate table t_log_with_idx; truncate table t_nolog_no_idx; truncate table t_nolog_with_idx; exec tpack.begin_session_snapshot; insert into t_log_with_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; insert /*+ append */ into t_log_with_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; insert /*+ append */ into t_nolog_with_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; commit; col type format a6 col item format a30 col deltas format a30 select type, item, deltas from table(tpack.session_snapshot_report) where type = 'STAT' and item in ('redo size', 'redo entries') ; ----------------------------------------------- -- alter database to archive log mode connect sys/password as sysdba shutdown immediate startup mount alter database archivelog; alter database open; connect user/password ---------------------------------------------- -- case 3: archive log mode + no_index select name, log_mode from v$database; -- ARCHIVELOG truncate table t_log_no_idx; truncate table t_log_with_idx; truncate table t_nolog_no_idx; truncate table t_nolog_with_idx; exec tpack.begin_session_snapshot; insert into t_log_no_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; insert /*+ append */ into t_log_no_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; insert /*+ append */ into t_nolog_no_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; col type format a6 col item format a30 col deltas format a30 select type, item, deltas from table(tpack.session_snapshot_report) where type = 'STAT' and item in ('redo size', 'redo entries') ; ---------------------------------------------- -- case 4: archive log mode + with_index select name, log_mode from v$database; truncate table t_log_no_idx; truncate table t_log_with_idx; truncate table t_nolog_no_idx; truncate table t_nolog_with_idx; exec tpack.begin_session_snapshot; insert into t_log_with_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; insert /*+ append */ into t_log_with_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; insert /*+ append */ into t_nolog_with_idx select 'x' from dual connect by level <= 100000; exec tpack.add_session_snapshot; commit; col type format a6 col item format a30 col deltas format a30 select type, item, deltas from table(tpack.session_snapshot_report) where type = 'STAT' and item in ('redo size', 'redo entries') ; Redo Size의 측정 결과를 정리해보면 아래 표와 같습니다.

간단하게 정리해보면 다음과 같네요.

1. NOARCHIVELOG 모드일 경우에는 테이블의 Logging 모드 여부에 상관없이 Direct Path Insert는 최소의 Redo를 생성한다.
2. ARCHIVELOG 모드일 경우에는 테이블이 Nologging 모드여야만 Direct Path Insert가 최소의 Redo를 생성한다.
3. 인덱스가 존재할 경우에는 Nologging모드에서 Direct Path Insert를 수행하더라도 적지 않은 Redo를 생성한다. 인덱스를 변경하는 과정에서의 Redo는 여전히 생성되기 때문이다.

세 번째 이유 때문에 보통 대량의 데이터를 Direct Path Insert로 추가할 경우에는 인덱스를 비활성화시킨 후 Insert가 끝나고 나면 리빌드하는 방법을 많이 사용합니다.이 외에 Direct Path Insert에서 헷갈려하시는 내용들에는 다음과 같은 것들이 있습니다.

• INSERT ... VALUES ... 구문도 Direct Path Insert가 되는가? 이 문제는 이 블로그 포스트에서 상세하 다룬바 있습니다. 요약하면, Oracle 10g까지는 불가능, Oracle 11gR1에서는 APPEND 힌트, Oracle 11gR2에서는 APPEND_VALUES 힌트를 사용하면 됩니다.
• UPDATE나 DELETE 문장에서도 Direct Path Mode가 가능한가? 더 정확하게 말하면 최소한의 Redo를 생성하는 방식의 UPDATE나 DELETE 수행이 가능한가?입니다. 상식적으로 생각해보면 불가능하다는 판단을 내릴 수 있습니다. INSERT 문장이 Direct Path Mode가 가능한 것은 새로운 데이터의 추가이기 때문입니다. 기존 데이터를 변경하지 않기 때문에 테이블에 TM 락을 Exclusive하게 걸어버리고, High Water Mark 위에 데이터를 Append해버리면 됩니다. 중간에 에러가 생기면 추가된 공간을 무시해버리면 되기 때문에 Redo를 생성하지 않는 옵션이 가능합니다. 하지만 UPDATE나 DELETE는 기존 데이터를 변경하는 것이기 때문에 반드시 Redo가 생성됩니다.

제가 자주 접했던 질문에 대한 대답은 위의 내용 정도로 정리가 가능하겠습니다. 그 외에도 Redo 생성과 관련해서 이슈들이 많을텐데 기회가 닿은 대로 논의하면 좋겠네요.