DBMS_COMPRESSION is a package used to manage the Oracle Database table compression feature. This feature lets you compresses data by eliminating duplicate values in disk pages. As database grows in size, table compression saves resources, not only disk storage but also reduces memory use in the buffer cache. First introduced in 11g R2, this package has been evolving, and some very useful enhancements were introduced now in 12c release.

Let’s create an environment to play with first. The following statements will create a user and a table (using a dedicated tablespace) to be used a sample:

SQL> CREATE USER z_test IDENTIFIED BY welcome1;

User created.

SQL> GRANT CONNECT, RESOURCE TO z_test;

Grant succeeded.

SQL> CREATE TABLESPACE bigdata
  2    DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Tablespace created.

SQL> ALTER USER z_test QUOTA UNLIMITED ON bigdata;

User altered.

SQL> CONN z_test/welcome1
Connected.
SQL> CREATE TABLE bigtable
  2    TABLESPACE bigdata
  3  AS
  4  SELECT rownum id, a.*
  5    FROM all_objects a
  6   WHERE 1 = 0;

Table created.

SQL> ALTER TABLE bigtable NOLOGGING;

Table altered.

Now populate it with 1000000 records using the following procedure:

SQL> DECLARE
  2    l_cnt  NUMBER;
  3    l_rows NUMBER := 1000000;
  4  BEGIN
  5    INSERT /*+ APPEND */ INTO bigtable
  6    SELECT rownum, a.*
  7      FROM all_objects a;
  8  
  9    l_cnt := SQL%ROWCOUNT;
 10  
 11    COMMIT;
 12  
 13    WHILE (l_cnt < l_rows) LOOP
 14  
 15       INSERT /*+ APPEND */ INTO bigtable
 16       SELECT rownum+l_cnt, owner, object_name, subobject_name, object_id, 
 17               data_object_id, object_type, created, last_ddl_time, 
 18               timestamp, status, temporary, generated, secondary, 
 19               namespace, edition_name, sharing, editionable, 
 20               oracle_maintained
 21         FROM bigtable
 22        WHERE rownum <= l_rows-l_cnt;
 23  
 24       l_cnt := l_cnt + SQL%ROWCOUNT;
 25  
 26       COMMIT;
 27    END LOOP;
 28  
 29    COMMIT;
 30  END;
 31  /

PL/SQL procedure successfully completed.

Let’s query dba_segments catalog view to find out how much disk storage is allocated to BIGTABLE table.

SQL> CONN / as sysdba
Connected.
SQL> SELECT segment_name, SUM(bytes)/1024/1024 MB
  2    FROM dba_segments
  3   WHERE segment_name = 'BIGTABLE'
  4   GROUP BY segment_name;

SEGMENT_NAME                                     MB                               
---------------------------------------- ----------
BIGTABLE                                        144

Now in 12c, you can find the estimated rows per block when using dbms_compression package. To achieve this goal, the GET_COMPRESSION_RATIO subprogram will be used. The DBMS_COMPRESSION package uses different numeric constants to describe each type of possible compression, from “1” that represents “No Compression” to 65536 that correspond to “In-Memory high compression level optimizing for capacity”.

Execute the following procedure using “Advanced Compression Level” (comptype=2) to define an initial baseline:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    v_blocks_comp   PLS_INTEGER;
  3    v_blocks_uncomp   PLS_INTEGER;
  4    v_rows_comp   PLS_INTEGER;
  5    v_rows_uncomp   PLS_INTEGER;
  6    v_compress_ratio  NUMBER;
  7    v_compress_type   VARCHAR2(32767);
  8  BEGIN
  9    DBMS_COMPRESSION.get_compression_ratio (
 10     scratchtbsname  => 'BIGDATA',
 11     ownname   => 'Z_TEST',
 12     objname   => 'BIGTABLE',
 13     subobjname   => NULL,
 14     comptype   => 2,
 15     blkcnt_cmp   => v_blocks_comp,
 16     blkcnt_uncmp   => v_blocks_uncomp,
 17     row_cmp   => v_rows_comp,
 18     row_uncmp   => v_rows_uncomp,
 19     cmp_ratio   => v_compress_ratio,
 20     comptype_str   => v_compress_type,
 21     subset_numrows  => DBMS_COMPRESSION.comp_ratio_minrows
 22    ) ;
 23  
 24    dbms_output.put_line('OUTPUT: ');
 25    dbms_output.put_line('Estimated Compression Ratio: ' 
 26                          || v_compress_ratio);
 27    dbms_output.put_line('Blocks used - compressed sample: ' 
 28                          || v_blocks_comp);
 29    dbms_output.put_line('Blocks used - uncompressed sample: ' 
 30                          || v_blocks_uncomp);
 31    dbms_output.put_line('Rows in a block - compressed sample: ' 
 32                          || v_rows_comp);
 33    dbms_output.put_line('Rows in a block - uncompressed sample: ' 
 34                          || v_rows_uncomp);
 35  END;
 36  /

First results will be:

OUTPUT: 
Estimated Compression Ratio: 3.5
Blocks used - compressed sample: 645
Blocks used - uncompressed sample: 2279
Rows in a block - compressed sample: 200
Rows in a block - uncompressed sample: 56

Note that using this level of compression you can reduce blocks used from 2279 (uncompressed) to 645 (compressed) because you have 200 rows in a block (compressed) instead of just 56 rows in a block (uncompressed).

Execute again the above procedure, but this time change “comptype” from 2 to 4 (“High compression level for query operations”).

Change the following line in the PL/SQL code:


...
 14     comptype   => 4,
...

Run again the PL/SQL procedure and check new results:

.....
Compression Advisor self-check validation successful. select count(*) on both   
Uncompressed and EHCC Compressed format = 1000000 rows                          
OUTPUT:                                                                         
Estimated Compression Ratio: 21.6                                               
Blocks used - compressed sample: 819                                            
Blocks used - uncompressed sample: 17737                                        
Rows in a block - compressed sample: 1221                                       
Rows in a block - uncompressed sample: 56

Note how compression ratio increases dramatically from 3.5 to 21.6 and in terms of rows, now you have 1221 rows in each compressed block.

The list of available available compression type can be found in the official Oracle documentation here.

This subprogram is especially useful when you are considering purchasing Exadata or Oracle storage hardware (Pillar or ZFS) that allows Hybrid Columnar Compression (HCC), and you need to know an estimate of the compression ratio that could take place with that type of compression.

Link to script that contains the examples in this post.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Comment