table compress for OLTP
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
값이 줄어든것을 확인할수 있다