redo와 archive log mode

/*
 테스트시 생성된 redo size를 조회 할 수있도록 view룰 생성한다.
*/
create or replace view redo_size
as
select b.name, a.value
from   v$mystat a, v$statname b
where  a.statistic# = b.statistic#
and    b.name = 'redo size';

/*
 첫번째 테스트를 위하여 nologging옵션으로 테이블 생성한다.
*/
create table t ( x int, y char(2000), z date )
nologging;

/*
 일반적인 모드로 10건의 insert를 수행했을 때와 direct(append)모드로 10건의 insert를 수행했을 때와 redo size 비교한다.
*/
declare
  l_redo_size_a number;
  l_redo_size_b number;
begin
  select value into l_redo_size_b from redo_size;
 
  insert into t
  select 1, user, sysdate
  from   all_objects
  where  rownum <= 10;
  commit;
  select value into l_redo_size_a from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_a - l_redo_size_b) || ' bytes');
 
  insert /*+ append */ into t
  select 1, user, sysdate
  from   all_objects
  where  rownum <= 10;
  commit;
 
  select value into l_redo_size_b from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_b - l_redo_size_a) || ' bytes');
end;
/

/*
  위의 테스트에 대한 결과는 아래와 같다.
 - generated redo size 22868 bytes  --일반적인 insert
 - generated redo size 3148 bytes   --direct insert
  결과를 보았을 때 nonogging 모드의 테이블에서 append 방식으로 insert했을 때 redo log가 최소화되며
  nologging 모드의 테이블이라도 append 방식으로 insert하지 않으면 redo log는 감소하지 않는다.
*/

/*
 이번에는 테이블을 logging옵션으로 변경하고 테스트 해보자
*/
alter table t logging;

/*
 그리고 일반적인 모드로 10건의 insert를 수행했을 때와 direct모드로 10건의 insert를 수행했을 때와 redo size 비교해 보자
*/
declare
  l_redo_size_a number;
  l_redo_size_b number;
begin
  select value into l_redo_size_b from redo_size;
 
  insert into t
  select 1, user, sysdate
  from   all_objects
  where  rownum <= 10;
  commit;
  select value into l_redo_size_a from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_a - l_redo_size_b) || ' bytes');
 
  insert /*+ append */ into t
  select 1, user, sysdate
  from   all_objects
  where  rownum <= 10;
  commit;
 
  select value into l_redo_size_b from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_b - l_redo_size_a) || ' bytes');
end;
/

/*
  위의 테스트에 대한 결과는 아래와 같다.
   - generated redo size 22660 bytes  --일반적인 insert
   - generated redo size 3664 bytes   --direct insert
  결과를 보았을 때 logging 모드의 테이블이라도 append 방식으로 insert했을 때 redo log가 최소화되며
  logging 모드의 테이블일지이라도 append 방식으로 insert을 하면 redo log는 감소하지 않는다.
*/

/*
  다시 한번 아래와 같이 테이블을 nologging옵션으로 변경하여 이번에는 update를 테스트 해보자
*/
alter table t nologging;

declare
  l_redo_size_a number;
  l_redo_size_b number;
begin
  select value into l_redo_size_b from redo_size;
 
  update t set x = 2, y = user, z = sysdate
  where  rownum <= 10;
  commit;
  select value into l_redo_size_a from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_a - l_redo_size_b) || ' bytes');
end;
/

/*
  결과는 아래와 같다.
    - generated redo size 43308 bytes
  테이블이 nologging 모드라도 insert 때 보다 2배가량의 redo log가 생성되었다.
*/

/*
 이번에는 다시 테이블을 logging옵션으로 변경한 후 update를 테스트 해보자
*/
alter table t logging;

declare
  l_redo_size_a number;
  l_redo_size_b number;
begin
  select value into l_redo_size_b from redo_size;
 
  update t set x = 2, y = user, z = sysdate
  where  rownum <= 10;
  commit;
  select value into l_redo_size_a from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_a - l_redo_size_b) || ' bytes');
end;
/

/*
  아래는 테스트 결과이다.
    - generated redo size 43404 bytes
  당연하겠지만, nologging 모드일때와 거의 동일한 redo log가 생성되었다.
*/

/*
 다시 테이블을 nologging옵션으로 변경한 후 delete를 테스트 해보자
*/
alter table t nologging;

declare
  l_redo_size_a number;
  l_redo_size_b number;
begin
  select value into l_redo_size_b from redo_size;
 
  delete t
  where  rownum <= 10;
  commit;
  select value into l_redo_size_a from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_a - l_redo_size_b) || ' bytes');
end;
/

/*
  아래는 테스트 결과이다.
    - generated redo size 23644 bytes
  일반적인 insert 때 만큼의 reod log 가 생성되었다.
*/

/*
  다시 테이블을 logging옵션으로 변경한 후 delete를 테스트 해보자
*/
alter table t logging;

declare
  l_redo_size_a number;
  l_redo_size_b number;
begin
  select value into l_redo_size_b from redo_size;
 
  delete t
  where  rownum <= 10;
  commit;
  select value into l_redo_size_a from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_a - l_redo_size_b) || ' bytes');
end;
/
/*
  아래는 테스트 결과이다.
    - generated redo size 23544 bytes
  역시 nologging 모드일 때와 거의 동일한 만큼의 redo log가 생성되었다.
*/

drop table t;

/*
  insert, update, delete 에 대한 테스트는 여기서 마무리 하고 CTAS에서 redo log의 생성량을 확인해 보자.
*/
/*
  CTAS방식으로 테이블을 생성하되 loging 옵션으로 생성해 보자.
*/
declare
  l_redo_size_a number;
  l_redo_size_b number;
begin
  select value into l_redo_size_b from redo_size;
 
  execute immediate 'create table t as select 100 as x, 200 as y, 300 as z from all_objects where rownum <= 10';
  select value into l_redo_size_a from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_a - l_redo_size_b) || ' bytes');
end;
/

/*
  아래와 같이 redo log가 생성되었다.
    - generated redo size 10744 bytes
*/

drop table t;

/*
 이번에는 CTAS방식으로 테이블을 생성하되 nologing 옵션으로 생성해 보자.
*/
declare
  l_redo_size_a number;
  l_redo_size_b number;
begin
  select value into l_redo_size_b from redo_size;
 
  execute immediate 'create table t nologging as select 100 as x, 200 as y, 300 as z from all_objects where rownum <= 10';
  select value into l_redo_size_a from redo_size;
  dbms_output.put_line('generated redo size ' || (l_redo_size_a - l_redo_size_b) || ' bytes');
end;
/

/*
  위의 CTAS - logging 모드 생성과 별 차이가 없다.
    - generated redo size 10744 bytes
  그럼 CTAS로 테이블을 생성시에는 nologging 모드와 logging 모드가 차이가 없는것인가?  
  그렇다. 차이가 없다. 그러나, 맨처음 테스트한 일반 insert방식과 redo log의 크기를 비교해 보자.
  redo log 생성량이 반으로 줄었다. 이는 CTAS로 테이블을 생성할 때는 테이블을 logging 으로 하던 nologging으로 하던 항상 redo log를 최소화 한다는 것이다.
  그럼에도 append 방식으로 insert 할 때 보다는 redo log 량이 큰 이유는 테이블을 create 할 때 내부 dictionary table에 대하여 insert 또는 update가 발생하기 때문이다.
  이는 결과적으로 CTAS 방식으로 테이블을 생성한 후 백업본이 없는 상태에서 시스템이 crash 되었다면 해당 테이블은 복구가 불가능하다.
  CTAS 방식으로 테이블을 생성한 후에는 항상 백업을 하는 습관을 들이기를.....(중요한 데이터라 생각이 되면)
*/


/*
  한가지 더 select가 redo를 발생시킬까? 아래 테스트 결과 select는 redo를 발생시키지 않는다.
*/
set autotrace traceonly

select * from all_objects;

/*
0  redo size
*/

select * from all_objects order by 1, 2, 3, 4, 5, 6;

/*
0  redo size
*/

/*
 테스트 결과 logging이건 nologging이건 insert의 direct path에서만 redo log가 최소화되며,
 update와 delete는 redo log를 최소화 할 수 있는 방법이 없다.
 또한 insert와 delete는 거의 동일한 양의 redo log를 발생 시키지만, update는 insert와 delete의 거의 두배에 해당하는 redo log를 발생시킨다.
   -> update가 insert, delete에 비해 거의 두배에 해당하는 redo log를 발생시키는 이유는 변경된 데이터를 저장하고, 변경전의 데이터를 undo segment에 보관해야 하기 때문이다.
      (undo segment도 redo log 생성의 대상이다.)
 또한 redo log는 archive log mode 방식과도 관련이 있으며, 아래에 redo log 와 archive log mode의 관계에 대하여 정리하였다.

 Table Mode    Insert Mode     ArchiveLog mode      result
 -----------   -------------   -----------------    ----------
 LOGGING       APPEND          ARCHIVE LOG          redo generated
 NOLOGGING     APPEND          ARCHIVE LOG          no redo
 LOGGING       no append       ARCHIVE LOG          redo generated
 NOLOGGING     no append       ARCHIVE LOG          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
*/