Oracle Advanced Compression

Oracle Advanced Compression provides a comprehensive set of compression capabilities to help improve performance and reduce storage costs. It allows organizations to reduce their overall database storage footprint by enabling compression for all types of data: relational (table), unstructured (file), network, Data Guard Redo and backup data.

Let's try it:

- check component status

select * from v$option where parameter='Advanced Compression'; PARAMETER                     VALUE ------------------------------                 ---------- Advanced Compression               TRUE

- prepare test table

create table test_compress as select * from dba_objects; begin for i in 1..5 loop insert into test_compress select * from dba_objects; end loop; end; / SQL> select count(*) from test_compress; COUNT(*) ---------- 522642

- check tabel status

select SEGMENT_NAME, sum(EXTENT_ID) EXTENTS,sum(BLOCKS) BLOCKS, sum(BYTES)/1024/1024 MB from dba_extents where SEGMENT_NAME='TEST_COMPRESS' group by SEGMENT_NAME; SEGMENT_NAME            EXTENTS     BLOCKS      MB --------------------                            ----------              ----------              ---------- TEST_COMPRESS           2775         7680             60 select TABLE_NAME,COMPRESSION,COMPRESS_FOR from dba_tables where TABLE_NAME='TEST_COMPRESS'; TABLE_NAME                     COMPRESS        COMPRESS_FOR ------------------------------                 --------                ------------ TEST_COMPRESS                DISABLED

- use "basic" compress

create table basic_compress compress as select * from test_compress ; commit; select SEGMENT_NAME, sum(EXTENT_ID) EXTENTS,sum(BLOCKS) BLOCKS, sum(BYTES)/1024/1024 MB from dba_extents where SEGMENT_NAME like ('%COMPRESS') group by SEGMENT_NAME; SEGMENT_NAME            EXTENTS     BLOCKS      MB --------------------                            ----------              ----------              ---------- BASIC_COMPRESS              528            2304             18 TEST_COMPRESS              2775       7680         60 select TABLE_NAME,COMPRESSION,COMPRESS_FOR from dba_tables where TABLE_NAME like ('%_COMPRESS'); TABLE_NAME                     COMPRESS        COMPRESS_FOR ------------------------------                 --------                ------------ TEST_COMPRESS                DISABLED BASIC_COMPRESS               ENABLED  BASIC

We can see that after the basic compress the table size only 18 MB.

       

Let’s try compression in RMAN

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a tradeoff related to backup through put and the degree of compression afforded.

To use this option, we can run the following RMAN commands

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET; CONFIGURE BACKUP OPTIMIZATION OFF;

set compression algorithm 'MEDIUM'; run { sql 'alter system archive log current'; allocate channel ch1 device type disk format '/tmp/medium/bk_test_%U'; backup database plus archivelog;}

Backup size:     577M   /tmp/medium

set compression algorithm 'HIGH'; run { sql 'alter system archive log current'; allocate channel ch1 device type disk format '/tmp/high/bk_test_%U'; backup database plus archivelog;}

Backup size:     415M   /tmp/high

* Please noted that to use of LOW, MEDIUM and HIGH requires the Advanced Compression license.