Compressing LOB segments with securefiles

After Oracle Database 11G we have a new storage option for the LOB segments which is securefiles. By enabling securefiles LOB segments can be compressed significantly. After I have faced an unusually extending tablespace (approximately 30G per day) which may halt the database because of this unplanned extend, I have start digging into the problem and find out this LOB column which hold the unstructured HTML data inside it, its size was more than 350Gb lying inside a total of 600Gb database which means more than half of the database is only this one column. This is just unacceptible. In the following example you can find the same scenerio on the test system. Here the uncomressed LOB column holds 22Gb of space.

select 
  OWNER, 
  TABLE_NAME, 
  COLUMN_NAME, 
  SEGMENT_NAME, 
  INDEX_NAME 
from 
  DBA_LOBS 
where 
  TABLE_NAME='AUD_PRODUCT_BIG_DATA';

/*
OWNER       TABLE_NAME            COLUMN_NAME   SEGMENT_NAME                INDEX_NAME
--------    --------------------  -----------   -------------------------   ------------------------
SCH_DMALL   AUD_PRODUCT_BIG_DATA  DESCRIPTION   AUD_PRODUCT_BIG_DATA_CLOB   SYS_IL0000126287C00004$$
*/

select bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000126287C00004$$';  
--22GB

After moving the LOB column with the storage option of "SECUREFILE" and "MEDIUM COMPRESS" option the size of the LOB segment decreased to approx. 833Mb. More than 1/25 compression which is as valuable as gold in this production database.

ALTER TABLE sch_dmall.aud_product_big_data move lob (description) store
AS
  SECUREFILE aud_product_big_data_clob (TABLESPACE TS_AUD_LOB_SEGMENT COMPRESS MEDIUM) parallel 4;

select bytes/1024/1024 from dba_segments where segment_name='AUD_PRODUCT_BIG_DATA_CLOB';  
--833.125

There are three levels of compression which is LOW MEDIUM and HIGH which defines the compression level. Of course the higher the level gets, the more CPU power you need to spend. here I used MEDIUM to see the result and it is pretty convincing to not to change the setting.


References:

http://blog.trivadis.com/b/mathiaszarick/archive/2012/03/05/lob-compression-with-oracle-strange-multiple-physical-reads.aspx

http://www.oracle-base.com/articles/11g/secure-files-11gr1.php