태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Direct Path Insert와 Redo 데이터

오라클 2010.08.11 15:00
의외로 많은 분들이 아직도 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의 측정 결과를 정리해보면 아래 표와 같습니다.

.

NOARCHIVEARCHIVE

.

LoggingNologgingLoggingNologging

.

No IndexConventional13,327,16413,327,16413,353,08813,353,088

.

Direct 1,317,3721,351,51213,419,2281,360,888

.

With IndexConventional80,755,88480,755,88481,188,59281,188,592

.

Direct 55,740,47248,755,74067,802,16848,652,796

.

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

  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 생성과 관련해서 이슈들이 많을텐데 기회가 닿은 대로 논의하면 좋겠네요.
저작자 표시
신고
tags : , ,
Trackback 0 : Comments 7
  1. 라튜니 2010.08.11 20:33 신고 Modify/Delete Reply

    항상 좋은 정보를 얻고 있습니다. 먼저 감사의 말씀을 드립니다.

    다름이 아니라 글을 보고 궁금한 점이 있어 염치불구 하고 이렇게 질문 드립니다.

    NOARCHIVE, ARCHIVE 모드 관계없이 인덱스가 있을 경우는 상당량의 REDO가 발생하는데요.

    인덱스가 있는 경우 해당 인덱스에도 NOLOGGING 모드로 설정해 버리면

    생성되는 REDO가 줄어드나요? 아님 인덱스 자체도 NOLOGGING 모드로 설정가능하지만,

    인덱스의 경우 KEY값의 순서에 해당하는 블록에 INSERT 되어야 하기 때문에

    NOLOGGING 모드 설정 자체가 큰 의미가 없는건지요? 궁금하네요~ 답변 부탁드립니다.


    여름철 건강 관리 잘 하시고 좋은 하루 되시길~

    • 욱짜 2010.08.12 09:11 신고 Modify/Delete

      말씀하신 것처럼 위의 예에서는 인덱스에 대한 Logging 모드는 의미가 없습니다. 스크립트에 오해의 소지가 있으므로 그 부분은 수정하도록 하겠습니다.

  2. 2dong0 2010.08.13 14:51 신고 Modify/Delete Reply

    언제나 좋은 정보 감사합니다.^^
    저도 욱짜님처럼 오라클을 완벽하게 이해하고 있으면 좋겠습니다^^ㅋㅋ

    • 욱짜 2010.08.13 15:21 신고 Modify/Delete

      완벽하게 이해하고 있다는 것은 오해의 소지가 있는데요?

      저는 성능 문제에 있어 가능한 많은 데이터를 분석하고 가능한 정확한 결론을 내리게끔 노력할 뿐입니다. 그리고 그런 방법을 공유하는 것 뿐이구요.

      완벽하게 이해했다면 이미 이 업계를 떠났을 겁니다. :)

  3. 2dong0 2010.08.15 20:41 신고 Modify/Delete Reply

    그저 제가 보기에 완변해보이실뿐입니다^^
    언제나 좋은 정보 공유해 주셔서 감사할 따름입니다
    업계를 떠나시는 그날까지 화이팅!!! 하세요^^

  4. 열심남 2010.08.16 13:24 신고 Modify/Delete Reply

    저도 담부터는 대량 작업이 잇으면 인덱스 disable하고 리빌드 하는 방식으로 가이드를 해야겠네요 ^^: 감사합니다.

    • 욱짜 2010.08.16 14:17 신고 Modify/Delete

      한가지 주의할점이 있습니다. 만일 새롭게 추가되는 데이터의 양에 비해 인덱스의 크기가 매우 크다고 하면(즉 기존의 데이터가 훨씬 많으면) 오히려 인덱스를 리빌드하는 것이 더 부담이 될 수도 있습니다. 데이터의 양에 따라 적절히 안배하면 되겠습니다.

Write a comment