반응형
HCC에는 4가지 옵션이 있습니다.
HCC에 관련된 글을 보면 Direct path bulk loading을 하는 DML은 HCC 압축이 가능하고, 12.2부터는 APPEND 없는 Conventional insert와 array insert도 HCC 압축을 지원한다고 되어 있습니다.
테스트를 통해 해당내용에 대해 확인해보도록 하겠습니다.
우선 압축상태의 테이블에 DML 시 어떻게 처리되는지 살펴보도록 하겠습니다.
Exadata HCC압축은 컬럼기반 압축이므로 HCC 압축이 된 테이블에 Update와 Delete가 수행된다면 압축이 깨지게 됩니다. Update의 경우 압축해제 된 Row는 Migration 후 일반 block으로 변환되고 Delete는 불용공간화됩니다.
HCC기능을 사용하여 압축하는 방법으로는 아래와 같습니다.
1) Direct Load
- Insert /*+ PARALLEL */
- sql*loader direct path load
2) CTAS
3) Table Move
(12.2부터 추가)
4) Conventional Load
- INSERT SELECT
테스트
12.2.0.1.0 Exadata 장비에서 수행
4개의 옵션(Query High/Query Low/Archive High/Archive Low)을 사용하여 테이블을 생성하고 일반테이블도 생성합니다.
그리고 여러 방식으로 DML을 수행하여 어떤 블록에 데이터가 담기는지 확인합니다.(DBMS_COMPRESSION.Get_compression_type 활용)
1) CTAS
명령어
CREATE TABLE HCC_QUERYHIGH COMPRESS FOR QUERY HIGH AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE HCC_QUERYLOW COMPRESS FOR QUERY LOW AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE HCC_ARCHHIGH COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE HCC_ARCHLOW COMPRESS FOR ARCHIVE LOW AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE HCC_NOCOMP AS SELECT * FROM DBA_OBJECTS;2.타입확인
HCC 옵션으로 생성하면 PCT_FREE가 0으로 생성됩니다. UPDATE를 하면 무조건 row migration이 발생할 수 밖에 없네요.
SQL> select table_name, pct_free, pct_used,compression,compress_for
from dba_tables
where owner = 'IBMDBA04'
and table_name in
(
'HCC_QUERYHIGH'
,'HCC_QUERYLOW'
,'HCC_ARCHHIGH'
,'HCC_ARCHLOW'
,'HCC_NOCOMP'
);
TABLE_NAME PCT_FREE PCT_USED COMPRESSION COMPRESS_FOR
-------------- --------- --------- ----------- ------------------------------
HCC_ARCHHIGH 0 ENABLED ARCHIVE HIGH
HCC_ARCHLOW 0 ENABLED ARCHIVE LOW
HCC_NOCOMP 10 DISABLED
HCC_QUERYHIGH 0 ENABLED QUERY HIGH
HCC_QUERYLOW 0 ENABLED QUERY LOW
2.블록 확인
쿼리는 아래 쿼리를 계속 사용할 예정입니다. 쿼리는 재사용하기 때문에 처음에만 작성하고 다음부터는 결과값만 보여주겠습니다.
테이블명을 넣는 자리에 각 테이블명을 입력하고 쿼리를 수행합니다. 5개의 테이블이니까 해당 쿼리는 테이블명을 바꿔가며 5번 수행합니다.
select CASE compression_type
WHEN 1 THEN 'No Compression'
WHEN 2 THEN 'Advanced compression level'
WHEN 4 THEN 'Hybrid Columnar Compression for Query High'
WHEN 8 THEN 'Hybrid Columnar Compression for Query Low'
WHEN 16 THEN 'Hybrid Columnar Compression for Archive High'
WHEN 32 THEN 'Hybrid Columnar Compression for Archive Low'
WHEN 64 THEN 'Compressed row'
WHEN 128 THEN 'High compression level for LOB operations'
WHEN 256 THEN 'Medium compression level for LOB operations'
WHEN 512 THEN 'Low compression level for LOB operations'
WHEN 1000 THEN 'Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated'
WHEN 4096 THEN 'Basic compression level'
WHEN 5000 THEN 'Maximum number of LOBs used to compute the LOB compression ratio'
WHEN 1000000 THEN 'Minimum required number of rows in the object for which HCC ratio is to be estimated'
WHEN -1 THEN 'To indicate the use of all the rows in the object to estimate HCC ratio'
WHEN 1 THEN 'Identifies the object whose compression ratio is estimated as of type table'
ELSE 'Unknown Compression Type'
END AS compression_type, n as num_rows
from ( select compression_type, Count(*) n
from ( select dbms_compression.Get_compression_type(USER, 'HCC_NOCOMP', ROWID) as compression_type --> 테이블명 넣기
from HCC_NOCOMP --> 테이블명 넣기
)
group by compression_type
);쿼리 출처 : https://jack-of-all-trades.tistory.com/109
HCC_QUERYHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query High 122448
HCC_QUERYLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query Low 122449
HCC_ARCHHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive High 122450
HCC_ARCHLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive Low 122451
HCC_NOCOMP
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 122452CTAS를 통해 생성한 테이블은 HCC로 잘 설정되어있는 것을 확인할 수 있습니다.
2) Conventional Load INSERT
명령어
INSERT INTO HCC_QUERYHIGH SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT INTO HCC_QUERYLOW SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT INTO HCC_ARCHHIGH SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT INTO HCC_ARCHLOW SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT INTO HCC_NOCOMP SELECT * FROM DBA_OBJECTS; COMMIT;
확인 결과 일반 인덱스를 수행하면 일부 데이터는 일반 Block(No Compression)에 저장되는 것을 확인 할 수 있습니다.
옵션에 따라서 갯수는 차이를 보입니다.
Archive High 옵션은 모든 데이터들이 다 HCC 블록에 저장되었습니다.
HCC_QUERYHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 13
Hybrid Columnar Compression for Query High 244888
HCC_QUERYLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 33
Hybrid Columnar Compression for Query Low 244869
HCC_ARCHHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive High 244903
HCC_ARCHLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 7
Hybrid Columnar Compression for Archive Low 244897
HCC_NOCOMP
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 244905
3)/*+ APPEND */ INSERT
명령어
INSERT /*+ APPEND */ INTO HCC_QUERYHIGH SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT /*+ APPEND */ INTO HCC_QUERYLOW SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT /*+ APPEND */ INTO HCC_ARCHHIGH SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT /*+ APPEND */ INTO HCC_ARCHLOW SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT /*+ APPEND */ INTO HCC_NOCOMP SELECT * FROM DBA_OBJECTS; COMMIT;결과값
APPEND INSERT 를 수행 시에는 모두 HCC BLock에 데이터들이 저장되었습니다.
No Compression으로 보이는 것은 위의 일반 Insert를 하고 저장된 블록입니다.
HCC_QUERYHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 13
Hybrid Columnar Compression for Query High 489802
HCC_QUERYLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 33
Hybrid Columnar Compression for Query Low 489783
HCC_ARCHHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive High 489817
HCC_ARCHLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 7
Hybrid Columnar Compression for Archive Low 489811
HCC_NOCOMP
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 489819
4)DELETE
명령문
DELETE FROM HCC_QUERYHIGH WHERE OWNER IN ('SYS','PUBLIC'); COMMIT;
DELETE FROM HCC_QUERYLOW WHERE OWNER IN ('SYS','PUBLIC'); COMMIT;
DELETE FROM HCC_ARCHHIGH WHERE OWNER IN ('SYS','PUBLIC'); COMMIT;
DELETE FROM HCC_ARCHLOW WHERE OWNER IN ('SYS','PUBLIC'); COMMIT;
DELETE FROM HCC_NOCOMP WHERE OWNER IN ('SYS','PUBLIC'); COMMI
결과값
블록 숫자가 데이터에 따라 많이 준 것을 확인할 수 있습니다.
하지만 테이블의 크기는 삭제전과 동일합니다. 즉 불용공간이 발생한것을 확인할 수 있습니다.
HCC_QUERYHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 1
Hybrid Columnar Compression for Query High 90454
HCC_QUERYLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 4
Hybrid Columnar Compression for Query Low 90452
HCC_ARCHHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive High 90457
HCC_ARCHLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 1
Hybrid Columnar Compression for Archive Low 90457
HCC_NOCOMP
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 90459SQL> select segment_name,round(bytes/1024/1024,2) as MB from dba_segments where segment_name in
(
'HCC_QUERYHIGH'
,'HCC_QUERYLOW'
,'HCC_ARCHHIGH'
,'HCC_ARCHLOW'
,'HCC_NOCOMP'
);
##삭제전
SEGMENT_NAME MB
----------------- ---------
HCC_QUERYHIGH 5
HCC_QUERYLOW 9
HCC_ARCHHIGH 3
HCC_ARCHLOW 5
HCC_NOCOMP 54
##삭제후
SEGMENT_NAME MB
----------------- ---------
HCC_QUERYHIGH 5
HCC_QUERYLOW 9
HCC_ARCHHIGH 3
HCC_ARCHLOW 5
HCC_NOCOMP 54
5)UPDATE
명령문
UPDATE HCC_QUERYHIGH SET OBJECT_NAME=DBMS_RANDOM.STRING('A',30); COMMIT;
UPDATE HCC_QUERYLOW SET OBJECT_NAME=DBMS_RANDOM.STRING('A',30); COMMIT;
UPDATE HCC_ARCHHIGH SET OBJECT_NAME=DBMS_RANDOM.STRING('A',30); COMMIT;
UPDATE HCC_ARCHLOW SET OBJECT_NAME=DBMS_RANDOM.STRING('A',30); COMMIT;
UPDATE HCC_NOCOMP SET OBJECT_NAME=DBMS_RANDOM.STRING('A',30); COMMIT;결과
UPDATE에 해당되는 데이터가 있는 block은 모두 일반 block으로 변경되었습니다. 즉 압축이 풀렸습니다.
UPDATE문이 모든 row에 해당하는 것이라서 모든 블록이 변경되었습니다만, where 절을 추가해 특정 row만 변경하면 그 부분만 일반 block으로 변경되게 됩니다.
HCC_QUERYHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 90455
HCC_QUERYLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 90456
HCC_ARCHHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 90457
HCC_ARCHLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 90458
HCC_NOCOMP
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 90459압축이 풀리면서 용량도 늘어나게되었습니다.
SEGMENT_NAME MB
--------------- ---------
HCC_QUERYHIGH 15
HCC_QUERYLOW 19
HCC_ARCHHIGH 14
HCC_ARCHLOW 15
HCC_NOCOMP 54
6) SQL*LOADER Direct path load
명령어
DROP TABLE HCC_QUERYHIGH PURGE;
DROP TABLE HCC_QUERYLOW PURGE;
DROP TABLE HCC_ARCHHIGH PURGE;
DROP TABLE HCC_ARCHLOW PURGE;
DROP TABLE HCC_NOCOMP PURGE;
CREATE TABLE HCC_QUERYHIGH COMPRESS FOR QUERY HIGH AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
CREATE TABLE HCC_QUERYLOW COMPRESS FOR QUERY LOW AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
CREATE TABLE HCC_ARCHHIGH COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
CREATE TABLE HCC_ARCHLOW COMPRESS FOR ARCHIVE LOW AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
CREATE TABLE HCC_NOCOMP AS SELECT * FROM DBA_OBJECTS WHERE 1=2;컨드롤파일 생성(각각 5개를 생성합니다.)
vi hcc_queryhigh.ctl
load data
infile '/home/oracle/dba/ysbae/loader/dataobj.txt'
append
into table HCC_QUERYHIGH
fields terminated by ' '
trailing nullcols
(
OWNER
,OBJECT_NAME
,SUBOBJECT_NAME
,OBJECT_ID
,DATA_OBJECT_ID
,OBJECT_TYPE
,CREATED date "YYYY/MM/DD HH24:MI:SS"
,LAST_DDL_TIME date "YYYY/MM/DD HH24:MI:SS"
,TIMESTAMP
,STATUS
,TEMPORARY
,GENERATED
,SECONDARY
,NAMESPACE
,EDITION_NAME
,SHARING
,EDITIONABLE
,ORACLE_MAINTAINED
,APPLICATION
,DEFAULT_COLLATION
,DUPLICATED
,SHARDED
,CREATED_APPID
,CREATED_VSNID
,MODIFIED_APPID INTEGER
,MODIFIED_VSNID INTEGER
)컨트롤 파일을 변경해서 5번 수행해줍니다.
sqlldr '계정'/'비밀번호' control=/home/oracle/dba/ysbae/loader/hcc_queryhigh.ctl log=hcc_queryhigh.log bad=hcc_queryhigh.bad direct=true
HCC압축이 되서 잘 저장된것을 확인할 수 있습니다.
hcc_queryhigh
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query High 122515
HCC_QUERYLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query Low 122515
HCC_ARCHHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive High 122515
hcc_archlow
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive Low 122515
no_comp
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 122515
7) PDML
명령어
DROP TABLE HCC_QUERYHIGH PURGE;
DROP TABLE HCC_QUERYLOW PURGE;
DROP TABLE HCC_ARCHHIGH PURGE;
DROP TABLE HCC_ARCHLOW PURGE;
DROP TABLE HCC_NOCOMP PURGE;
CREATE TABLE HCC_QUERYHIGH COMPRESS FOR QUERY HIGH AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
CREATE TABLE HCC_QUERYLOW COMPRESS FOR QUERY LOW AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
CREATE TABLE HCC_ARCHHIGH COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
CREATE TABLE HCC_ARCHLOW COMPRESS FOR ARCHIVE LOW AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
CREATE TABLE HCC_NOCOMP AS SELECT * FROM DBA_OBJECTS WHERE 1=2;INSERT /*+ PARALLEL(4) */ INTO HCC_QUERYHIGH SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT /*+ PARALLEL(4) */ INTO HCC_QUERYLOW SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT /*+ PARALLEL(4) */ INTO HCC_ARCHHIGH SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT /*+ PARALLEL(4) */ INTO HCC_ARCHLOW SELECT * FROM DBA_OBJECTS; COMMIT;
INSERT /*+ PARALLEL(4) */ INTO HCC_NOCOMP SELECT * FROM DBA_OBJECTS; COMMIT;
확인
각 옵션에 맞게 HCC 압축이 잘 유지되고 적재된것을 확인할 수 있습니다.
HCC_QUERYHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query High 122531
HCC_QUERYLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query Low 122531
HCC_ARCHHIGH
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 4
Hybrid Columnar Compression for Archive High 122527
HCC_ARCHLOW
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive Low 122531
HCC_NOCOMP
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 122531
8) Array Insert
명령어
DROP TABLE HCC_QUERYHIGH PURGE;
DROP TABLE HCC_QUERYLOW PURGE;
DROP TABLE HCC_ARCHHIGH PURGE;
DROP TABLE HCC_ARCHLOW PURGE;
DROP TABLE HCC_NOCOMP PURGE;
CREATE TABLE HCC_QUERYHIGH (A NUMBER, B NUMBER, C NUMBER) COMPRESS FOR QUERY HIGH ;
CREATE TABLE HCC_QUERYLOW (A NUMBER, B NUMBER, C NUMBER) COMPRESS FOR QUERY LOW;
CREATE TABLE HCC_ARCHHIGH (A NUMBER, B NUMBER, C NUMBER) COMPRESS FOR ARCHIVE HIGH;
CREATE TABLE HCC_ARCHLOW (A NUMBER, B NUMBER, C NUMBER) COMPRESS FOR ARCHIVE LOW;
CREATE TABLE HCC_NOCOMP (A NUMBER, B NUMBER, C NUMBER);DECLARE
TYPE v_HCC_QUERYHIGH IS TABLE OF HCC_QUERYHIGH%ROWTYPE INDEX BY BINARY_INTEGER;
ysbae v_HCC_QUERYHIGH;
BEGIN
FOR i IN 1..1000000 LOOP
ysbae(i).A :=i;
ysbae(i).B :=10;
ysbae(i).C :=99;
END LOOP;
FORALL i in 1..1000000 INSERT INTO HCC_QUERYHIGH VALUES ysbae(i);
COMMIT;
FORALL i in 1..1000000 INSERT INTO HCC_QUERYLOW VALUES ysbae(i);
COMMIT;
FORALL i in 1..1000000 INSERT INTO HCC_ARCHHIGH VALUES ysbae(i);
COMMIT;
FORALL i in 1..1000000 INSERT INTO HCC_ARCHLOW VALUES ysbae(i);
COMMIT;
FORALL i in 1..1000000 INSERT INTO HCC_NOCOMP VALUES ysbae(i);
COMMIT;
END;
/
결과
각 옵션별로 잘 적재되었습니다.
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query High 1000000
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query Low 1000000
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive High 1000000
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Archive Low 1000000
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 1000000
여러 테스트를 통해 direct bulk loading 시에는 insert 작업에도 압축이 풀리지 않고 잘 적재됩니다.
conventional load insert(일반 insert)는 일부 블록은 일반 block에 적재됩니다. (완벽하게 HCC 압축 block으로 호환되서 적재되는 것같아 보이지는 않습니다.)
그리고 update시에 row migration이 일어나면서 OLTP compress block으로 저장된다고 그랬는데 제가 테스트 한 결과로는 아예 비압축 block으로 저장된 것을 확인할 수 있었습니다.
버전이 바뀌면서 아키텍쳐가 바뀐건지..
주로 참고한 다른 자료는 아래와 같습니다.
참고 : https://blog.goodusdata.com/201
참고 : https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=235930
참고 :Oracle Hybrid Columnar Compression.pdf
반응형
'Oracle > Exadata' 카테고리의 다른 글
| Exadata 스마트 스캔 힌트로 제어 하기. (2) | 2024.01.13 |
|---|---|
| 장비 시리얼 번호 알기 (0) | 2023.06.29 |
| [Sundiag] 한 노드에서 한번에 EXADATA H/W 수집하는 쉘 (0) | 2023.02.08 |
| Smart Scan(Cell offload) (0) | 2021.11.04 |
댓글