오라클 19c 인덱스 생성시 nologging 옵션 속도 차이 확인

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 인덱스 생성시 nologging 옵션 속도 차이 확인

오라클에서 인덱스 생성시 nologging 옵션을 사용할 수 있음

nologging 옵션을 사용하면 리두를 최대한 적게 생성할수 있는데 이때 속도차이를 확인해봄

 

 

샘플 유저 생성

1 2 3

SQL> create user imsi identified by imsi account unlock default tablespace users quota unlimited on users; grant resource, connect to imsi;

 

 

샘플 테이블 생성

1 2 3 4 5 6 7 8 9 10 11 12

SQL> conn imsi/imsi drop table sample_t purge; create table sample_t  (id1 number, id2 number, name varchar2(10),   date1 varchar2(8), date2 varchar2(8), date3 date,  phone varchar2(13), price number, qty number,  test1 number,  test2 varchar2(5), test3 varchar2(4) ) nologging;   Table created.

 

 

샘플 데이터 삽입

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SQL> DECLARE TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER; w_ins tbl_ins; BEGIN FOR d IN 1..100 LOOP FOR i IN 1..100000 LOOP     w_ins(i).id1   := i;    w_ins(i).id2   := i||ceil(dbms_random.value(1, 10000000));    w_ins(i).name  := dbms_random.string('x',5);    w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');    w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');    w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');    w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));    w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;    w_ins(i).qty   := ceil(dbms_random.value(1, 10));    w_ins(i).test1 := 1234;    w_ins(i).test2 := 'SQLP';    w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999)); END LOOP; FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);    COMMIT; END LOOP; END; /   PL/SQL procedure successfully completed.

 

 

리두 발생량 확인을 위해 세션 종료 후 재접속 후 테스트

 

 

sqlplus timing 옵션 on

1	SQL> set timing on

 

 

nologging 미사용 인덱스 생성

1 2 3 4 5

SQL> create index sample_t_ix01 on sample_t(id1, id2, name, date1, test1, test2, test3, qty, price, phone, date2, date3) online;   Index created.   Elapsed: 00:00:49.22

49.22초 소요됨

 

 

리두 생성량 확인

1 2 3 4 5 6 7 8 9

SQL> select round(vm.value/1024/1024, 2) "REDO_MB" from v$statname vs, v$mystat vm where vs.statistic# = vm.statistic# and vs.name = 'redo size';      REDO_MB ----------     995.53

redo가 약 1gb정도 발생함

 

 

리두 발생량 확인을 위해 세션 종료 후 재접속 후 테스트

 

 

sqlplus timing 옵션 on

1	SQL> set timing on

 

 

기존 인덱스 삭제

1 2 3

SQL> drop index sample_t_ix01;   Index dropped.

 

 

nologging 사용 인덱스 생성

1 2 3 4 5

SQL> create index sample_t_ix01 on sample_t(id1, id2, name, date1, test1, test2, test3, qty, price, phone, date2, date3) nologging online;   Index created.   Elapsed: 00:00:41.54

41.54초 소요됨

 

 

리두 생성량 확인

1 2 3 4 5 6 7 8 9

SQL> select round(vm.value/1024/1024, 2) "REDO_MB" from v$statname vs, v$mystat vm where vs.statistic# = vm.statistic# and vs.name = 'redo size';      REDO_MB ----------       2.49

redo가 약 2mb정도 발생함

 

 

작업 후 테이블 및 인덱스 logging 처리

1 2	SQL> alter table SAMPLE_T logging; SQL> alter index SAMPLE_T_IX01 logging;

 

 

결론 :
nologging 미사용 시:
- 인덱스 생성 시간: 약 49.22초
- 리두 로그 발생량: 약 995.53MB
nologging 사용 시:
- 인덱스 생성 시간: 약 41.54초
- 리두 로그 발생량: 약 2.49MB
nologging 옵션을 사용한 경우 테스트에선 인덱스 생성 속도가 약간 향상됨
그리고 리두 로그 발생량이 매우 감소함을 확인할 수 있음(실제 업무에선 테이블 크기도 더 크기 때문에 시간을 더 절약하고 리두 발생량을 더 줄일수있을것으로 예상됨)
하지만 nologging 옵션을 사용할 때 추후 장애 발생 및 복구시 문제가 발생할수 있기때문에
작업 후 logging으로 변경해주고 백업을 해줘야함

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-72D72713-0EF5-4BDD-813C-BFF63AE4F94F
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE