태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'APPEND'에 해당되는 글 2건

  1. 2010.08.11 Direct Path Insert와 Redo 데이터 (7)
  2. 2010.06.21 INSERT /*+ APPEND */ ... VALUES ... (8)

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


INSERT /*+ APPEND */ ... VALUES ...

오라클 2010.06.21 13:43
한건의 로우(Row)를 Insert하는데 APPEND 힌트를 이용한 Direct Path Mode가 필요한지는 잘 모르겠습니다. 어쨌든 오라클은 11g부터 이러한 모드를 지원하기로 했습니다. 즉 INSERT ... VALUES ... 구문에서도 APPEND 힌트가 동작하도록 수정되었습니다. 좀 더 상세하게 정리를 해보면
  • Oracle 10g까지는 INSERT ... VALUES ... 구문에 APPEND 힌트를 부여해도 무시됩니다. 즉, APPEND 힌트는 INSERT ... SELECT 구문에서만 동작합니다.
  • APPEND 힌트로 인해 Direct Path Mode로 동작하면 로우를 Insert할 때 현재 세그먼트의 Free Block을 무시하고(동시에 Buffer Cache를 경유하지 않고) 세그먼트의 High Water Mark 뒤에 Append 시켜버립니다. 따라서 대량의 데이터를 추가할 때 속도는 빠르지만 데이터 파일에 그만큼 불필요한 빈공간이 남아있을 수는 있습니다. (하지만 이 빈공간도 언젠가는 사용될 것입니다)
  • Oracle 11gR1에서는 INSERT ... VALUES ... 구문에서도 APPEND 힌트가 동작합니다. 히든 파라미터 _direct_path_insert_features에 의해 제어됩니다.
  • Oracle 11gR2에서는 INSERT ... VALUES ... 구문에서 APPEND 힌트를 부여해도 무시됩니다(응?). 대신 APPEND_VALUES 라는 힌트가 추가되었습니다. 즉 APPEND_VALUES 힌트를 사용하면 INSERT ... VALUES ... 구문에서도 Direct Path Mode로 Insert가 이루어집니다.
간단한 테스트 결과입니다.
create table t1(c1 number);

insert into t1 values(1);
commit;
insert into t1 values(2);
commit;

-- (2건의 로우를 Insert했고 Conventional Path Mode이므로 두 개의 로우가 같은 Block에 있을 것입니다)
select
	dbms_rowid.rowid_relative_fno(rowid) as file#,
	dbms_rowid.rowid_block_number(rowid) as block#
from t1;

-- 10.2.0.1
     FILE#     BLOCK#
---------- ----------
         4        853
         4        853

-- 11.1.0.6
     FILE#     BLOCK#
---------- ----------
         4        758
         4        758

-- (APPEND 힌트를 부여한 INSERT ... VALUES 구문입니다)
insert /*+ append */ into t1 values(3);
commit;

-- 10.2.0.1에서는 APPEND 힌트가 무시되므로 역시 같은 Block에 Insert 됩니다.
     FILE#     BLOCK#
---------- ----------
         4        853
         4        853
         4        853

-- 하지만 11.1.0.6에서는 APPEND 힌트가 동작했고, 그 결과 High Water Mark의 끝 이후로 Insert 되어 버립니다.
     FILE#     BLOCK#
---------- ----------
         4        758
         4        758
         4        769

-- 11.2.0.1에서는 APPEND_VALUES 힌트가 그 역할을 대신합니다.
iinsert /*+ append_values */ into t1 values(3);
commit;

     FILE#     BLOCK#
---------- ----------
         4        526
         4        526
         4        528
왜 오라클 개발자들이 갑자기 INSERT ... VALUES ... 구문에서도 APPEND 힌트가 동작하도록 바꾼 것일까요? 아무리 생각해도 모르겠습니다.

아마 이전에 일하던 개발자가 그만두고 가면서 인수인계가 잘 안되었나봅니다. 이 모든 것은 사람이 하는 것이기에... :)

저작자 표시
신고
Trackback 0 : Comments 8
  1. 왕만두 2010.06.22 09:30 신고 Modify/Delete Reply

    TABLE 이 Compress 로 되어 있을때 Direct Load 로 입력하지 않으면 Compress 가 무의미하므로
    11g 부터 insert ~ values 에도 append 를 추가한것 같네요.
    좋은글 감사합니다.

    • 욱짜 2010.06.22 10:21 신고 Modify/Delete

      Oracle 11gR1에서는 OLTP Compression 기능이 추가되었기때문에 Insert ... Values... 에서도 Compression이 가능합니다. 그러니까 아마 다른 이유가 있을 것 같습니다.

  2. 백면서생 2010.06.22 10:32 신고 Modify/Delete Reply

    insert ~ select 절을 사용할수 없는 상황이면서
    pl/sql이나 java 등에서 array processing할때 성능에 도움이 되지는 않을런지요..^^

    • 욱짜 2010.06.22 10:49 신고 Modify/Delete

      INSERT ... VALUES 구문을 통해 "1건"을 Insert할 때 Direct Path Mode를 사용하면 그 오베허드(블록킹, Physical I/O 발생, Data File의 불필요한 크기 증가 등)가 훨씩 더 큽니다. 배보다 배꼽이 더(너무나 더) 크다고 할 수 있겠습니다.

      적은 수의 데이터를 Insert할 때는 Conventional Path Mode가 훨씬 더 효율적입니다. Logical I/O만으로 원하는 작업을 할 수 있으니까요.

    • 백면서생 2010.06.24 10:36 신고 Modify/Delete

      이런류의 array processing에서 효과는 없을런지요.^^
      CREATE OR REPLACE PROCEDURE a2 (p_array_size IN PLS_INTEGER DEFAULT 100)
      IS

      TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
      l_data ARRAY;

      CURSOR c IS
      SELECT *
      FROM all_objects;

      BEGIN
      OPEN c;
      LOOP
      FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

      FORALL i IN 1..l_data.COUNT
      INSERT /*+ APPEND_VALUES */ INTO tb2 VALUES l_data(i);
      commit;
      EXIT WHEN c%NOTFOUND;
      END LOOP;
      close c;
      END a2;
      /

  3. 욱짜 2010.06.24 13:35 신고 Modify/Delete Reply

    FORALL ... INSERT ... VALUES 구문은 INSERT ... SELECT 와 거의 동일하게 동작하므로 APPEND 힌트(혹은 APPEND_VALUES 힌트)가 확실히 의미가 있겠습니다.

    좋은 발견입니다.

    • 욱짜 2010.06.24 15:37 신고 Modify/Delete

      좀 더 점검을 해보니 SAVE EXCEPTIONS 구문과는 같이 사용이 안되는군요. 다음과 같은 에러가 발생합니다.

      ORA-38910: BATCH ERROR mode is not supported for this operation

      만일 Oracle 10g에서 FORALL INSERT VALUES 구문에 대해 Direct Path Insert를 사용하려면 다음과 같은 방법을 사용할 수도 있습니다.

      insert /*+ append */ into t1 select * from table(cast(... as ...));

      이 구문을 사용하려면 Collection Type이 create or replace를 통해 명시적으로 선언되어있어야 할 것 같은데요. 제약으로 여길 수 있겠습니다.

      INSERT ... VALUES ... 구문에서 APPEND(11gR1)나 APPEND_VALUES(11gR2) 힌트를 지원하는 목적인 PL/SQL Batch Insert인지 아닌지는 알 수 없지만 잘 이용하면 유용하다는 것만은 확실해보입니다.

  4. ilsantan 2013.02.20 14:32 Modify/Delete Reply

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

Write a comment