1.압축의 원리
DataBase의 Block내의 다수 컬럼의 중복된 값을 제거함.(중복제거)
Compress 된 Block은 Compresion과 관련된 Metadata를 Symbol Table에 저장함.(block dump 내의 bindmp)
이 Symbol Table은 block상단에 위치함.
나머지는 일반 block과 거의 동일.
중복 컬럼 외의 컬럼들은 순서가 해당 block내에 재정렬(컬럼순서가 뒤죽박죽)될 수 있음.
2.장점
스토리지 공간을 2~3배 절감할 수 있음.
Block을 uncompress 하지 않고 바로 read 할 수 있음.
Access하는 block수가 줄어들어 I/O감소효과
Block수가 감소한 만큼 buffer cache를 효율적으로 사용가능.
3.HCC(Hybrid Columnar Compression)
CU(compression unit)개념을 적용하여 컬럼단위로 압축
CU는 32k나 64k로 구성(8k * 4개). CU안에 데이터들은 컬럼으로 재구성 됨.
Table에 대한 압축을 수행할 때 DB server의 CPU를 사용하며, SMART SCAN시 대부분의 압축해제는 Cell Server가 담당.
4.HCC 압축옵션
Query Low
Query High
Archive Low
Archive High
5.HCC압축방법
1) Direct Load
2) CTAS & Direct insert
Create table A compress for < Compress Option > as select ..
INSERT /*+ APPEND */ into A ...
3) Table Move
Alter Table A move compress for <Compression Option> ..
Update/Delete 개념정리
Hcc압축은 컬럼기반 압축이므로 HCC 압축이 된 테이블에 Update/Delete가 수행된다면 압축이 깨지게 됨
Update의 경우 압축해제된 row는 migration 후 적절한시점에 OLTP 압축으로 변환되고 delete는 불용공간화 됨.
**11.2.0.4 이전버전에서는 Update/Delete되는 동안 Compress Unit단위로 Lock이 걸리게 됨***
테스트 시나리오
version Oracle exadata 12.2.0.1
dba_objects 를 기반으로한 CTAS로 Advacned Compress(OLTP)과 HCC(query high) 생성 후 Update 진행하여 블록의 압축 상태변화 체크
1.Advacned Compress
1) 테이블 생성
CREATE TABLE COMP_ADVANCED COMPRESS FOR OLTP
AS
SELECT * FROM DBA_OBJECTS
2)블록 확인
select owner,table_name,tablespace_name,status,compression,compress_for from dba_tables
where table_name='COMP_ADVANCED';
OWNER TABLE_NAME TABLESPACE_NAME STATUS COMPRESSION COMPRESS_FOR
------------- ----------------- ------------------------------ -------- ----------- ------------------------------
YSBAE COMP_ADVANCED USERS VALID ENABLED ADVANCED
SQL> select owner,segment_name,tablespace_name,bytes,blocks,extents from dba_segments where segment_name='COMP_ADVANCED';
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------ ----------------- ------------------------------ --------- --------- ---------
YSBAE COMP_ADVANCED USERS 5242880 640 20
SQL> select object_id,rowid,object_name from comp_advanced where object_id <= 100
order by 1;
OBJECT_ID ROWID OBJECT_NAME
--------- ------------------- --------------------------------------------------------------------------------------------------------------------------------
2 AACNXGAAIAAAFB7AAC C_OBJ#
3 AACNXGAAIAAAFB7AAv I_OBJ#
4 AACNXGAAIAAAFB7AAl TAB$
...
99 rows selected.
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, 'COMP_ADVANCED', ROWID) as compression_type --> 여기에 압축방식 조회할 테이블로 대체
from COMP_ADVANCED --> 여기에 압축방식 조회할 테이블로 대체
where object_id <= 100
)
group by compression_type
)
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Advanced compression level 99
압축테이블 직후 보면 Advanced compress level(2)로 로우들이 블록에 저장된 것을 확인할 수 있습니다.
3)update
UPDATE COMP_ADVANCED
SET OBJECT_NAME = 'YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATE' || dbms_random.string('A',10)
WHERE OBJECT_ID <= 100;
commit;
4)
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, 'COMP_ADVANCED', ROWID) as compression_type --> 여기에 압축방식 조회할 테이블로 대체
from COMP_ADVANCED --> 여기에 압축방식 조회할 테이블로 대체
where object_id <= 100
)
group by compression_type
)
;
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 67
Advanced compression level 32
select object_id,rowid,object_name from comp_advanced where object_id <= 100
order by 1;
OBJECT_ID ROWID OBJECT_NAME
--------- ------------------- --------------------------------------------------------------------------------------------------------------------------------
2 AACNXGAAIAAAFB7AAC YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATExPUCNyRCFX
3 AACNXGAAIAAAFB7AAv YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEhUQZybutiF
4 AACNXGAAIAAAFB7AAl YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATENpmsBjEgwR
...
99 rows selected.
SQL> select owner,segment_name,tablespace_name,bytes,blocks,extents from dba_segments where segment_name='COMP_ADVANCED';
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
--------- ---------------- ------------------------------ --------- --------- ---------
YSBAE COMP_ADVANCED USERS 5242880 640 20
1 rows selected.
기존 99개였던 advanced compress 블록이 32로 줄고 67블록이 압축이 풀리게 되었습니다.
SQL> select object_id,object_name,
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
from
(
select object_id,object_name,dbms_compression.Get_compression_type(USER, 'COMP_ADVANCED', ROWID) as compression_type --> 여기에 압축방식 조회할 테이블로 대체
from COMP_ADVANCED --> 여기에 압축방식 조회할 테이블로 대체
where object_id <= 100
order by object_id
);
OBJECT_ID OBJECT_NAME COMPRESSION_TYPE
--------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------
2 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATExPUCNyRCFX Advanced compression level
3 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEhUQZybutiF No Compression
4 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATENpmsBjEgwR No Compression
5 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATExvOedogvud No Compression
6 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEvErjHgzZAt No Compression
7 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEGmcHfEukYv No Compression
8 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEsgtevgXhAG No Compression
9 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEHSAkgVKkBr Advanced compression level
10 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATErspJyvukkj Advanced compression level
11 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEHubvgKQBbP No Compression
12 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEVbtUjOADJz No Compression
13 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEktRujTcuOG Advanced compression level
14 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEfsIqUqTgAO No Compression
15 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEbRlqgxSaWx No Compression
16 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEuggNhUxSkO Advanced compression level
17 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEtQJEUdytCr No Compression
18 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATERJCPZQkYvU Advanced compression level
19 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEriYaYKPjrl No Compression
20 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEIaoXnjPAce No Compression
21 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEMnoxDRoxDb No Compression
22 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEKraHtodQlH No Compression
23 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATELNwqJzQaPe No Compression
24 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEhAbimaNdmM Advanced compression level
25 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATECQaXGheWcK Advanced compression level
26 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEjOvhoKXIGn Advanced compression level
27 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEAxgtACzofe Advanced compression level
28 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEBAZsiAsBXv No Compression
29 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEhXFGbYECMW Advanced compression level
30 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEXLVAkNRsnr Advanced compression level
31 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEdlPtLptFbV Advanced compression level
32 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEWHaWbrnnpG No Compression
33 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEmqHHcXgXAW Advanced compression level
34 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATENjOjqCSuZa No Compression
35 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATExwSYUMYsGZ Advanced compression level
36 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEskreJIorFl No Compression
37 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATESjMPqQjFRq No Compression
38 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATElcTOGTgVBV Advanced compression level
39 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEClHtPLLkZv Advanced compression level
40 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATElImMhZhJFg No Compression
41 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEdzeGaphKIH Advanced compression level
42 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEWUhYQBHGHy No Compression
43 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEjjvuwOzglz Advanced compression level
44 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEpDjIJjpIgL No Compression
45 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEwIKSZWjggy Advanced compression level
46 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEasFvgquTwE No Compression
47 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEFrHfJClVGj No Compression
48 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEfjUoDLsdUr Advanced compression level
49 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEOyEUThvIEY No Compression
50 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEdQlvEjrEWo No Compression
51 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEGasBWMXqfc No Compression
52 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATExOxCQbPDxR No Compression
53 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEoPiNOtiHSl Advanced compression level
54 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEFiMHnxbFZg Advanced compression level
55 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEWStcrrhVaq No Compression
56 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATExEVFAwTXtR No Compression
57 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEXVilFHUFNk No Compression
58 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEYVWTtCZbJp No Compression
59 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEIXNopPawnp No Compression
60 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEIRcTmayAzm No Compression
61 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEeTFuOqRXdZ No Compression
62 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEQMHSXysmwO Advanced compression level
63 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEdhJjtLwVUs No Compression
64 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEoDuOkLumce No Compression
65 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEVkkBjfPVGi No Compression
66 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEpciIVXeBQj No Compression
67 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEjFaYoBYCEA Advanced compression level
68 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEcOXgQMMYFz No Compression
69 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEulxCBKbZQA No Compression
70 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEvdrDCKlIEn Advanced compression level
71 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEfrjePGouha No Compression
72 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEXYnNlkPBxE No Compression
73 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEBTgvDfYJoU No Compression
74 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEAsQpeJrmxj Advanced compression level
75 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATElyxEOgOHFA No Compression
76 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEPnbkjjWIoE No Compression
77 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEapnxPBifAi Advanced compression level
78 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEUAAvJPzvDJ No Compression
79 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEfnxQFjbmxS No Compression
80 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEOSbkgBzgjO No Compression
81 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEPiIFwYCbfm No Compression
82 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEsCwlUfYOkN Advanced compression level
83 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATETwybTBCzcs No Compression
84 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEdPeLPcMArk No Compression
85 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEWUpuDjyAyR No Compression
86 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEOSchpikKFY Advanced compression level
87 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEQgqJxHeIlM No Compression
88 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEQuiaIXfwbR No Compression
89 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATESQSFaTxOVU No Compression
90 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEAchiuTbUDp No Compression
91 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEvOuhrWpIeR Advanced compression level
92 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEACaXdrAoXS No Compression
93 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEcflSvKOKuL No Compression
94 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEMbLWzKnINF No Compression
95 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATElyQwsdUhXS No Compression
96 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEmDAoIrZJsm No Compression
97 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEiCkpnzMOgq Advanced compression level
98 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEwmmoPUJGAS No Compression
99 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEcBOvAUSkfs No Compression
100 YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATEwWKxvZydKY No Compression
99 rows selected.
아래 접은글을 확인해보면 압축이 풀린 row가 있고 압축이 그대로 유지된 row가 있는것을 확인할 수 있습니다.
SQL> select
dbms_rowid.rowid_relative_fno(rowid) as file_num,
dbms_rowid.rowid_block_number(rowid) as block_num,
dbms_compression.Get_compression_type(USER, 'COMP_ADVANCED', ROWID) as comp_type,
count(*) as row_cnt
from comp_advanced
where object_id <= 100
group by
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid),
dbms_compression.Get_compression_type(USER, 'COMP_ADVANCED', ROWID)
order by file_num,block_num;
FILE_NUM BLOCK_NUM COMP_TYPE ROW_CNT
--------- --------- --------- ---------
8 20603 1 67
8 20603 2 32
그리고 값들의 블록 위치를 확인해보면 같은 블록에 압축블록과 비압축블록이 같이 들어가있는것을 확인할 수 있습니다.
**해당값이 이런것은 Advanced compress의 아키텍쳐와 연관이있습니다.
Advanced compress를 바로바로 압축하지 않고 내부적으로 제어되는 임계값에 도달할 때까지 압축하지 않은 상태로 유지됩니다. 이를 배치모드로 블록을 압축한다고 하는데, 임계값에 도달하면 전체블록이 압축시도를 하여 압축블록이 됩니다.
Advanced Compress(OLTP압축)은 잦은 DML 환경을 타겟으로 하므로, 성능저하의 주범인 Row migration(로우 마이그레이션)을 피하고 기존 블록내에서 업데이터를 처리하는 In-Place Update를 지향합니다.
출처 :Oracle Advanced Compression Frequently Asked Questions 4.2 What optimizations has Oracle done to minimize compression overhead?
Non exa의 압축 비압축 블록덤프 내의 로우 데이터 비교
압축블록은 바인드맵(bindmp)이라고 불리는 동일값을 저장해놓는 부분이 추가되어 있음.
비압축블록은 실제 테이블 컬럼순서대로 저장되어있음.
비압축 테이블 블록 덤프 NOCOMP*/
tab 0, row 64, @0x50e
tl: 119 fb: --H-FL-- lb: 0x0 cc: 22
col 0: [ 3] 53 59 53
col 1: [ 8] 48 49 53 54 47 52 4d 24
col 2: *NULL*
col 3: [ 2] c1 43
col 4: [ 2] c1 41
col 5: [ 5] 54 41 42 4c 45
col 6: [ 7] 78 75 04 01 16 0f 1a
col 7: [ 7] 78 75 04 01 16 0f 1a
col 8: [19] 32 30 31 37 2d 30 34 2d 30 31 3a 32 31 3a 31 34 3a 32 35
col 9: [ 5] 56 41 4c 49 44
col 10: [ 1] 4e
col 11: [ 1] 4e
col 12: [ 1] 4e
col 13: [ 2] c1 02
col 14: *NULL*
col 15: [13] 4d 45 54 41 44 41 54 41 20 4c 49 4e 4b
col 16: *NULL*
col 17: [ 1] 59
col 18: [ 1] 4e
col 19: [14] 55 53 49 4e 47 5f 4e 4c 53 5f 43 4f 4d 50
col 20: [ 1] 4e
col 21: [ 1] 4e
/*압축 테이블 블록 덤프 COMP_ADVACNED*/
tab 1, row 249, @0x312
tl: 30 fb: --H-FL-- lb: 0x0 cc: 26
col 0: *NULL*
col 1: [ 5] 56 41 4c 49 44
col 2: [ 1] 4e
col 3: *NULL*
col 4: [ 1] 4e
col 5: [ 1] 4e
col 6: [ 1] 4e
col 7: *NULL*
col 8: *NULL*
col 9: *NULL*
col 10: *NULL*
col 11: [ 1] 4e
col 12: [ 1] 4e
col 13: [ 1] 59
col 14: [ 3] 53 59 53
col 15: [ 4] 4e 4f 4e 45
col 16: *NULL*
col 17: *NULL*
col 18: [ 2] c1 05
col 19: [ 5] 49 4e 44 45 58
col 20: [ 7] 78 75 04 01 16 0f 21
col 21: [19] 32 30 31 37 2d 30 34 2d 30 31 3a 32 31 3a 31 34 3a 33 32
col 22: [ 7] 78 75 04 01 16 0f 21
col 23: [17] 52 45 43 59 43 4c 45 42 49 4e 24 5f 4f 57 4e 45 52
col 24: [ 3] c2 03 35
col 25: [ 3] c2 03 35
bindmp: 2c 00 04 06 d9 52 45 43 59 43 4c 45 42 49 4e 24 5f 4f 57 4e 45 52 cb c2 03 35 cb c2 03 35
2.HCC(Query High)
1)테이블 생성
DROP TABLE COMP_ADVANCED PURGE;
CREATE TABLE COMP_ADVANCED COMPRESS FOR QUERY HIGH TABLESPACE TS_TMPD01
AS
SELECT * FROM DBA_OBJECTS;
2)
SQL> select owner,table_name,tablespace_name,status,compression,compress_for from dba_tables
where table_name='COMP_ADVANCED';
OWNER TABLE_NAME TABLESPACE_NAME STATUS COMPRESSION COMPRESS_FOR
----------- ---------------- ------------------------------ -------- ----------- ------------------------------
YSBAE COMP_ADVANCED TS_TMPD01 VALID ENABLED QUERY HIGHSQL> select
dbms_rowid.rowid_relative_fno(rowid) as file_num,
dbms_rowid.rowid_block_number(rowid) as block_num,
dbms_compression.Get_compression_type(USER, 'COMP_ADVANCED', ROWID) as comp_type,
count(*) as row_cnt
from comp_advanced
where object_id <= 100
group by
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid),
dbms_compression.Get_compression_type(USER, 'COMP_ADVANCED', ROWID)
order by file_num,block_num;
FILE_NUM BLOCK_NUM COMP_TYPE ROW_CNT
--------- --------- --------- ---------
468 508843 4 99
1 rows selected.
모든 데이터가 508843 블록에 HCC Query High(4) 옵션으로 저장되어 있는것을 확인할 수 있습니다.
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, 'COMP_ADVANCED', ROWID) as compression_type --> 여기에 압축방식 조회할 테이블로 대체
from COMP_ADVANCED --> 여기에 압축방식 조회할 테이블로 대체
where object_id <= 100
)
group by compression_type
);
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
Hybrid Columnar Compression for Query High 99
1 rows selected.
select object_id,rowid from comp_advanced where object_id <= 100
order by 1;
OBJECT_ID ROWID
--------- -------------------
2 AAfTVRAHUAAB8OrAAW
3 AAfTVRAHUAAB8OrAAJ
4 AAfTVRAHUAAB8OrAAs
...
99 rows selected.
select owner,segment_name,tablespace_name,bytes,blocks,extents from dba_segments where segment_name='COMP_ADVANCED';
OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS
----------- ------------------ ------------------------------ --------- --------- ---------
YSBAE COMP_ADVANCED TS_TMPD01 2097152 256 17
1 rows selected.
3)Update 수행
UPDATE COMP_ADVANCED
SET OBJECT_NAME = 'YSBAE`s ADVANCED COMPRESS TEST AFTER UPDATE' || dbms_random.string('A',10)
WHERE OBJECT_ID <= 100;
commit;
4)변화 체크
SQL> 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, 'COMP_ADVANCED', ROWID) as compression_type --> 여기에 압축방식 조회할 테이블로 대체
from COMP_ADVANCED --> 여기에 압축방식 조회할 테이블로 대체
where object_id <= 100
)
group by compression_type
);
COMPRESSION_TYPE NUM_ROWS
------------------------------------------------------------------------------------------------ ---------
No Compression 99
1 rows selected.모두 비압축블록(1)로 변경됨.
select object_id,rowid from comp_advanced where object_id <= 100
order by 1;
OBJECT_ID ROWID
--------- -------------------
2 AAfTVRAHSAACwY/AAV
3 AAfTVRAHSAACwY/AAI
4 AAfTVRAHSAACwY/AAr
...ROWID도 변경됨
select
dbms_rowid.rowid_relative_fno(rowid) as file_num,
dbms_rowid.rowid_block_number(rowid) as block_num,
dbms_compression.Get_compression_type(USER, 'COMP_ADVANCED', ROWID) as comp_type,
count(*) as row_cnt
from comp_advanced
where object_id <= 100
group by
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid),
dbms_compression.Get_compression_type(USER, 'COMP_ADVANCED', ROWID)
order by file_num,block_num;
FILE_NUM BLOCK_NUM COMP_TYPE ROW_CNT
--------- --------- --------- ---------
466 722495 1 49
466 722506 1 49
466 722510 1 1
3 rows selected.
기존에 블록1개(508843)에 있던 데이터들이 3개의 블록에 나눠서 저장되었음.
오라클공식문서에 따르면 HCC 압축된 블록이 일반적인 DML시에는 덜압축된 포맷인 OLTP 압축블록으로 이동한다고 되어 있는데 실제 테스트는 비압축블록으로 보이게됩니다. (추가 테스트가 필요하다고 보입니다)
HCC 압축/비압축된 블록의 블록덤프
/*HCC 압축된 블록덤프 */
data_block_dump,data header at 0x7fc93b6de07c
===============
tsiz: 0x1f80
hsiz: 0x1c
pbl: 0x7fc93b6de07c
76543210
flag=-0------
ntab=1
nrow=1
frre=-1
fsbo=0x1c
fseo=0x30
avsp=0x14
tosp=0x14
r0_9ir2=0x0
mec_kdbh9ir2=0x0
76543210
shcf_kdbh9ir2=----------
76543210
flag_9ir2=--R----- Archive compression: Y
fcls_9ir2[0]={ }
0x16:pti[0] nrow=1 offs=0
0x1a:pri[0] offs=0x30
block_row_dump:
tab 0, row 0, @0x30
tl: 8016 fb: --H-F--N lb: 0x0 cc: 1
nrid: 0x7507c3ac.0
col 0: [8004]
Compression level: 02 (Query High)
Length of CU row: 8004
kdzhrh: ------PC- CBLK: 4 Start Slot: 00
NUMP: 04
PNUM: 00 POFF: 7954 PRID: 0x7507c3ac.0
PNUM: 01 POFF: 15970 PRID: 0x7507c3ad.0
PNUM: 02 POFF: 23986 PRID: 0x7507c3ae.0
PNUM: 03 POFF: 32002 PRID: 0x7507c3af.0
*---------
CU header:
CU version: 0 CU magic number: 0x4b445a30
CU checksum: 0x747fbadd
CU total length: 32359
CU flags: NC-U-CRD-OP
ncols: 26
nrows: 6193
algo: 0
CU decomp length: 31458 len/value length: 713869
row pieces per row: 1
..
START_CU:
00 00 1f 44 17 04 00 00 00 04 00 00 1f 12 75 07 c3 ac 00 00 00 00 3e 62 75
07 c3 ad 00 00 00 00 5d b2 75 07 c3 ae 00 00 00 00 7d 02 75 07 c3 af 00 00
00 4b 44 5a 30 dd ba 7f 74 00 00 7e 67 eb 06 00 1a 18 31 00 0a e4 8d 01 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
...
END_CU
bindmp: 29 00 01 75 07 c3 ac 00 00 fe 44 1f 00 00 1f 44 17 04 00 00 00 04 00 00 1f 12 75 07 c3 ac 00 00 00 00 3e 62 75 07 c3 ad 00 00 00 00 5d b2 75 07 c3 ae 00 00 00 00 7d 02 75 07 c3 af 00 00 00 4b 44 5a 30 dd ba 7f 74 00 00 7e 67 eb 06 00 1a 18 31 00 0a
e4 8d 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
/*비압축블록 덤프*/
data_block_dump,data header at 0x7fe9175c607c
===============
tsiz: 0x1f80
hsiz: 0x54
pbl: 0x7fe9175c607c
76543210
flag=--------
ntab=1
nrow=33
frre=-1
fsbo=0x54
fseo=0x3ca
avsp=0x376
tosp=0x376
0xe:pti[0] nrow=33 offs=0
0x12:pri[0] offs=0x1ea7
0x14:pri[1] offs=0x1dcc
0x16:pri[2] offs=0x1cfa
0x18:pri[3] offs=0x1c20
0x1a:pri[4] offs=0x1b46
0x1c:pri[5] offs=0x1a77
0x1e:pri[6] offs=0x199d
0x20:pri[7] offs=0x18cd
0x22:pri[8] offs=0x17fe
0x24:pri[9] offs=0x1725
0x26:pri[10] offs=0x1644
0x28:pri[11] offs=0x156b
0x2a:pri[12] offs=0x1497
0x2c:pri[13] offs=0x13c3
0x2e:pri[14] offs=0x12e9
0x30:pri[15] offs=0x120e
0x32:pri[16] offs=0x1133
0x34:pri[17] offs=0x1059
0x36:pri[18] offs=0xf87
0x38:pri[19] offs=0xeaf
0x3a:pri[20] offs=0xdcc
0x3c:pri[21] offs=0xcfa
0x3e:pri[22] offs=0xc20
0x40:pri[23] offs=0xb4f
0x42:pri[24] offs=0xa72
0x44:pri[25] offs=0x998
0x46:pri[26] offs=0x8c8
0x48:pri[27] offs=0x7ef
0x4a:pri[28] offs=0x71f
0x4c:pri[29] offs=0x64f
0x4e:pri[30] offs=0x56e
tab 0, row 31, @0x49c
tl: 210 fb: --H-FL-- lb: 0x0 cc: 49
col 0: [20] 32 30 31 35 30 31 30 36 31 35 30 33 35 34 31 35 32 37 32 30
col 1: [ 2] c1 02
col 2: [12] 32 31 34 31 32 31 30 31 37 35 33 39
col 3: *NULL*
col 4: [ 7] 35 30 30 30 34 35 37
col 5: [ 2] 30 34
col 6: [ 1] 4e
col 7: [ 7] 78 72 0c 1a 01 01 01
col 8: [ 1] 4e
col 9: *NULL*
col 10: [ 2] 32 30
col 11: [ 4] 32 30 31 30
col 12: *NULL*
col 13: [ 7] 78 72 0c 1a 01 01 01
col 14: [ 7] 78 72 0c 1a 01 01 01
col 15: [ 7] 78 72 0c 1a 01 01 01
col 16: [ 7] c7 c7 0c 1f 01 01 01
col 17: *NULL*
col 18: *NULL*
col 19: *NULL*
col 20: [ 1] 39
col 21: [ 3] c2 0a 64
col 22: [ 3] c2 0a 64
col 23: *NULL*
col 24: *NULL*
col 25: [ 1] 80
col 26: [ 1] 4e
col 27: *NULL*
col 28: *NULL*
col 29: *NULL*
col 30: [ 1] 80
col 31: *NULL*
col 32: [ 2] 30 31
col 33: [ 1] 30
col 34: [20] 32 30 31 35 30 31 30 36 31 35 30 33 35 34 31 35 32 37 32 30
col 35: [ 2] c0 33
col 36: [ 2] 30 31
col 37: [ 1] 32
col 38: [ 2] c1 15
col 39: [ 6] 32 30 32 35 31 30
col 40: [ 3] c2 02 20
col 41: [ 2] c1 15
col 42: [ 3] c2 03 29
col 43: [ 2] 31 31
col 44: *NULL*
col 45: [ 1] 80
col 46: [ 1] 80
col 47: [ 7] 78 75 0c 1a 01 01 01
col 48: [ 7] c7 c7 0c 1f 18 3c 3c
...
'Oracle > 운영' 카테고리의 다른 글
| DB서버와 클라이언트 간의 캐릭터셋 차이에 따른 한글 입력 깨짐 현상 테스트 (0) | 2026.05.18 |
|---|---|
| Linux에서 gui를 띄우기 위해서 확인해보아야 할것 (0) | 2026.04.27 |
| CTAS의 심화 (0) | 2026.02.02 |
| Delayed block cleanout 발생과 해결법 (0) | 2026.01.06 |
| ORA-01450: Maximum Key Length (6398) Exceeded (3) | 2025.12.19 |
댓글