Friday, August 3, 2012

Oracle Compression: To Compress Or Not To Compress



I.) Environment Setup and Compression Test:


1.) Table COMPRESSION_TEST to be used for testing. The size of the table is:

00:42:47 SQL> select segment_name,bytes/1024/1024/1024 "Size in GB" from dba_segments where owner='APUN' and segment_name='COMPRESSION_TEST';

SEGMENT_NAME   Size in GB
----------------------------------------------------------------------
COMPRESSION_TEST   37.208984375

This is a typical data warehousing aggregate table. It has both varchar2 and number columns:
select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns where table_name='COMPRESSION_TEST'

COLUMN_NAME       DATA_TYPE   DATA_LENGTH
------------------------------ ---------- ------------
ID1 NUMBER 22
ID2       NUMBER     22
ID3        NUMBER   22
ID4       NUMBER 22
ID5       NUMBER 22
ID6       NUMBER 22
ID7 NUMBER 22
ID8       NUMBER 22
ID9       NUMBER 22
ID10       VARCHAR2      1
ID11        NUMBER 22
ID12       NUMBER 22
CODE1       VARCHAR2   1024
CODE2       VARCHAR2   1024
CODE3       VARCHAR2   1024
CODE4       VARCHAR2   1024
ID13       NUMBER 22
ID14     NUMBER 22
ID15       NUMBER 22
ID16   NUMBER 22
ID17  NUMBER 22
DETAILS       VARCHAR2   4000


2.) I create a table with BASIC compression:

15:05:27 SQL> create table COMPRESSION_TEST_BASIC tablespace USERS compress as select * from COMPRESSION_TEST;

Table created.

Table Size after Compression:

16:27:19 SQL> select segment_name,bytes/1024/1024/1024 from user_segments where segment_name='COMPRESSION_TEST_BASIC';

SEGMENT_NAME   BYTES/1024/1024/1024
-------------------------------------------------- --------------------
COMPRESSION_TEST_BASIC     9.724609375


3.) Now I am creating a table with "Compress for OLTP":

16:28:48 SQL> create table COMPRESSION_TEST_OLTP tablespace USERS compress for oltp as select * from COMPRESSION_TEST;


Table created.

Table Size after compression:
16:51:12 SQL> 16:51:12 SQL> select segment_name,bytes/1024/1024/1024 from user_segments where segment_name='COMPRESSION_TEST_OLTP';

SEGMENT_NAME   BYTES/1024/1024/1024
-------------------------------------------------- --------------------
COMPRESSION_TEST_OLTP   10.895507813


II.) Deciding to Compress an existing tables or not


We would like to evaluate the amount of compression we would get before we decide to compress a table. We can do a "create table as select" as above, but this would not be feasible for very large table.
Oracle provides a handy procedure to estimate the compression we would get on an existing table. The procedure is called DBMS_COMPRESSION.GET_COMPRESSION_RATIO. DBMS_COMPRESSION.GET_COMPRESSION_RATIO cannot be used to find the ratio for BASIC compression.

Note: Please create a tablespace which you would use for this testing, as this procedure can use a lot of space for getting the compression ratio. We would not want to use our application or users tablespace as it would be difficult to retrieve this space back. You can create a tablespace named SCRAP and then drop it after the evaluation is done.

I have create a small wrapper procedure in SYS schema for this:
CREATE OR REPLACE PROCEDURE CHECK_COMPRESSION
(OWNNAME IN VARCHAR2,
 TABNAME IN VARCHAR2,
 TBSPNAME IN VARCHAR2,
 COMTYPE IN NUMBER)
IS
COMPRESSION_RATIO NUMBER;
COMPRESSION_TYPE VARCHAR2(500);
BLKCNT_COMPRESSION NUMBER;
BLKCNT_NOCOMPRESSION NUMBER;
ROWS_COMPRESSED NUMBER;
ROWS_NOCOMPRESSED NUMBER;
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
SCRATCHTBSNAME => TBSPNAME,
OWNNAME => OWNNAME,
TABNAME => TABNAME,
PARTNAME =>NULL, -- PARTITION NAME
COMPTYPE => COMTYPE,
BLKCNT_CMP =>BLKCNT_COMPRESSION, -- NUMBER OF BLOCKS COMPRESSED
BLKCNT_UNCMP =>BLKCNT_NOCOMPRESSION, -- NUMBER OF BLOCKS NON COMPRESSED
ROW_CMP =>ROWS_COMPRESSED, -- NUMBER OF ROWS COMPRESSED
ROW_UNCMP =>ROWS_NOCOMPRESSED, -- NUMBER OF ROWS NON COMPRESSED
CMP_RATIO =>COMPRESSION_RATIO, -- COMPRESSION RATIO
COMPTYPE_STR =>COMPRESSION_TYPE); -- COMPRESSION TYPE
DBMS_OUTPUT.PUT_LINE('COMPRESSION RATIO :'||COMPRESSION_RATIO);
DBMS_OUTPUT.PUT_LINE('COMPRESSION TYPE :'||COMPRESSION_TYPE);
DBMS_OUTPUT.PUT_LINE('BLOCKS COMPRESSED :'||BLKCNT_COMPRESSION);
DBMS_OUTPUT.PUT_LINE('BLOCKSUNCOMPRESSED:'||BLKCNT_NOCOMPRESSION);
DBMS_OUTPUT.PUT_LINE('ROWS COMPRESSED :'||ROWS_COMPRESSED);
DBMS_OUTPUT.PUT_LINE('ROWS UNCOMPRESSED:'||ROWS_NOCOMPRESSED);
END;

Usage of the above procedure is:

exec apun.check_compression('TABLE_OWNER','TABLE_NAME','TABLESPACE_NAME',Type of Compression to Test);

Value/Option of Type of Compression are:
TYPE: NO COMPRESSION 
VALUE: 1
TYPE:  COMPRESSION FOR OLTP
VALUE: 2

Other details can be found in the documentation at: http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_compress.htm

17:30:08 SQL> exec check_compression('APUN','COMPRESSION_TEST','SCRAP',2);
COMPRESSION RATIO :3.4
COMPRESSION TYPE :"Compress For OLTP"
BLOCKS COMPRESSED :2745
BLOCKSUNCOMPRESSED:9344
ROWS COMPRESSED :304
ROWS UNCOMPRESSED:106

PL/SQL procedure successfully completed.

As per the output it suggests that the compression ratio is 3.4
Our uncompressed table size was: 37.2 GB
Our compressed for OLTP size was: 10.8
So if we divide 37.2 by 3.4 we get 10.9 GB, which is very close to what we achieved when we actually compressed it. DBMS_COMPRESSION.GET_COMPRESSION_RATIO output is quite reliable :).


Compression can be very useful in large databases. It save disk space as well can improve performance, as oracle has to load less block in the SGA.

0 Comments: