오라클 19c 인덱스 nologging 사용시 append insert 속도 차이 확인

OS 환경 : Oracle Linux 8.1 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 인덱스 nologging 사용시 append insert 속도 차이 확인

본문에서는 인덱스를 logging 또는 nologging 옵션을 사용해 생성하고
itas와 append 힌트로 대량 데이터를 삽입해보면서 속도 및 redo 발생량을 측정해봄

 

 

지난 테스트 참고 : 오라클 19c 인덱스 생성시 nologging 옵션 속도 차이 확인 ( https://positivemh.tistory.com/1010 )

 

 

테스트
샘플 유저 생성
참고 : 오라클 19c 테스트 시 사용하는 imsi 유저 생성 쿼리 ( https://positivemh.tistory.com/1017 )

 

 

샘플 테이블 생성

1 2 3 4 5 6 7 8 9 10 11

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) );   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.

 

 

샘플테이블2 구조만 생성

1 2 3 4 5

SQL>  drop table sample_t2 purge; create table sample_t2 as select * from sample_t where 1=2;   Table created.

 

 

샘플테이블2 logging 인덱스 생성

1 2 3

SQL> create index sample_t2_ix01 on sample_t2(id1, id2, name, date1, test1, test2, test3, qty, price, phone, date2, date3) online logging;   Index created.

 

 

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

 

 

sqlplus timing 옵션 on

1	SQL> set timing on

 

 

샘플테이블2에 데이터 삽입

1 2 3 4 5

SQL> insert /*+ append */ into sample_t2 select * from sample_t;   10000000 rows created.   Elapsed: 00:01:02.78

1분 2초 소요됨

 

 

리두 생성량 확인

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 ----------    4599.37

redo가 약 4.6gb정도 발생함

 

 

테이블 truncate

1 2 3

SQL> truncate table sample_t2;   Table truncated.

 

 

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

 

 

인덱스 logging시 insert 속도 측정 결과 : isnert 시간이 1분 2초 소요되고 redo가 4.6gb 생성됨

 

 

샘플테이블2 logging 인덱스 삭제

1 2 3

SQL> drop index sample_t2_ix01;   Index created.

 

 

샘플테이블2 nologging 인덱스 생성

1 2 3

SQL> create index sample_t2_ix02 on sample_t2(id1, id2, name, date1, test1, test2, test3, qty, price, phone, date2, date3) online nologging;   Index created.

 

 

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

 

 

sqlplus timing 옵션 on

1	SQL> set timing on

 

 

샘플테이블2에 데이터 삽입

1 2 3 4 5

SQL> insert /*+ append */ into sample_t2 select * from sample_t;   10000000 rows created.   Elapsed: 00:01:02.85

1분 2초 소요됨

 

 

리두 생성량 확인

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 ----------    4606.74

redo가 약 4.6gb정도 발생함

 

 

인덱스 nologging시 insert 속도 측정 결과 : isnert 시간이 1분 2초 소요되고 redo가 4.6gb 생성됨

 

 

결론 :
인덱스 nologging 미사용 시:
- insert 시간: 약 1분 2초
- 리두 로그 발생량: 약 4599.37MB
인덱스 nologging 사용 시:
- insert 시간: 약 1분 2초
- 리두 로그 발생량: 약 4606.74MB
두 경우 모두 시간도 동일하고 redo 생성량도 거의 동일함
지난 테스트와는 다르게 인덱스가 존재하는 상태에서 데이터 삽입시에는 성능차이가 나지 않았음

 

 

오라클 docs의 managing-indexes를 보면 nologging에 대해서는
21.2.10 Consider Creating Indexes with NOLOGGING 라는 파트만 존재함
이는 인덱스 생성시에만 nologging으로 성능 향상이 된다는걸 의미함
그래서 이미 만들어진 인덱스가 nologging이라고 해도 데이터 삽입 시간은 차이가 나지 않는것임
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-72D72713-0EF5-4BDD-813C-BFF63AE4F94F

 

 

 

참조 : 오라클 19c 인덱스 생성시 nologging 옵션 속도 차이 확인 ( https://positivemh.tistory.com/1010 )

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
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-72D72713-0EF5-4BDD-813C-BFF63AE4F94F