Append 힌트를 사용하려면 테이블을 NOLOGGING으로 설정해야 한다는 논란

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 힌트의 효용성을 누릴수 있을 것이다.

답글 ->

속도 빠른건 좋죠.. 하지만 스토리지 용량을 더 많이 잡아 먹는다는 단점 정도가 있겠네요~

핵심은 배치작업 수행시 주로 사용되는 append 힌트가 redo로그를 감소시켜서 결국 log switching관련 대기이벤트를 감소시키고, 전체적인 야간배치작업 성능을 향상시킨다는 점입니다. 
HWM 이후에 append시킨다는 점은 물론 delete가 빈번한 테이블일 경우 공간활용의 비효율성으로 연결될수 있으므로 그러한 예외적인 경우에는 table shrink등의 리오그 대책이 뒤따라야 할것입니다.. 
하지만 대부분의 대형사이트라면, 요즘은 (저렴해진)스토리지에 투자하는 비용보다는 성능개선이 우선시되니까요~

 

[출처] http://blog.naver.com/lalaboy/40051083359