오라클(Oracle) - APPEND 와 APPEND_VALUES 힌트

Last Updated on 1월 4, 2021 by 태랑(정현호)


Oracle 11gR2 에서 APPEND_VALUES 힌트가  등장 하였고,
 정리를 하면  insert  values 절에서도 Direct Path I/O  로 처리되는 힌트이다.

오라클 10gR2 까지는 insert values 절에는 Direct Path I/O가 불가능 하였다
오라클 11gR1 에서는  _direct_path_insert_features   파라미터에 의해서  APPEND 힌트로도 insert values 구문에서도 Direct Path I/O 로 동작하였으며,11gR2 에서는 APPEND_VALUES 라는 이름의 힌트가 추가 되었다.

단건을 처리하는 insert values 절에 Direct Path I/O 의 필요성 보다는 아래와 같이 PL/SQL 내에서 FORALL 구문으로 대량의 INSERT 시 활용이 가능하다고 이해하면 될것 같다.

FORALL i IN 1..numrecords
INSERT /*+ APPEND_VALUES */ INTO orderdata
VALUES(ordernum(i), custid(i), orderdate(i),shipmode(i), paymentid(i));
COMMIT;


그런데 오라클 문서에서 아래와 같은 내용이 있다
(sql_elements006.htm#SQLRF50903)



APPEND_VALUES 힌트 설명에서는 다음과 같이

The APPEND_VALUES hint is only supported with the VALUES clause of the INSERT statement.
If you specify the APPEND_VALUES hint with the subquery syntax of the INSERT statement,
it is ignored and conventional insert will be used.
To use direct-path INSERT with a subquery, refer to "APPEND Hint".

APPEND 힌트 설명에서는 다음과 같이
The APPEND hint is only supported with the subquery syntax of the INSERT statement, not the VALUES clause.
If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used.
To use direct-path INSERT with the VALUES clause, refer to "APPEND_VALUES Hint".


결론은 같은 내용인데

insert values 구문에서는 오직 APPEND_VALUES 힌트 만 Direct Path I/O 가 가능 하고
 insert select(sub query) 구문에서는 APPEND 힌트만 Direct Path I/O 가 가능 하다 라는 내용이다.

그러므로 명시적으로 구분하여 문장에 맞게 사용하라는 의미인데 
실제로 운영중인 사이트 쿼리중 insert select(sub query) 의 문장에서 APPEND_VALUES 힌트로 수행중인 쿼리를 발견 하였고 PLAN 및 10046 트레이스를 생성해보니 Direct Path I/O 로 동작되는 것을 확인 할 수 있었다.

버전은 11.2.0.3 이고 sample 테이블의 생성하여 insert select(sub query) 절에서
APPEND 와 APPEND_VALUES 힌트를 10046 트레이스로 비교해보면 아래와 같이 동일하게 수행됨을 확인 할 수 있다.

insert /*+ APPEND_VALUES */ into monitor.test_jhh
select * from monitor.test_jhh2

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.025 0.062 0 63 0 0
Execute 1 0.004 0.119 0 7 16 3
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 0.029 0.182 0 70 16 3

Misses in library cache during parse : 1
Optimizer Goal : FIRST_ROWS
Parsing user : SYS (ID=0)

Rows Row Source Operation
------- -----------------------------------------------------------------------
0 LOAD AS SELECT (cr=7 pr=0 pw=1 time=118797 us)
3 TABLE ACCESS FULL TEST_JHH2 (cr=7 pr=0 pw=0 time=1170 us cost=3 size=26585 card=409)

Wait Event Name Count Wait(sec) Max Wait
-------------------------------------------------- ------- 
CSS initialization 1 0.053 0.053
Disk file operations I/O 2 0.001 0.000
library cache pin 2 0.001 0.000
direct path write 1 0.002 0.002
row cache lock 8 0.003 0.000
library cache lock 2 0.001 0.001
direct path sync 1 0.001 0.001
gc cr multi block request 1 0.000 0.000
SQL*Net message from client 1 1.757 1.757
CSS operation: action 1 0.002 0.002
enq: TM - contention 1 0.000 0.000
SQL*Net message to client 1 0.000 0.000
gc current block 2-way 4 0.001 0.000
gc cr block 2-way 2 0.001 0.000
gc current grant busy 1 0.000 0.000
-------------------------------------------------- 
Total 29 1.822
*******************************************

insert /*+ APPEND */ into monitor.test_jhh
select * from monitor.test_jhh2

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.001 0.002 0 0 0 0
Execute 1 0.004 0.053 0 9 9 3
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 0.005 0.055 0 9 9 3

Misses in library cache during parse : 1
Optimizer Goal : FIRST_ROWS
Parsing user : SYS (ID=0)

Rows Row Source Operation
------- -----------------------------------------------------------------------
0 LOAD AS SELECT (cr=9 pr=0 pw=1 time=51875 us)
3 TABLE ACCESS FULL TEST_JHH2 (cr=7 pr=0 pw=0 time=1482 us cost=3 size=26585 card=409)

Wait Event Name Count Wait(sec) Max Wait
-------------------------------------------------- ------- ---------- ----------
CSS initialization 1 0.046 0.046
Disk file operations I/O 2 0.001 0.000
direct path write 1 0.001 0.001
direct path sync 1 0.001 0.001
gc cr multi block request 1 0.001 0.001
SQL*Net message from client 1 0.709 0.709
CSS operation: action 1 0.002 0.002
SQL*Net message to client 1 0.000 0.000
-------------------------------------------------- ------- ---------- ----------
Total 9 0.759
*************************************************

결국은 문서에서 힌트의 내용 설명 과 달리 insert select(sub query)  구문에서 APPEND 와 APPEND_VALUES 은 동일하게 동작됨을 확인하여 메타링크에서 여러 NOTE를 검색하였으나, 정확한 내용이 없어서 결국은 SR을 진행 하였고 아래와 같은 답변을 받게 되었다.



Bug 8287983 에 의하여 hint append_values 의 경우는 하위 버전의 호환성 (Backward compatibility) 를 위하여 append hint 의 기능을 support 합니다.



그러므로, 11gR2 에서 insert … into … value statement 에 direct insert 의 기능을 지원 하면서, append hint 의 기능도 지원이 가능하다고 합니다.
그렇기 때문에, Test 하신 결과에 의하여 Append_values / append hint 를 사용하였을 때,
동일하게 subquery 를 insert 문과 사용시 direct insert 가 가능합니다.

하위 버전의 호환성 (Backward compatibility) 을 위하여
APPEND_VALUES 힌트에는 APPEND 힌트 기능이 포함되어있다고 이해하면 될 것같다.

일단 현재 사용/테스트 환경인 11.2.0.3 에서는 오라클 문서와 달리 insert select 구문에서도 APPEND 와 APPEND_VALUES 가 동일 하게 동작하지만, 명확히/명시적으로 구문에 따라 힌트를 사용하는 것이 맞을 것 같다

답글 남기기