append 힌트의 효용성

 

append 힌트의 효용성에 대한 논란이 잠시 있어 막간을 이용, 확인차 테스트해봤습니다..

 

아래 링크를 차례대로 읽어보세요...

 

i) NOLOGGING이 아닌 테이블에는 INSERT /*+ APPEND */ 효과가 없다?
   : "APPEND 힌트의 진실과 거짓" --> http://blog.naver.com/addibuddi/22476554

 

ii) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869

위 사이트에서 Kyte는 다음과 같이 정리함..

 

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

 

이제 테스트를 시작합니다..

 

############################################
### 1. logging 테이블에 conventional INSERT
############################################

SQL> conn scott
Enter password:
Connected.

 

SQL> create table tab3 logging
as select * from all_objects
where 1=2;

Table created.

SQL> insert into tab3
select * from all_objects
;

36013 rows created.

 

SQL> commit;

Commit complete.

 

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ;

 

       SID STATISTIC#      VALUE
---------- ---------- ----------
       282        133       3420   <====

SQL> explain plan for insert into tab3  
  2  select * from tab3 ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1925526863

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |      | 37070 |  4633K|   129  (14)| 00:00:02 |    <====
|   1 |  TABLE ACCESS FULL| TAB3 | 37070 |  4633K|   129  (14)| 00:00:02 |
--------------------------------------------------------------------------

#######################################
### 2. logging 테이블에 Direct Insert
#######################################

SQL> conn scott
Enter password:
Connected.

SQL> create table tab1 logging
as select * from all_objects
where 1=2;

Table created.

 

SQL> insert /*+  append */ into tab1
select * from all_objects
; 

36011 rows created.

 

SQL> commit;

Commit complete.

 

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ; 

 

       SID STATISTIC#      VALUE
---------- ---------- ----------
       240        133        866   <====

 

SQL> explain plan for insert /*+  append */ into tab1
  2  select * from tab1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1481117511

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |      | 32454 |  4056K|   126  (12)| 00:00:02 |
|   1 |  LOAD AS SELECT    | TAB1 |       |       |            |          |    <====
|   2 |   TABLE ACCESS FULL| TAB1 | 32454 |  4056K|   126  (12)| 00:00:02 |
---------------------------------------------------------------------------

#######################################
### 3. nologging 테이블에 Direct Insert
#######################################

SQL> conn scott
Enter password:
Connected.

SQL> create table tab2 nologging
as select * from all_objects
where 1=2;

Table created.

 

SQL> insert /*+  append */ into tab2
select * from all_objects
;

36012 rows created.

 

SQL> commit;

Commit complete.

 

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ; 

 

       SID STATISTIC#      VALUE
---------- ---------- ----------
       118        133        363  <====

SQL> explain plan for insert /*+  append */ into tab2
  2  select * from tab2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4264119061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |      | 36080 |  4510K|   128  (14)| 00:00:02 |
|   1 |  LOAD AS SELECT    | TAB2 |       |       |            |          |    <====
|   2 |   TABLE ACCESS FULL| TAB2 | 36080 |  4510K|   128  (14)| 00:00:02 |
---------------------------------------------------------------------------

 

** 테스트 결과, logging 속성의 테이블이더라도 APPEND 힌트에 의한 REDO감소 효과는 큰것으로 나타남.

 

3420  > 866 > 363

 

이정도의 차이라면 Production환경에서는 recovery를 포기하고 테이블까지 nologging으로 해야 한다는 우를 범하지 않고서라도 충분히 append 힌트의 효용성을 누릴수 있을 것이다.

[출처] http://blog.naver.com/dbmedic?Redirect=Log&logNo=50040322413