태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'direct path insert'에 해당되는 글 2건

  1. 2010.06.21 INSERT /*+ APPEND */ ... VALUES ... (8)
  2. 2008.07.29 11g is better - Primary key + Non Unique Index + Direct Path Insert!

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


11g is better - Primary key + Non Unique Index + Direct Path Insert!

오라클 2008.07.29 11:14
Primary Key는 Index를 필요로 한다.

당연히 Unique Index를 필요로 할 것이라고 예상할 것이다. 하지만 간혹 Non Unqiue Index를 사용해야만 하는 경우가 있다. 가령 다음과 같은 경우가 그렇다.

  • Primary Key를 생성하기 전에 Non Unique Index를 이미 생성한 경우. Index를 Drop하고 새로 만드는 엄청난 일을 하기 보다는 아마 기존 Index를 이용해서 Primary Key를 만들고 싶을 것이다.
  • Deferred Constraint를 사용하고 싶은 경우. Primary Key Constraint를 잠시 지연(Defer)시키고 복잡한 Data 조작을 하고 싶은 경우가 있을 것이다.

전자의 경우에는 명시적으로 Non Unique Index를 생성하게 된다. 후자의 경우에는 다음과 같이 Deferrable 속성을 주는 순간 내부적으로 Non Unique Index가 생성된다.

UKJA@ukja102> create table t1(c1 int, c2 int);                                  

Table created.

Elapsed: 00:00:00.00
UKJA@ukja102> 
UKJA@ukja102> alter table t1 add constraint t1_pk primary key (c1) deferrable; <-- Deferrable Primary Constraint!!!

Table altered.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> set serveroutput on
UKJA@ukja102> exec print_table('select index_name, uniqueness 
          from user_indexes where table_name = ''T1''');
INDEX_NAME                    : T1_PK       
UNIQUENESS                    : NONUNIQUE    <-- Non-Unique!!!  
-----------------                           

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
UKJA@ukja102> set serveroutput off
UKJA@ukja102> 
UKJA@ukja102> set constraint t1_pk deferred;

Constraint set.

Elapsed: 00:00:00.00
UKJA@ukja102> 
UKJA@ukja102> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.00
UKJA@ukja102> insert into t1 values(1, 2);

1 row created.

Elapsed: 00:00:00.00
UKJA@ukja102> 
UKJA@ukja102> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (UKJA.T1_PK) violated 
                           
어떤 경우에 해당하든, Primary Key를 Non Unique Index와 함께 사용해야 할 일은 반드시 생기게 마련이다. 하지만 여기에 중요한 문제가 있다.

Non Unique Index로 구성된 Primary Key가 있는 경우에는 direct path insert가 무시된다.

대용량 데이터 생성을 위해 append Hint를 사용해서 direct path insert를 잘 사용하고 있다는 착각하는 순간, 내부적으로 conventional path insert가 이루어지고 있는 것이다. 성능이 저하되는 것은 당연한 수순이다.

다행히 11g에서는 이런 경우에도 direct path insert가 지원된다. 11g를 사용해야 할 이유가 한가지 늘어난 셈이다. 아래에 10g(10.2.0.1)와 11g(11.1.0.6)에서의 테스트 결과가 있다.

테스트에 사용한 Script는 다음과 같다.

drop table t1 purge;
create table t1(c1 int, c2 int);

begin
  if &1 = 1 then
    execute immediate 'create index t1_n1 on t1(c1)';
  else
    execute immediate 'create unique index t1_n1 on t1(c1)';
  end if;
end;
/

alter table t1 add constraint t1_pk primary key(c1);

select index_name 
from user_indexes
where table_name = 'T1'
;

@trace_on 10046 8

insert /*+ append */ into t1
select level, level from dual
connect by level <= 30000
;

@trace_off 
@trace_file
ed &trace_file

테스트 결과는 다음과 같다.
--------------------------------------------
-- 10.2.0.1
-- Non Unique Index를 사용한 경우
-- direct path write Wait Event가 목격되지 않는다

-- Unique Index를 사용한 경우
-- 아래와 같이 direct path write Wait Event가 목격된다.
WAIT #1: nam='direct path write' ela= 3 file number=6 first dba=10930 block cnt=7 obj#=-1 tim=605843960858
WAIT #1: nam='direct path write' ela= 2 file number=12 first dba=2649 block cnt=7 obj#=-1 tim=605843960909
WAIT #1: nam='direct path write' ela= 2 file number=6 first dba=10930 block cnt=7 obj#=-1 tim=605843994227


--------------------------------------------
-- 11.1.0.6
-- Non Unique Index를 사용한 경우
-- 아래와 같이 direct path write Wait Event가 목격된다.
WAIT #5: nam='direct path write' ela= 114 file number=6 first dba=4233 block cnt=8 obj#=-1 tim=605914885393
WAIT #5: nam='direct path write' ela= 588 file number=6 first dba=4242 block cnt=2 obj#=-1 tim=605914886456

-- Unique Index를 사용한 경우
-- 아래와 같이 direct path write Wait Event가 목격된다.
WAIT #5: nam='direct path write' ela= 20 file number=6 first dba=4233 block cnt=8 obj#=-1 tim=605970855998
WAIT #5: nam='direct path write' ela= 177 file number=6 first dba=4242 block cnt=2 obj#=-1 tim=605970856274

11g에서는 어떤 경우든 항상 direct path insert가 이루어진다는 것을 알 수 있다.

11g에서 또 하나의 중요한 성능 걸림돌이 제거된 것을 기뻐하며, 언젠가 실제 고객사에서 11g의 적용 사례를 접할 날을 기대해본다.

신고
Trackback 1 : Comment 0

Write a comment

티스토리 툴바