Oracle 11g Advanced Compression

Although table compression was introduced in Oracle 9i and 10g, it was aimed more at bulk load operations for data warehouse environments. The overhead associated with compression and uncompression made it unsuitable for OLTP type shops.

In Oracle 11g, Advanced Compression (which is an additional licensed feature) includes OLTP compression capabilities as well as compression of unstructured data like images and text with Secure Files and also direct compression of the export dump file generated by Data Pump which is also directly read without any uncompress while doing an import.

The compression feature has been greatly enhanced so as to remove any overhead associated with dealing with compressed data while performing any DML activities.The COMPRESS FOR ALL OPERATIONS keyword needs to be used for enabling OLTP data compression.

While Oracle does claim compression ratios of 3:1, we were able to see clearly 2:1 ratios in terms of storage reductions and no real performance overhead while performing deletes and inserts on compressed data as shown in the example below.

So this feature could provide us both Storage as well as Performance gains due to the reduced number of blocks which will need to be read while performing I/O.

UNCOMPRESSED DATA

SQL> create table myobjects
2 as select * from all_objects;

Table created.

SQL> select sum(bytes)/1048576 from user_segments where segment_name=’MYOBJECTS’;

SUM(BYTES)/1048576
——————
21

SQL> insert into myobjects
2 select * from all_objects;

56261 rows created.

Elapsed: 00:00:09.39

SQL> select count(*) from myobjects where object_type=’TABLE’;

COUNT(*)
———-
444

Elapsed: 00:00:00.66

SQL> delete myobjects;

168780 rows deleted.

Elapsed: 00:00:12.22

COMPRESSED DATA

SQL> create table myobjects_compress
2 compress for all operations
3 as select * from all_objects;

SQL> select sum(bytes)/1048576 from user_segments where segment_name=’MYOBJECTS_COMPRESS’;

SUM(BYTES)/1048576
——————
9

SQL> insert into myobjects_compress
2 select * from all_objects;

56261 rows created.

Elapsed: 00:00:09.08

SQL> select count(*) from myobjects_compress where object_type=’TABLE’;

COUNT(*)
———-
444

Elapsed: 00:00:00.21

SQL> delete myobjects_compress;

168783 rows deleted.

Elapsed: 00:00:10.96

Compression of Data Pump Exports

While doing an export, we can use the parameter COMPRESSION=ALL (other options are DATA_ONLY or METADATA) and we see that the size of the dumpfile has reduced very significantly while using this compression feature.

The uncompressed dumpfile was 113 MB while the compressed dumpfile was just 13 MB. This feature can be very useful when we need to take a large export on disk but are limited by disk space. The earlier methods required us to use a pipe while performing an export, but we had to then uncompress the file while performing the import as well which is a very costly operation in terms of time and resources.

$ expdp directory=data_file_dir dumpfile=uncompress.dmp schemas=SH compression=ALL

middba1:/u01/oracle/product/11.1.0/db/demo/schema/sales_history> ls -l *.dmp
-rw-rw—- 1 oracle dba 113610752 Sep 3 12:25 uncompress.dmp
-rw-rw—- 1 oracle dba 13426688 Sep 3 12:21 compress.dmp