태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'Redo'에 해당되는 글 2건

  1. 2010.08.11 Direct Path Insert와 Redo 데이터 (7)
  2. 2007.10.11 Oracle Redo Log 기록을 제어하는 세가지 파라미터 (1)

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 : APPEND, Redo, 티팩
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


Oracle Redo Log 기록을 제어하는 세가지 파라미터

오라클 2007.10.11 17:23
오라클 10g R2에서 COMMIT_WRITE 파라미터가 추가됨으로써 이제 사용자가 오라클의 Redo 기록을 제어할 수 있는 길이 생겼다. 이전에는 일부 히든 파라미터로만 가능했던 일이다.

잘 사용하면 앉아서 코풀기... 손쉽게 성능을 개선할 수 있다. 하지만 그만큼 위험이 따른다.

역시 최고봉은 COMMIT_WRITE 파라미터이다. 아래 정보를 보자.

기본 정보

Parameter 정보


Syntax{IMMEDIATE|BATCH},{WAIT |NOWAIT}
DefaultIMMEDIATE, WAIT
설정방법
  • Parameter File
  • ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT
  • ALTER SESSION SET COMMIT_WRITE = BATCH, NOWAIT
  • COMMIT WRITE BATCH NOWAIT
지원 10gR2


설명

Transaction에 대한 Commit 수행시 Redo Write를 실행하는 방식을 결정한다. 사용자가 Commit을 수행하면 오라클은 Redo Buffer의 변경 내역을 Redo Log에 즉각(IMMEDIATE) 기록하며, 실제 Redo Log에 기록될 때까지 기다린다(WAIT). 이러한 수행 방식은 사용자에 의한 데이터 변경 내역이 Redo Log에 기록되어 사후 Recovery에 사용될 수 있다는 것을 100% 보장하기 위해서이다. COMMIT_WRITE 파라미터를 이용하면 이러한 Redo Log 기록 방식을 변경할 수 있다. 가능한 조합은 다음과 같다.

  • IMMEDIATE + WAIT: 기본 방식으로 Redo Write를 즉각 요청하고 기록이 끝날 때까지 기다린다.
  • IMMEDIATE + NOWAIT: 요청은 즉시 보내되, 기록이 끝나기를 기다리지 않고 사용자에게 제어권을 넘겨준다.
  • BATCH + WAIT: 여러 개의 Redo Write 요청을 모아서 한번에 요청하며, 기록이 끝날 때까지 기다린다.
  • BATCH + NOWAIT: 여러 개의 Redo Write 요청을 모아서 한번에 요청하며, 기록이 끝나기를 기다리지 않고 사용자에게 제어권을 넘겨준다.


참고 사항

COMMIT_WRITE와 OLTP/DSS

OLTP 환경에서는 COMMIT_WRITE 파라미터를 변경해서는 안된다. BATCH나 NOWAIT 모드의 Commit에서는 Redo Log에 데이터가 정확하게 저장된다는 보장이 없기 때문에 비정상적인 종료시 데이터의 정합성을 보장할 수 없다.
DSS 환경에서는 데이터의 세밀한 정합성이 불필요한 경우가 많기 때문에 성능 개선을 위해서 이 파라미터를 적극적으로 활용할 수 있다.

PL/SQL의 Asynchronous Commit

PL/SQL 블록 내의 Commit 요청은 마치 BATCH, NOWAIT 모드의 COMMIT_WRITE 파라미터를 사용하는 것과 같은 효과가 있다. 이를 Asynchronous Commit이라고 부른다. 따라서 배치 작업을 처리할 경우에는 PL/SQL을 사용하는 것이 성능면에서 유리하다.

관련된 정보들

  1. _WAIT_FOR_SYNC 파라미터
  2. _DISABLE_LOGGING 파라미터
  3. log file sync 대기이벤트

대용량의 데이터를 처리하는 경우(DSS/DW)에는 COMMIT_WRITE = BATCH, NOWAIT 를 지정함으로써 Commit에 의한 지연을 최소화할 수 있다.

