오라클 19c insert append, append_values 힌트 extent 테스트

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c insert append, append_values 힌트 extent 테스트

oracle 19c 환경에서 append 힌트와 append_values 힌트 사용시와 미사용시 extent 사용량을 비교함
append 힌트는 ctas(create table as select ~)나 itas(insert into select ~) 구문에서 사용가능한 힌트임
append 힌트 사용시 위와 같은 구문에서 direct path i/o 기능을 사용할수 있고 
insert 시 freelist 를 조회하지 않고 hwm 뒷쪽에 insert를하기 때문에 일반 insert 에 비해 속도가 빠름

 

append 힌트 사용시 장점
1. Insert 할 때 Freelist 조회 안 함
2. Insert 할 블록을 찾기 위한 버퍼캐시 탐색 없음
3. 버퍼캐시에 적재하지 않고 데이터파일에 직접 Write
4. Undo 발생량 최소화
5. nologging 모드에서 사용한다면, Redo 로그 최소화 

(https://cafe.naver.com/dbian/109)

 

append 힌트 사용시 단점
1. Exclusive 모드 TM Lock --> 동시 DML 불가
2. conventional insert가 없는 테이블일 경우, HWM 아래 쪽에 빈공간 양산 
3. nologging 모드로 insert 한 경우, 복구 불가

하지만 append 힌트는 일반 insert 문(insert into ~ values ~)에서는 사용할수 없음(힌트가 무시됨)
11gR2에서 새로운 힌트가 나왔는데 append_values 라는 힌트임
이 힌트를 사용하면 insert values 절에서도 append 힌트와 같이 direct path i/o 기능을 사용할수 있음

 

 

테스트는 6가지 경우를 확인해봄
1. insert select 절에 append 미사용시 extent 체크
2. insert select 절에 append 사용시 extent 체크
3. 일반 insert values 절에 append_values 미사용시 extent 체크
4. 일반 insert values 절에 append_values 사용시 extent 체크

5. forall 구문이 아닌 일반 for 문만 사용해서 다시 테스트(append_valeus 미사용시 extent 체크)

6. forall 구문이 아닌 일반 for 문만 사용해서 다시 테스트(append_valeus 사용시 extent 체크)

 

 

0. 테스트 전 insert select 데이터용 테이블 생성

데이터용 테이블 생성
SQL>
create table extent_test1_dat (cola varchar2(20), colb number, colc number,
cold varchar2(30), cole varchar2(30), colf varchar2(30),
colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30));
 
데이터 삽입
SQL>
set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF EXTENT_TEST1_DAT%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..10000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=999;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII';
   w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA';
END LOOP;
   FORALL i in 1..10000 INSERT INTO EXTENT_TEST1_DAT VALUES w_ins(i);
   COMMIT;
END;
/
 
count 확인
SQL> select count(*) from extent_test1_dat;
 
  COUNT(*)
----------
   10000
 
용량 확인
SQL>
set lines 200
col owner for a10
col segment_name for a20
select segment_name, sum(bytes)/1024/1024 as MB
from dba_segments
where segment_name='EXTENT_TEST1_DAT'
GROUP BY segment_name;
 
SEGMENT_NAME             MB
-------------------- ----------
EXTENT_TEST1_DAT    2
 
extent 확인
SQL> 
select count (*) from dba_extents
where segment_name = 'EXTENT_TEST1_DAT';
 
  COUNT(*)
----------
        17

 

 

1. insert select 절에 append 미사용시 extent 체크

샘플 테이블 생성

SQL> create table extent_test1 as select * from extent_test1_dat where 1=2;

 

 

데이터 삽입 및 commit

SQL>
insert into extent_test1 select * from extent_test1_dat;
commit;

 

 

extent 확인

SQL> 
select count (*) from dba_extents
where segment_name = 'EXTENT_TEST1';
 
  COUNT(*)
----------
        17

 

 

2. insert select 절에 append 사용시 extent 체크
샘플 테이블 생성

SQL> create table extent_test2 as select * from extent_test1_dat where 1=2;

 

 

데이터 삽입 및 commit

SQL>
insert /*+ append no_gather_optimizer_statistics */ into extent_test2 select * from extent_test1_dat;
commit;

 

 

extent 확인

SQL> 
select count (*) from dba_extents
where segment_name = 'EXTENT_TEST2';
 
  COUNT(*)
----------
        17

=> insert select 방식의 경우 append 힌트를 사용한 경우와 사용하지 않은 경우에 extent 차이가 없음

 

 

