오라클 APPEND 힌트

출처 : http://blog.naver.com/PostView.nhn?blogId=j0ngmali&logNo=47601863&parentCategoryNo=&categoryNo=&viewDate=&isShowPopularPosts=false&from=postView

INSERT문에 APPEND 힌트가 붙어있는 것을 종종 보았을 것입니다. 그렇다면 이 APPEND 가 어떻게 동작하면 통상의 INSERT문과 어떻게 다른지 알아보겠습니다.

 INSERT … SELECT문에 APPEND 힌트를 붙인 것을 다이렉트 로드 인서트라고 부릅니다.

그중에서도 패러렐화한 것을 패러렐 다이렉트 로드 인서트, 시리얼 실행의 것을 시리얼 다이렉트 로드 인서트라고 나누어 부르고 있습니다.
이러한 INSERT문은 통상의 INSERT문과는 여러가지 면에서 움직임이 다르기 때문에 주의가 필요합니다.

■ 실행예


 

여기에서는 예로서 같은 구조의 테이블 EMP1, EMP2 가 있고 EMP1의 전데이터를 EMP2에 추가하는 경우를 생각해 보겠습니다.

– 통상의 INSERT…SELECT문:
 SQL> insert into emp2 select * from emp1;
 SQL> commit;

– 시리얼 다이렉트 로드 인서트:
 SQL> insert /*+ append */ into emp2 select * from emp1;
 SQL> commit;

– 패러렐 다이렉트 로드 인서트:
 SQL> alter session enable parallel dml;                       — 1)
 SQL> insert /*+ append parallel(emp2,3) */ into emp2   — 2)
          select * from emp1;
 SQL> commit;

==> 1)의 SQL문에 의해 이 세션에서 패러렐 DML를 실시하는 것을 가능하게 합니다.
      2)테이블 EMP2 에 이미 패러렐도(degree)의 지정이 되어 있으면 PARALLEL 힌트는

        생략 할 수 있습니다.
        또한 테이블 EMP1에 degree를 설정하므로써 ( parallel 힌트를 붙임으로써)

        INSERT문과 SELECT문의 양쪽 모두를 패러렐화할 수도 있습니다.

■ 다이렉트 로드 인서트의 메리트, 디메리트


메리트:
 

  통상의 INSERT문과는 달리 버퍼 캐쉬를 사용하지 않고 다이렉트로 데이터 파일에의 기입을

  실시하므로 INSERT에 걸리는 시간이 단축된다.
  패러렐도를 설정해 있는 경우에는 기입 처리가 병렬로 동작하므로 한층 INSERT에 걸리는 시간이

  단축된다.

디메리트:
 

   테이블에 대한 배타 락을 걸리므로 INSERT가 종료할 때까지는 다른 트랜잭션(transaction)

  에서 INSERT/UPDATE/DELETE문은 동시에 실행할 수 없다.
  비파티션 테이블에 대한 다이렉트 로드 인서트는 그 테이블의 기존의 빈영역에 INSERT 하는 것이

  아니라 그 테이블의 High Water Mark이후에 새롭게 영역을 할당해 INSERT 한다. (새롭게 할당한

  영역은 INSERT중에는 템포러리 세그먼트로 불러서 작업후 INSERT 종료후에 기존의 테이블에 머지

  된다) 그로인해 통상의 INSERT문보다 많은 영역을 사용하는 일이 있다.
  영역의 사용에 대한 자세한 것은, 메뉴얼 「Oracle8i 개요 vol.2」p.25-8 에 있습니다.

■  다이렉트 로드 인서트로 놓치는 락


 

다이렉트 로드 인서트에서는 INSERT 대상의 테이블에 배타 락 (TM락을 모드=X)이 걸리므로
다른 트랜잭션(transaction)에 의한 INSERT/UPDATE/DELETE문은 동시에 실행할 수 없습니다.

다이렉트 로드 인서트를 실시하는 프로세스


시리얼 다이렉트 로드 인서트의 경우에는 그 insert문을 실행한 서버 프로세스가 실행합니다.
패러렐 다이렉트 로드 인서트의 경우에는 그 insert문을 실행한 서버 프로세스가 코디네이타가 되어

지정된 패러렐도에 맞은 수의 패러렐 슬레이브(p000 프로세스등)가 insert문(select 대상에도 패러렐도가 붙어 있으면 select문도)를 실행합니다.