또 하나의 재미있는 파라미터는 _WAIT_FOR_SYNC 이벤트이다. 이 파라미터를 FALSE로 지정하면 Commit 요청시 기록이 끝나기를 기다리지 않고 즉각 사용자에게 제어권을 넘겨준다. COMMIT_WRITE = NOWAIT 와 동일한 역할을 한다. COMMIT_WRITE의 등장과 함께 사라질 파라미터가 된 셈이다.
COMMIT_WRITE는 시스템 레벨, 세션 레벨, 트랜잭션 레벨에서 제어할 수 있다는 점이 특히 돋보인다.

또 하나의 있어서는 안될 것 같은 파라미터가 바로 _DISABLE_LOGGING 파라미터이다. 파라미터 정보는 다음과 같다.

기본 정보

Parameter 정보


Syntax_DISABLE_LOGGING
DefaultFALSE
설정방법
  • Parameter File
  • ALTER SYSTEM SET "_DISABLE_LOGGING" = FALSE
지원 대부분의 버전


설명

Redo Buffer의 내용을 Redo Log에 기록할 지의 여부를 지정한다. 만일 FALSE로 값을 지정하면 기록이 이루어지지 않는다. Import 작업이 Direct Load 작업의 성능을 높이기 위해 사용되는 경우가 있다. 하지만 데이터 정합성과 복구(Recovery)에 영향을 줄 수 있기 때문에 권장되지 않는다.

참고 사항

파라미터의 한계

_DISABLE_LOGGING 파라미터는 Redo Log에 기록되는 작업만을 제어한다. 값을 FALSE로 지정하더라도 다음과 같은 작업들은 여전히 이루어진다.

즉, Redo Log에 기록하는 실제 I/O 작업만이 이루어지지 않을 뿐 그 외의 Redo와 관련된 모든 작업은 다 이루어진다. 따라서 Redo Buffer의 크기가 큰 경우에 특히 유리하다. Redo Buffer의 크기가 크면 그 만큼 Log Switch가 덜 발생하고 Checkpoint 또한 이루어지지 않기 때문이다.

Import/Direct Load와의 관계

대량의 데이터를 로딩하는 경우 _DISABLE_LOGGING 파라미터를 사용하는 것보다는 Direct Load를 사용하는 것이 바람직하다. Direct Load를 사용하는 경우 Redo 데이터 기록을 하지 않기 때문에 성능을 극적으로 개선시킬 수 있다.

Direct Load를 사용할 수 없는 일반 Import의 경우에는 _DISABLE_LOGGING 파라미터가 도움이 될 수 있다. 그러나 이 경우에도 최후의 방법으로 남겨두는 것이 좋다. 사용 가능한 방법들은 다음과 같다.

  • Commit 빈도를 줄인다. 즉 가능한 여러 단위를 묶어서 Commit을 수행한다.
  • Index를 삭제한 상태에서 Loading한다. Loading이 종료되면 인덱스를 Nologging 모드로 재생성한다.
  • COMMIT_WRITE 파라미터를 사용한다.

관련된 정보들

  1. COMMIT_WRITE 파라미터
  2. _WAIT_FOR_SYNC 파라미터

이 파라미터는 정확하게 말하면 Commit이 아니라 Redo Log에의 기록 자체를 제어한다. 즉 Redo Buffer의 데이터를 Redo Log에 기록할 것인가 말것인가를 결정한다. FALSE로 지정하면 Redo 기록에 의한 Disk I/O가 사라지므로 그만큼 성능에 유리하다.

말 그대로 있어서는 안되는 파라미터이지만, 까다로운 고객을 만족시키기 위해 히든 파라미터로 추가된 셈이다. Direct Path Load를 사용할 수 없는 상황에서 대량 DML을 성능을 높이고자 할 경우 임시로 사용하는 목적으로 활용된다. 단, 인스턴스 크래시가 발생했을 때 복구가 이루어지거나 데이터 정합성이 맞을 것이라고 절대로 상상해서는 안된다.

COMMT_WRITE, _WAIT_FOR_SYNC, _DISABLE_LOGGING 이 세개의 파라미터를 비상 실탄처럼 가지고 있다가 다른 어떤 수단으로도 성능 개선이 어려울 때 사용하는 목적으로 활용해보기 바란다.


신고
tags : commit, Oracle, Redo
Trackback 0 : Comment 1
  1. oraking 2011.02.02 14:19 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment