종이컵통신 :: 11g compress table...

11g compress table

from 6. 먹고살기/6.1 Oracle 2011/09/14 17:14

11g Compress table을 해보기로함..

declare
lv_cmp_ratio number;
lv_comptype_str varchar2(300);
lv_BLKCNT_CMP number;
lv_BLKCNT_UNCMP number;
lv_ROW_CMP number;
lv_ROW_UNCMP number;
begin
dbms_compression.GET_COMPRESSION_RATIO(
SCRATCHTBSNAME=>'EXAMPLE',
OWNNAME=>'SCOTT',
TABNAME=>'IMSI',
PARTNAME =>null,
COMPTYPE =>2, ---2 means OLTP
BLKCNT_CMP=>lv_BLKCNT_CMP,
BLKCNT_UNCMP=>lv_BLKCNT_UNCMP,
ROW_CMP =>lv_ROW_CMP,
ROW_UNCMP =>lv_ROW_UNCMP,
CMP_RATIO=>lv_cmp_ratio,
COMPTYPE_STR=>lv_COMPTYPE_STR);

dbms_output.put_line('====================================================');
dbms_output.put_line('1. Compression Ratio :'||lv_cmp_ratio);
dbms_output.put_line('2. Block Count :'||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type :'||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed :'||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count Un-compressed:'||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed :'||lv_row_cmp);
dbms_output.put_line('4. Row Count Un-Compressed:'||lv_row_uncmp);
dbms_output.put_line('====================================================');
end;
/

적절한 값을 넣은후 실행을 시키면 다음과 같은 값이 나온당

====================================================
1. Compression Ratio :2.8
2. Block Count :694
3. Compression Type :"Compress For OLTP"
4. Blk Count Compressed :694
5. Blk Count Un-compressed:1988
6. Row Count Compressed :202
4. Row Count Un-Compressed:70
====================================================

해당 패키지는 압축률을 계산해 어떠한 방식으로 압축을 했을때

얼마나 효과가 있는지 알려준다 다음은 CTAS로 넘긴후 계산된결과이다

col segment for a40
select segment_name, bytes/1024/1024 as MB, a.blocks, compression, compress_for from dba_segments a,
dba_tables b
where a.segment_name = b.table_name and a.segment_name in ('IMSI','IMSI_COMPRESS');

SEGMENT_NAME                 MB     BLOCKS COMPRESS COMPRESS_FOR
-------------------- ---------- ---------- -------- ------------
IMSI                       4156     531968 DISABLED
IMSI_COMPRESS              1351     172928 ENABLED  BASIC

2 rows selected.

Elapsed: 00:00:00.03

17:08:23 SCOTT@RAC1> select count(*) from imsi;

  COUNT(*)
----------
  37377536

1 row selected.

Elapsed: 00:01:02.38
17:14:11 SCOTT@RAC1> select count(*) from imsi_compress;

  COUNT(*)
----------
  37377536

1 row selected.

Elapsed: 00:00:16.67

예상치보다 압축률이 잘나왔다. 해당 더미데이터는 dba_objects 들이다...

그리고 단순히 count 함수를 써서 돌려보았다..

압축되지 않은 테이블은 1분가량 시간이 소요되었고 압축된 테이블은 16초가량이 걸렸다

09:21:56 SCOTT@RAC1> desc imsi
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                          VARCHAR2(30)
 OBJECT_NAME                                                    VARCHAR2(128)
 SUBOBJECT_NAME                                                 VARCHAR2(30)
 OBJECT_ID                                                      NUMBER
 DATA_OBJECT_ID                                                 NUMBER
 OBJECT_TYPE                                                    VARCHAR2(19)
 CREATED                                                        DATE
 LAST_DDL_TIME                                                  DATE
 TIMESTAMP                                                      VARCHAR2(19)
 STATUS                                                         VARCHAR2(7)
 TEMPORARY                                                      VARCHAR2(1)
 GENERATED                                                      VARCHAR2(1)
 SECONDARY                                                      VARCHAR2(1)
 NAMESPACE                                                      NUMBER
 EDITION_NAME                                                   VARCHAR2(30)

09:22:34 SCOTT@RAC1> desc imsi_compress
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                          VARCHAR2(30)
 OBJECT_NAME                                                    VARCHAR2(128)
 SUBOBJECT_NAME                                                 VARCHAR2(30)
 OBJECT_ID                                                      NUMBER
 DATA_OBJECT_ID                                                 NUMBER
 OBJECT_TYPE                                                    VARCHAR2(19)
 CREATED                                                        DATE
 LAST_DDL_TIME                                                  DATE
 TIMESTAMP                                                      VARCHAR2(19)
 STATUS                                                         VARCHAR2(7)
 TEMPORARY                                                      VARCHAR2(1)
 GENERATED                                                      VARCHAR2(1)
 SECONDARY                                                      VARCHAR2(1)
 NAMESPACE                                                      NUMBER
 EDITION_NAME                                                   VARCHAR2(30)

혹시나 해서 memory flush 후 재실행-

17:50:51 SCOTT@RAC1> alter system flush shared_pool;

System altered.

Elapsed: 00:00:23.09
17:51:20 SCOTT@RAC1> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.52
17:51:40 SCOTT@RAC1> select count(*) from imsi;

  COUNT(*)
----------
  37377536

1 row selected.

Elapsed: 00:01:10.34

17:53:03 SCOTT@RAC1> alter system flush shared_pool;

System altered.

Elapsed: 00:00:01.99
17:53:53 SCOTT@RAC1> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.29
17:53:55 SCOTT@RAC1>  select count(*) from imsi_compress;

  COUNT(*)
----------
  37377536

1 row selected.

Elapsed: 00:00:17.07

값이 줄어든것을 확인할수 있다