3. 일반 insert values 절에 append_values 미사용시 extent 체크
일반 insert values 절 단건은 extent 증가량 확인이 어려울것 같아 
forall문을 사용해서 테스트해봄

 

 

샘플 테이블 생성

SQL> create table INV_EXTENT_TEST1 (col1 number);

 

 

데이터 삽입 및 commit

SQL>
DECLARE
 TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 testlist NumList;
BEGIN
 FOR j IN 1..10000 LOOP
   testlist(j) := j;
 END LOOP;
 FOR k IN 1..1000 LOOP
   FORALL i IN 1..10000
     INSERT
     INTO INV_EXTENT_TEST1
     VALUES (testlist(i));
   commit;
 END LOOP;
END;
/

 

 

​extent 확인

SQL> 
select count (*) from dba_extents
where segment_name = 'INV_EXTENT_TEST1';
 
  COUNT(*)
----------
        86

 

 

​4. 일반 insert values 절에 append_valeus 사용시 extent 체크

샘플 테이블 생성

SQL> create table INV_EXTENT_TEST2 (col1 number);

 

 

데이터 삽입 및 commit

SQL>
DECLARE
 TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 testlist NumList;
BEGIN
 FOR j IN 1..10000 LOOP
   testlist(j) := j;
 END LOOP;
 FOR k IN 1..1000 LOOP
   FORALL i IN 1..10000
     INSERT /*+ append_values no_gather_optimizer_statistics */
     INTO INV_EXTENT_TEST2
     VALUES (testlist(i));
   commit;
 END LOOP;
END;
/

 

 

​extent 확인

SQL> 
select count (*) from dba_extents
where segment_name = 'INV_EXTENT_TEST2';
 
  COUNT(*)
----------
        87

=> 일반 insert values 절을 사용했지만 extent는 큰차이가 나지않음

테스트 시 for문에 값을 변경하면서 하면 extent가 1씩 차이나는 정도였음

위 쿼리는 벌크인서트이기 때문에 단건 insert 후 commit 하는 방식으로 테스트를 다시해봄

 

 

5. forall 구문이 아닌 일반 for 문만 사용해서 다시 테스트(append_valeus 미사용시 extent 체크)

샘플 테이블 재생성

SQL>
drop table INV_EXTENT_TEST1 purge;
drop table INV_EXTENT_TEST2 purge;
create table INV_EXTENT_TEST1 (col1 number);
create table INV_EXTENT_TEST2 (col1 number);

 

 

데이터 삽입 및 commit

SQL>
begin
for i in 1..10000 loop
insert into INV_EXTENT_TEST1 values (1);
commit;
end loop;
end;
/

 

 

​extent 확인

SQL> 
select count (*) from dba_extents
where segment_name = 'INV_EXTENT_TEST1';
 
  COUNT(*)
----------
         3

 

 

6. forall 구문이 아닌 일반 for 문만 사용해서 다시 테스트(append_valeus 사용시 extent 체크)

데이터 삽입 및 commit

SQL>
begin
for i in 1..10000 loop
insert /*+ append_values no_gather_optimizer_statistics */ into INV_EXTENT_TEST2 values (1);
commit;
end loop;
end;
/

 

 

​extent 확인

SQL> 
select count (*) from dba_extents
where segment_name = 'INV_EXTENT_TEST2';
 
  COUNT(*)
----------
        81

=> 단건 insert 후 commit 하는 방식으로 테스트를 다시해본 결과 extent 차이가 확연하게 남

일반 insert의 경우 10000건을 삽입하는데 extent를 3개 사용한 반면

append_values 힌트를 사용한 insert의 경우 extent를 81개나 사용함

 

 

참조 : 

https://positivemh.tistory.com/850

오라클 19c insert append, append_values 힌트 Direct path, 실행계획 테스트

https://positivemh.tistory.com/851

오라클 19c insert append, append_values 힌트 속도 비교 테스트

https://positivemh.tistory.com/852

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Comments.html#GUID-DD069661-D431-40F5-9303-DB8C1153D87D
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50901
https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL344
https://blogs.oracle.com/optimizer/post/space-management-and-oracle-direct-path-load
https://oracle-base.com/articles/11g/append-values-hint-11gr2
https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484 
http://www.dba-oracle.com/t_append_oracle.htm
https://hoing.io/archives/292
https://hrjeong.tistory.com/223
https://cafe.naver.com/dbian/109
https://cafe.naver.com/dbian/234
https://cafe.naver.com/dbian/232
https://cafe.naver.com/dbian/5804
https://www.dbmaestro.com/blog/database-devops/boost-insert-statements
https://www.bobbydurrettdba.com/2012/10/19/append_values-hint/