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 144Now 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: 56Note 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.