백문이 불여일런이라 먼저 실습을 해보지요.

 

nologging 모드로 Tablespace를 만듭니다.

 

CREATE TABLESPACE nologging_test DATAFILE
  '/home/ora9i/oradata/ORA9I/nologging_test01.dbf' SIZE 30M AUTOEXTEND OFF
NOLOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

제대로 되었는지 확인... LOGGING 이 "NOLOGGING"인지 확인

SELECT tablespace_name, LOGGING, force_logging
  FROM dba_tablespaces

where tablespace_name = 'NOLOGGING_TEST'

 

위의 테이블스페이스에 테이블 생성

create table nologging_test_table (col1 varchar2(30))

tablespace nologging_test

 

user_tables를 뒤져서 방금 생성한 테이블의 logging 모드확인

select table_name, logging from user_tables where table_name = 'NOLOGGING_TEST_TABLE';

결과 : NO

 

PL/SQL에서 1만건을 insert 하고 생성되는 리두 로그 사이즈 측정

 

 

DECLARE
   before_log_size   NUMBER (10);
   result_log_size   NUMBER (10);
BEGIN
   SELECT TO_NUMBER (VALUE)
     INTO before_log_size
     FROM v$mystat s, v$statname n
    WHERE n.statistic# = s.statistic# AND n.NAME = 'redo size';

   FOR i IN 1 .. 10000
   LOOP
      INSERT INTO nologging_test_table
           VALUES (i);
   END LOOP;

   COMMIT;

   SELECT TO_NUMBER (VALUE) - before_log_size
     INTO result_log_size
     FROM v$mystat s, v$statname n
    WHERE n.statistic# = s.statistic# AND n.NAME = 'redo size';

   DBMS_OUTPUT.put_line ('Generated Redo size : ' || result_log_size);
END;

 

결과 : Generated Redo size : 2333532

 

위의 결과를 보면 해당 테이블이 nologging 임에도 불구하고 2.3M에 육박하는 리두 로그가 생성되었다.

 

 

 

 

항상 오해하기 쉬운 것이 nologging 모드로 테이블을 생성하면 해당 테이블에 대한 DML 작업시 리두 로그가 나오지 않는다고 착각하는 것이다.

 

nologging 은 특정 오퍼레이션에 한해서만 리두 로그 생성을 억제하는 기능이지 일반적인 DML(Insert, delete, update)에 대해서는 작동하지 않는다.

 

특정 오퍼레이션이란 SQL*Loader를 통한 직접경로모드(Direct Path load) 혹은 Insert /*+ append */ 를 이용한 Direct path insert 등만 해당된다.

 

 

 

테이블스페이스를 nologging 으로 생성하는 것은 해당 테이블스페이스에 생성되는 객체들이 별도로 지정하지 않으면 기본적으로 nologging 모드로 생성된다는 의미이다.

 

즉, 위의 nologging 테이블스페이스에 테이블생성시 logging 모드로 생성하면 객체 생성시 logging 옵션이 더 우선한다.

 

create table nologging_test_table2 (col1 varchar2(30))

logging tablespace nologging_test;

 

select logging from user_tables

where table_name = 'NOLOGGING_TEST_TABLE2';

 

 

 

마지막으로 위에서 언급한 특정 오퍼레이션인 /*+ append */ 힌트를 사용한 Direct mod insert를 해보고 생성되는 리두의 양을 측정해보자.

 

DECLARE
   before_log_size   NUMBER (10);
   result_log_size   NUMBER (10);
BEGIN
   SELECT TO_NUMBER (VALUE)
     INTO before_log_size
     FROM v$mystat s, v$statname n
    WHERE n.statistic# = s.statistic# AND n.NAME = 'redo size';

   INSERT      /*+ append */INTO nologging_test_table
      SELECT object_name
        FROM all_objects;

   COMMIT;

   SELECT TO_NUMBER (VALUE) - before_log_size
     INTO result_log_size
     FROM v$mystat s, v$statname n
    WHERE n.statistic# = s.statistic# AND n.NAME = 'redo size';

   DBMS_OUTPUT.put_line ('Generated Redo size : ' || result_log_size);
END;

 

결과 : Generated Redo size : 2828

 

위의 결과를 보면 2.8K 정도의 리두만 생성된 것을 알 수 있다.

리두가 전혀 생성안될 수는 없다. 왜냐하면 데이터딕셔너리에 대한 수정등이 동반되므로 이에 대한 최소한의 리두는 나온다.

 

앞으로 리두가 안나오길 기대하면서 Tablespace나 혹은 Create table등 오브젝트 생성시 nologging 을 지정하지 않기를 바랍니다.

 

 

주의: 가끔 현업에 나가보면 개발자들이 다음과 같이 SQL을 작성하는 경우가 있다.

insert /*+ append */ into test values('1', '홍길동');

 

append 힌트는 insert ... select 구문등 대용량 인서트에서 사용 가능한 것이지 위와 같이 1건씩 insert 하는 일반적인 구문에서는 전혀 효과가 없다.

따라서 일반적인 인서트 구문에 /*+ append */ 힌트를 박는 것은  자신의 무지를 과시하는 것 밖에는 안된다.