예를 들면, EMP1(degree=3), EMP2(degree=3)에 대해 이하를 실행했을 경우

   SQL> alter session enable parallel dml;
   SQL> insert /*+ append */ into emp2
            select * from emp1;
   SQL> commit;

EMP2에 대한 INSERT문은 패러렐 슬레이브가 3개가 실행되고 , EMP1에 대한 SELECT문에 대해서도 패러렐 슬레이브가 3개가 실행됩니다.

다이렉트 로드 인서트의 제한


다이렉트 로드 인서트에는 몇개의 제한 사항이 있습니다.
각 버젼에 의해서 제한 사항이 다르기 때문에, 제한 사항에 대해서는 각 버젼의 메뉴얼을 참조하세요

Can INSERT’s be tuned?

Overview

The CREATE TABLEAS SELECT
statement (CTAS) is a powerful tool for manipulating large sets of
data. CTAS provides a mechanism for efficiently executing a SQL query
and storing the results of that query in a new database table.

The INSERT /*+APPEND*/ … AS SELECT statement offers the same capabilities with existing database tables.

APPEND into Tables

By using the APPEND hint, you ensure that Oracle
always grabs “fresh” data blocks by raising the high-water-mark for the
table. If you are doing parallel insert DML, the Append mode is the
default and you don’t need to specify an APPEND hint.

When you use the APPEND hint for INSERT, data is
simply appended to a table above the HWM which has the effect of not
creating UNDO. Existing free space in blocks is not used.

High Water Mark

The high water mark is the boundary between used
and unused space in a segment. As requests for new free blocks that
cannot be satisfied by existing free lists are received, the block to
which the high water mark points becomes a used block, and the high
water mark is advanced to the next block. In other words, the segment
space to the left of the high water mark is used, and the space to the
right of it is unused.

Suggest Way for fast INSERTs

  • Mark indexes unuasble
  • Disable primary key
  • Alter table nologging
  • Do an insert /*+ append */ into table (select ..)
  • Enable primary key
  • Rebuild indexes nologging

Example

1.  First create a Big Table for this Test

create table bigtab
as
select rownum id, a.*
  from all_objects a
 where 1=0;

alter table bigtab nologging;

declare
    l_cnt number;
    l_rows number := 1000000;
begin
    insert /*+ append */
    into bigtab
    select rownum, a.*
      from all_objects a;

    l_cnt := sql%rowcount;

    commit;

    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into bigtab
        select rownum+l_cnt,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from bigtab
         where rownum <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/

Now we have a Table with 1’000’000 Rows. Now
delete some Rows, to force Oracle to refill this freespace using the
FREELISTS in a normal INSERT. However in APPEND Mode the FREELISTS are
not used and the freespace are not reoccupied.

DELETE FROM bigtab WHERE id between 1000 and 2500;
DELETE FROM bigtab WHERE id between 3500 and 6500;
DELETE FROM bigtab WHERE id between 15000 and 20000;
DELETE FROM bigtab WHERE id between 350000 and 370000;
COMMIT;

CREATE TABLE insert_test AS SELECT * FROM bigtab;

2.  Test with normal Insert

SQL> CREATE TABLE insert_test AS SELECT * FROM bigtab;
Table created.

SQL> INSERT INTO insert_test SELECT rownum, a.* FROM all_objects a;
49483 rows created.

Elapsed: 00:00:03.92   <==================== !

SQL> DROP TABLE insert_test;
Table dropped.


3.  Test with APPEND Hint and NOLOGGING

SQL> CREATE TABLE insert_test AS SELECT * FROM bigtab;
Table created.

SQL> ALTER TABLE insert_test NOLOGGING;
Table altered.

SQL> INSERT /*+ append */ INTO insert_test SELECT rownum, a.* FROM all_objects a;
49483 rows created.

Elapsed: 00:00:02.54    <==================== !


As you can see, only to insert about 50’000 Rows,
the APPEND Insert is much faster, due the free space in the Oracle
blocks are not refilled, the Rows are appended and the Highwater Mark is
moved
.

Table Mode    Insert Mode     ArchiveLog mode      result
———–   ————-   —————–    ———-
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       “”                   redo generated
NOLOGGING     no append       “”                   redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated
NOLOGGING     no append       noarchive log mode   redo generated

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다