12.2 버전에 HCC Compression for Array Inserts 기능이 추가되어 Conventional Path로 수행되는 INSERT SELECT 문과 ARRAY INSERT 문도 HCC 압축이 가능해졌습니다.
Hybrid Columnar Compression (HCC) can now be used during array inserts into tables. This means that the SQL INSERT SELECT statement without the APPEND hint can use HCC, and array inserts from programmatic interfaces such as PL/SQL and the Oracle Call Interface (OCI) can use HCC. This feature makes it easier to gain the benefits of HCC by enabling HCC when data is inserted without using direct path operations. In addition, the impact on concurrency is minimal, because direct path operations require a segment lock, whereas array inserts do not use a segment lock.
이 기능은 _disable_hcc_array_insert 파라미터와 관련이 있습니다.
NAME VALUE DEFAULT_VALUE DESCRIPTION
------------------------- ----- ------------- -----------------------------------------------
_disable_hcc_array_insert FALSE FALSE TRUE - enable conventional inserts into HCC CUs
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1-1
DROP TABLE ts1 PURGE;
CREATE TABLE ts1 (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 tt1 PURGE;
DROP TABLE tt2 PURGE;
CREATE TABLE tt1 COMPRESS FOR QUERY HIGH AS SELECT * FROM ts1 WHERE 0 = 1;
CREATE TABLE tt2 COMPRESS FOR QUERY HIGH AS SELECT * FROM ts1 WHERE 0 = 1;
12.2 이전 버전의 동작을 확인하기 위해 _disable_hcc_array_insert 파라미터를 TRUE로 설정하고 Conventional Path로 INSERT 문을 수행하겠습니다. INSERT 문 수행에 8초가 소요됩니다.
-- 2-1
ALTER SESSION SET "_disable_hcc_array_insert" = TRUE;
INSERT INTO tt1 SELECT * FROM ts1;
COMMIT;
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 |00:00:08.03 | 739K|
| 1 | LOAD TABLE CONVENTIONAL | TT1 | 1 | 0 |00:00:08.03 | 739K|
| 2 | TABLE ACCESS STORAGE FULL| TS1 | 1 | 10M|00:00:00.66 | 91596 |
------------------------------------------------------------------------------------
Global Stats
==========================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
==========================================================================================================
| 8.03 | 6.44 | 0.00 | 0.00 | 0.00 | 0.53 | 1.06 | 740K | 1 | 8192 |
==========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2098348240)
====================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows |
| | | | Active(s) | Active | | (Actual) |
====================================================================================
| 0 | INSERT STATEMENT | | 1 | +8 | 1 | 0 |
| 1 | LOAD TABLE CONVENTIONAL | TT1 | 8 | +1 | 1 | 0 |
| 2 | TABLE ACCESS STORAGE FULL | TS1 | 1 | +8 | 1 | 10M |
====================================================================================
12.2 이후 버전의 동작을 확인하기 위해 _disable_hcc_array_insert 파라미터를 FALSE로 설정하고 Conventional Path로 INSERT 문을 수행하겠습니다. INSERT 문 수행에 146초가 소요됩니다. 이 중 145초는 압축을 위한 CPU 작업에 사용되었습니다. 버전 업으로 인해 HCC Compression for Array Inserts 기능이 동작하면 INSERT 문의 수행 시간이 증가하여 성능 저하된 것으로 오해할 수 있습니다. 이런 경우 HCC 압축이 불필요하다면 테이블의 압축 옵션을 NOCOMPRESS로 변경하거나 _disable_hcc_array_insert 파라미터를 TRUE로 설정하는 방안을 고려해야 합니다.
-- 2-2
ALTER SESSION SET "_disable_hcc_array_insert" = FALSE;
INSERT INTO tt2 SELECT * FROM ts1;
COMMIT;
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 |00:02:26.24 | 230K|
| 1 | LOAD TABLE CONVENTIONAL | TT2 | 1 | 0 |00:02:26.24 | 230K|
| 2 | TABLE ACCESS STORAGE FULL| TS1 | 1 | 10M|00:00:00.80 | 91596 |
------------------------------------------------------------------------------------
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
============================================================================================
| 146 | 145 | 0.00 | 0.00 | 0.30 | 0.76 | 231K | 1 | 8192 |
============================================================================================
SQL Plan Monitoring Details
====================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows |
| | | | Active(s) | Active | | (Actual) |
====================================================================================
| 0 | INSERT STATEMENT | | 142 | +6 | 1 | 0 |
| 1 | LOAD TABLE CONVENTIONAL | TT2 | 147 | +1 | 1 | 0 |
| 2 | TABLE ACCESS STORAGE FULL | TS1 | 142 | +6 | 1 | 10M |
====================================================================================
*_SEGMENTS 뷰를 조회하면 tt2 테이블이 압축된 것을 확인할 수 있습니다.
-- 3
SELECT segment_name, bytes / POWER (1024, 2) AS mb
FROM dba_segments
WHERE segment_name IN ('TS1', 'TT1', 'TT2');
SEGMENT_NAME MB
------------ ---
TS1 720
TT1 648
TT2 264
3 행이 선택되었습니다.
DBMS_COMPRESSION.GET_COMPRESSION_TYPE 함수로 tt2 테이블의 압축 유형을 조회해보면 QUERY HIGH 유형으로 압축된 것을 확인할 수 있습니다.
-- 4
SELECT compression_type, COUNT (*) AS num_rows
FROM (SELECT /*+ NO_MERGE */
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ('TUNA', 'TT2', ROWID) AS compression_type
FROM tt2 SAMPLE (0.1))
GROUP BY compression_type;
COMPRESSION_TYPE NUM_ROWS
---------------- --------
4 10156
1개의 행이 선택되었습니다.
압축 유형은 온라인 문서를 참고하거나 아래 쿼리로 조회할 수 있습니다.
-- 5
SELECT REGEXP_SUBSTR (text, '([-0-9]+);', 1, 1, NULL, 1) AS compression_type
, REGEXP_SUBSTR (text, 'COMP_(\w+)', 1, 1, NULL, 1) AS compression_name
FROM dba_source
WHERE owner = 'SYS'
AND name = 'DBMS_COMPRESSION'
AND TYPE = 'PACKAGE'
AND text LIKE 'COMP_%';
COMPRESSION_TYPE COMPRESSION_NAME
---------------- ----------------
1 NOCOMPRESS
2 ADVANCED
4 QUERY_HIGH
8 QUERY_LOW
16 ARCHIVE_HIGH
32 ARCHIVE_LOW
...
24 행이 선택되었습니다.
참고로 HCC Compression for Array Inserts 기능과 관련하여 HCC load conventional로 시작하는 통계가 추가되었습니다. 19.1 버전에 추가된 HCC load conventional CUs tail blk enhanced 통계를 제외하면 HCC load direct로 시작하는 통계와 항목이 일치합니다.
SELECT name
FROM v$statname
WHERE name LIKE 'HCC load%'
ORDER BY name;
NAME
-------------------------------------------
HCC load conventional CUs
HCC load conventional CUs archive high
HCC load conventional CUs archive low
HCC load conventional CUs query high
HCC load conventional CUs query low
HCC load conventional CUs row pieces
HCC load conventional CUs tail blk enhanced -- !
HCC load conventional bytes compressed
HCC load conventional bytes uncompressed
HCC load conventional rows
HCC load conventional rows not compressed
HCC load direct CUs
HCC load direct CUs archive high
HCC load direct CUs archive low
HCC load direct CUs query high
HCC load direct CUs query low
HCC load direct CUs row pieces
HCC load direct bytes compressed
HCC load direct bytes uncompressed
HCC load direct rows
HCC load direct rows not compressed
21 행이 선택되었습니다.
관련 링크