지난 글에 이어 HCC Compression for Array Inserts 기능에 대한 INSERT SELECT 문과 Array INSERT 문을 Conventional Path와 Direct Path로 수행했을 때의 동작을 비교하겠습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1-1
DROP TABLE ts PURGE;
CREATE TABLE ts (c1, c2, c3, c4, c5, c6) AS
SELECT ROWNUM
, LPAD (ORA_HASH (ROWNUM, 4294967295, 0), 10, '0')
, LPAD (ORA_HASH (ROWNUM, 4294967295, 1), 10, '0')
, LPAD (ORA_HASH (ROWNUM, 4294967295, 2), 10, '0')
, LPAD (ORA_HASH (ROWNUM, 4294967295, 3), 10, '0')
, LPAD (ORA_HASH (ROWNUM, 4294967295, 4), 10, '0')
FROM XMLTABLE ('1 to 10000000');
-- 1-2
DROP TABLE tt_isc PURGE;
DROP TABLE tt_isd PURGE;
DROP TABLE tt_aic PURGE;
DROP TABLE tt_aid PURGE;
CREATE TABLE tt_isc COMPRESS FOR QUERY HIGH AS SELECT * FROM ts1 WHERE 0 = 1;
CREATE TABLE tt_isd COMPRESS FOR QUERY HIGH AS SELECT * FROM ts1 WHERE 0 = 1;
CREATE TABLE tt_aic COMPRESS FOR QUERY HIGH AS SELECT * FROM ts1 WHERE 0 = 1;
CREATE TABLE tt_aid COMPRESS FOR QUERY HIGH AS SELECT * FROM ts1 WHERE 0 = 1;
첫 번째로 INSERT SELECT 문을 Conventional Path로 수행합니다.
-- 2
INSERT INTO tt_isc SELECT * FROM ts;
COMMIT;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 |00:02:26.59 | 229K|
| 1 | LOAD TABLE CONVENTIONAL | TT_ISC | 1 | 0 |00:02:26.59 | 229K|
| 2 | TABLE ACCESS STORAGE FULL| TS | 1 | 10M|00:00:00.84 | 91596 |
--------------------------------------------------------------------------------------
Global Stats
================================================================================
| Elapsed | Cpu | Application | Concurrency | Cluster | Other | Buffer |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets |
================================================================================
| 147 | 146 | 0.00 | 0.00 | 0.31 | 0.76 | 230K |
================================================================================
SQL Plan Monitoring Details
===================================================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | Active(s) | Active | | (Actual) | (%) | (# samples) |
===================================================================================================================
| 0 | INSERT STATEMENT | | 142 | +6 | 1 | 0 | | |
| 1 | LOAD TABLE CONVENTIONAL | TT_ISC | 146 | +2 | 1 | 0 | 98.60 | Cpu (141) |
| 2 | TABLE ACCESS STORAGE FULL | TS | 142 | +6 | 1 | 10M | 1.40 | Cpu (2) |
===================================================================================================================
두 번째로 APPEND 힌트를 사용하여 INSERT SELECT 문을 Direct Path로 수행합니다.
-- 3
INSERT /*+ APPEND */ INTO tt_isd SELECT * FROM ts;
COMMIT;
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Writes | Used-Mem |
----------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 |00:00:25.55 | 128K| 32155 | |
| 1 | LOAD AS SELECT | TT_ISD | 1 | 0 |00:00:25.55 | 128K| 32155 | 2070K (0)|
| 2 | TABLE ACCESS STORAGE FULL| TS | 1 | 10M|00:00:00.99 | 91596 | 0 | |
----------------------------------------------------------------------------------------------------------
Global Stats
==============================================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Other | Buffer | Write | Write | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Returned Bytes |
==============================================================================================================
| 26 | 25 | 0.01 | 0.00 | 0.01 | 0.20 | 128K | 262 | 251MB | 502MB |
==============================================================================================================
SQL Plan Monitoring Details
===================================================================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows | Write | Write | Activity | Activity Detail |
| | | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
===================================================================================================================================
| 0 | INSERT STATEMENT | | 21 | +6 | 1 | 2 | | | | |
| 1 | LOAD AS SELECT | TT_ISD | 26 | +1 | 1 | 2 | 262 | 251MB | 100.00 | Cpu (25) |
| 2 | TABLE ACCESS STORAGE FULL | TS | 21 | +6 | 1 | 10M | | | | |
===================================================================================================================================
세 번째로 Array INSERT 문을 Conventional Path로 수행합니다.
-- 4
DECLARE
CURSOR cur_ts IS SELECT * FROM ts;
TYPE tnt_ts IS TABLE OF ts%ROWTYPE;
v_ts tnt_ts;
BEGIN
OPEN cur_ts;
LOOP
FETCH cur_ts BULK COLLECT INTO v_ts LIMIT 1000;
FORALL i IN v_ts.FIRST .. v_ts.LAST INSERT INTO tt_aic VALUES v_ts(i);
EXIT WHEN cur_ts%NOTFOUND;
END LOOP;
CLOSE cur_ts;
COMMIT;
END;
/
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10000 | 0 |00:00:27.71 | 301K|
| 1 | LOAD TABLE CONVENTIONAL | TT_AIC | 10000 | 0 |00:00:27.71 | 301K|
------------------------------------------------------------------------------------
Global Stats
====================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | PL/SQL | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |
====================================================================================================================
| 34 | 33 | 0.00 | 0.00 | 0.00 | 0.44 | 0.08 | 0.57 | 403K | 1 | 8192 |
====================================================================================================================
마지막으로 APPEND_VALUES 힌트를 사용하여 Array INSERT 문을 Direct Path로 수행합니다. COMMIT 문을 루프 내에서 수행해야 "ORA-12838: 병렬로 수정한 후 객체를 읽거나 수정할 수 없습니다" 에러가 발생하지 않습니다.
-- 5
DECLARE
CURSOR cur_ts IS SELECT * FROM ts;
TYPE tnt_ts IS TABLE OF ts%ROWTYPE;
v_ts tnt_ts;
BEGIN
OPEN cur_ts;
LOOP
FETCH cur_ts BULK COLLECT INTO v_ts LIMIT 1000;
FORALL i IN v_ts.FIRST .. v_ts.LAST INSERT /*+ APPEND_VALUES */ INTO tt_aid VALUES v_ts(i);
COMMIT;
EXIT WHEN cur_ts%NOTFOUND;
END LOOP;
CLOSE cur_ts;
END;
/
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Writes | O/1/M |
------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10000 | 0 |00:00:38.59 | 145K| 40000 | |
| 1 | LOAD AS SELECT | TT_AID | 10000 | 0 |00:00:38.59 | 145K| 40000 | 10000/0/0|
| 2 | BULK BINDS GET | | 10000 | 10M|00:00:03.36 | 0 | 0 | |
------------------------------------------------------------------------------------------------
Global Stats
========================================================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | PL/SQL | Other | Buffer | Write | Write | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Returned Bytes |
========================================================================================================================
| 59 | 45 | 3.99 | 0.00 | 0.02 | 0.13 | 11 | 461K | 10288 | 313MB | 625MB |
========================================================================================================================
*_SEGMENTS 뷰를 조회하면 타깃 테이블이 모두 압축된 것을 확인할 수 있습니다. 압축 효율은 INSERT SELECT 문이 Array INSERT 문보다 높고, Direct Path가 Conventional Path보다 높습니다.
-- 6
SELECT segment_name, bytes / POWER (1024, 2) AS mb
FROM user_segments
WHERE segment_name IN ('TS', 'TT_ISC', 'TT_ISD', 'TT_AIC', 'TT_AID');
SEGMENT_NAME MB
------------ ---
TS 720
TT_ISC 264 -- INSERT SELECT + Conventional Path
TT_ISD 256 -- INSERT SELECT + Direct Path
TT_AIC 376 -- Array INSERT + Conventional Path
TT_AID 320 -- Array INSERT + Direct Path
5 행이 선택되었습니다.
아래는 SQL 모니터 리포트의 Global Stats 항목을 비교한 결과입니다. 먼저 INSERT SELECT 문은 CPU 시간으로 인해 Conventional Path가 Direct Path보다 오래 수행되었습니다. Array INSERT 문은 COMMIT에 의한 Other 대기(enq: CR - block range reuse ckpt)로 인해 Direct Path가 Conventional Path보다 오래 수행되었습니다.
-- 8
========================================================================================================
| Table | Elapsed | Cpu | IO | Other | PL/SQL | Buffer | Write | Write | Offload |
| | Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | Returned Bytes |
========================================================================================================
| TT_ISC | 147 | 146 | | 0.76 | | 230K | | | |
| TT_ISD | 26 | 25 | 0.01 | 0.20 | | 128K | 262 | 251MB | 502MB |
| TT_AIC | 34 | 33 | 0.00 | 0.57 | 0.08 | 403K | | | |
| TT_AID | 59 | 45 | 3.99 | 11 | 0.13 | 461K | 10288 | 313MB | 625MB |
========================================================================================================
아래는 주요 세션 통계 값을 비교한 결과입니다.
-- 9
+--------------------------------------------+----------+----------+----------+----------+------+
|NAME | TT_ISC| TT_ISD| TT_AIC| TT_AID|CLASS |
+--------------------------------------------+----------+----------+----------+----------+------+
|CPU used by this session | 14360| 2354| 3173| 4228|User |
|DB time | 14471| 2384| 3288| 5649|User |
|session logical reads | 230908| 129051| 408647| 461673|User |
|physical read IO requests | 2| 7| 1| 1|Cache |
|physical read bytes | 16384| 57344| 8192| 8192|Cache |
|physical read requests optimized | 2| 7| 1| 1|Cache |
|physical read total IO requests | 2| 7| 1| 1|Cache |
|physical read total bytes | 16384| 57344| 8192| 8192|Cache |
|physical read total bytes optimized | 16384| 57344| 8192| 8192|Cache |
|physical reads | 2| 7| 1| 1|Cache |
|physical reads cache | 2| 7| 1| 1|Cache |
|physical write IO requests | 0| 262| 0| 10288|Cache |
|physical write bytes | 0| 263430144| 0| 327680000|Cache |
|physical write total IO requests | 0| 262| 0| 10288|Cache |
|physical write total bytes | 0| 263430144| 0| 327680000|Cache |
|physical write total bytes optimized | 0| 999424| 0| 327680000|Cache |
|physical write total multi block requests | 0| 252| 0| 0|Cache |
|physical writes | 0| 32157| 0| 40000|Cache |
|physical writes direct | 0| 32157| 0| 40000|Cache |
|physical writes non checkpoint | 0| 32157| 0| 40000|Cache |
|HCC DML conventional | 34763| 0| 40000| 0|Debug |
|HCC load conventional CUs | 3785| 0| 10000| 0|Debug |
|HCC load conventional CUs query high | 3785| 0| 10000| 0|Debug |
|HCC load conventional CUs row pieces | 34763| 0| 40000| 0|Debug |
|HCC load conventional CUs tail blk enhanced | 2908| 0| 0| 0|Debug | -- !
|HCC load conventional bytes compressed | 252054840| 0| 256929277| 0|Debug |
|HCC load conventional bytes uncompressed | 608888893| 0| 608888893| 0|Debug |
|HCC load conventional rows | 10000000| 0| 10000000| 0|Debug |
|HCC load direct CUs | 0| 8218| 0| 10576|Debug |
|HCC load direct CUs query high | 0| 8218| 0| 10576|Debug |
|HCC load direct CUs row pieces | 0| 34015| 0| 40000|Debug |
|HCC load direct bytes compressed | 0| 259597305| 0| 264403749|Debug |
|HCC load direct bytes uncompressed | 0| 617431302| 0| 626431977|Debug |
|HCC load direct rows | 0| 10140783| 0| 10288000|Debug |
|HCC load direct rows not compressed | 0| 7| 0| 0|Debug |
+--------------------------------------------+----------+----------+----------+----------+------+