[펌] 오라클 9i R2의 Table Compression

오라클 9i R2의 Table Compression

 

RDBMS를 사용한 이래로 점점 더 많은 정보가 데이타베이스에 쌓이고 있으며 Telecom 업무와 같은 다량의 데이타를 관리하는 경우 수 테라에서 수십 테라바이트에 이르는 방대한 데이타를 관리해야 하는 어려움에 직면해 있으며 데이타의 양에 비례하여 하드웨어의 비용도 증가하고 있는 것이 현실이다.

이에 대한 대안으로 오라클 9i R2에서 새로 선보인 TABLE Compression 방법에 대해 알아보고 이를 활용하여 얼마나 물리적인 저장공간을 줄일 수 있으며 대량의 데이타를 분석해야 하는 DW 환경에서 성능상 어느 부분에 영향을 미치고 온라인의 어느 경우에 사용가능한지 성능을 비교해 보고자 한다.

TABLE Compression방법은 DB2와 같은 타 DBMS에서는 이미 사용되고 있는 방법으로 오라클은 Oracle 9i Release2에서 기능이 추가되었다.

오라클의 Table Compression의 특징은 다음과 같다.

• TABLE, TABLE Partition, Materialized View에서 사용할 수도 있으며 Tablespace 단위로 지정 가능하다.
• Block 단위로 I/O를 수행하므로 물리적 I/O가 감소한다.
• Lossless dictionary-based 알고리즘을 사용하여 block내 중복값을 제거하는 압축방법을 사용한다.
• Compression 및 Decompression은 ROW 단위로 수행되며 Row level locking은 유효하다.
• Data의 Compression Ratio는 온라인 30~80% DW의 경우 400%까지도 가능하며 데이타 분포도에 따라 다를 수 있다.

1. Compression의 원리

오라클에서 채택하고 있는 Compression방법은 Data Segment Compression 또는 heap-organized block-level compression라고도 하며 한 block내에서 복원되어야 하는 정보는 그 Block내에 모두 존재해야 한다.

아래의 그림은 주문 Table에 고객 정보가 같이 있는 경우를 가정하고 Block내의 압축 저장원리를 설명하였다.

압축된 Block과 압축되지 않은 Block의 비교

이는 column 길이와 중복값의 정도에 따라 모든 중복값은 Symbol Table의 해당값을 참조하고 있으며 압축의 결과로 column의 순서가 해당 block내에서 재정렬 될 수도 있다. 오라클의 Symbol Table Entry는 최적의 Compression 결과를 위해 Data Loading시에 시스템에 의해 결정되어 생성되므로 Symbol Table Entrty를 사용자의 임의로 변경할 필요가 없다. 오라클 알고리즘은 압축 factor와 별도로 분포도의 변경에 대하여 동적으로 변경사항에 대하여 적용된다.


2. Compression Ratio(압축율)

압축율은 데이타의 분포도에 따라 다르지만 DW환경에서는 2:1, 4:1 많은 경우 12:1 까지도 압축되기도 한다. 각 Block의 중복값을 제거하는 알고리즘을 사용하므로 데이타 로드시에 Cardinality가 낮은 값으로 정렬해서 data를 Loading하는 방법이 좋다. DW상황의 Materialized View는 대부분 Group by에 의해 정렬된 값을 생성하는 경우가 많으므로 높은 Compression Ratio을 유지할 수 있다. 또한 block size가 클수록 한 Block내에 중복값이 많이 포함될 수 있으므로 compress Ratio에 영향을주며 Storage Parameter 인 PCTFREE가 낮을수록 Block내에 많은 양의 데이타의를 저장할 수 있으므로Compression Ratio가 높아진다. UPDATE 작업이 거의 없는 Compressed Table에는 PCTFREE를 0로 하는 것이 좋으며 compress option을 사용하여 table creation시에는 default PCTFREE 0으로 지정된다.


3. Compression 대상

Compress 대상은 Table, Materialized View, Partition table의 개별 Partition별로 Compress option지정이 가능하다. Tablespace level에서 지정한 경우 tablespace내에 생성되는 모든 Table들은 default로 Compress option이 적용된다. Table의 Compress option은 변경도 가능하지만 이후에 입력되는 데이타만 Compression의 적용 대상이 된다.

bulk insert나 bulk load시의 Compresion이 일어나는 시기는

• Direct Path SQL*Loader 사용시
• Create Table as SELECT(CTAS) 문장 사용시
• Parallel INSERT ( 또는 append hint를 통한 Serial INSERT) 문장 사용시에 발생한다.

ALTER TABLE TABLE_NAME MOVE COMPRESS;

명령을 통하여 기존에 존재하는 table에 compress option을 지정할 수 있으나 table 전체에 Exclusive Lock 상태로 작업을 진행하므로 이를 고려하여 다른 DML 작업에 영향이 미치지 않도록 해야한다. Compress 적용 Data type은 LOB와 LOB에서 파생된 CLOB등을 제외한 모든 Type에 적용 가능하다.

partition table과 non-partitioned table의 compression여부를 확인하는 dicitionary는 아래의 SQL을 통해 확인해 볼 수 있다.

1) partitioned table

SELECT TABLE_NAME, PARTITION_NAME, COMPRESSION
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME LIKE UPPER(:TAB_NAME)||"%";

2) non-partitioned table

SELECT E.OWNER, E.SEGMENT_NAME, E.SEGMENT_TYPE, S.SPARE1,
DECODE(S.SPARE1,1,"NORMAL",6145,"COMPRESSED",4097,"COMP->NOCOMP","OTHER") COMP_TYPE
FROM SYS.SEG$ S, DBA_EXTENTS E
WHERE E.SEGMENT_NAME LIKE UPPER(:seg_name)||"%"
AND E.SEGMENT_TYPE = "TABLE"
AND E.EXTENT_ID = 0
AND E.BLOCK_ID = S.BLOCK#
AND E.FILE_ID = S.FILE#;

4. Compression/non-Compress Table 성능비교

물리적인 compress ratio는 데이타의 분포도에 따라 다르다. 주로 DW의 데이타의 경우 Cardinality가 낮으나 OLTP성 Transaction 데이타의 경우 Cardinality가 높아 중복 값의 비중이 낮은 경우가 많다. Compression ratio가 높을수록 Data file size나 Redo log file Size가 줄어들게 되므로 Backup /Recovery등 유지보수 효과를 높여줄 뿐만아니라 , Database buffer cache의 Hit Ratio 향상에 도움을 주고 물리적인 DISK I/O를 줄여주는 효과가 있다.

아래의 테스트는 OLTP성 TABLE중 대리점 TABLE로 TEST한 결과이다. e-mail, fax, 주소, 전화번호등 정보등 주로 Cardinality가 높은 편이다.

<그림2> OLTP성 TABLE의 압축율과 SQL 성능비교

결과를 보면 생성시에는 압축시에 2배이상의 시간이 필요하며 압축율은 50%정도의 compression ratio를 보이고 있으며 50%의 Compress ratio에서는 SELECT시의 속도 TEST시 거의 차이가 나타나지 않음을 알 수 있다. Update시에는 40~70% 정도 더 낮은 성능이 나타나는데 이는 압축된 Table의 update시 더 복잡한 operation 과정이 필요하고 PCTFREE등의 Storage Parameter등의 영향으로 판단된다. Delete는 평균 30%정도의 성능향상을 보였는데 이는 data의 압축으로 logging data의 양도 줄어들게 되고 row의 길이가 줄어들게 되어 1 Block내에 더 많은 Row를 포함하는 등의 영향에 따른 것으로 판단된다.아래의 경우는 DW Star Schema하에서의 압축율을 보여주고 있다.

<그림3> DW Star Schema 모델상황의 Compression Ration 비교

위의 그림에서는 300%이상의 Compression Ratio를 보이고 있는데 이와 같이 중복 Value가 많아 Compression Ratio가 높고 DML작업이 적은 DW 환경에서 Compression Option을 사용하는 것이 좋으며, 이와는 반대로 update 빈도가 높은 OLTP 환경에서는 사용시 Performance에 문제를 발생시킬 수 있으므로 사전 Sampling을 통하여 최소 50%이상의 압축율을 보이는지 여부와 DML 사용정도등을 조사한 후 DML의 요구가 많지 않은 집계성 Table에 사용할 것을 권장한다.