Tuesday, June 11, 2013

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


7 comments:

  1. I want to add one line SQL to prevent physical I/O overhead of LOB segments.
    SQL> ALTER TABLE aud_product_big_data_clob MODIFY LOB (DESCRIPTION) (CACHE READS);
    Sincerely yours.

    ReplyDelete
  2. I get a lot of great information from this blog. Thank you for your sharing this informative blog. I have bookmarked this page for my future reference. Recently I did oracle certification course at a leading academy. If you are looking for best Oracle Training in Chennai visit FITA IT training and placement academy which offer PL SQL Training in Chennai.

    ReplyDelete
  3. Its really awesome blog..If anyone wants to get Software Testing Course in Chennai visit FITA IT academy located at Chennai. Rated as No.1 Software Testing Training Institutes in Chennai

    Regards.....

    Testing Training in Chennai | QTP Course in Chennai

    ReplyDelete

  4. The information you posted here is useful to make my career better keep updates...If anyone want to get Cloud Computing Course in Chennai, Please visit FITA academy located at Chennai. Rated as No.1 Cloud Computing Training Institutes in Chennai

    ReplyDelete
  5. I have read your blog and i got a very useful and knowledgeable information from your blog.You have done a great job . If anyone want to get Salesforce Admin Training in Chennai, Please visit FITA academy located at Chennai Velachery. Rated as No.1 Salesforce Training Institutes in Chennai.

    ReplyDelete
  6. Pretty Post! It is really interesting to read from the beginning & I would like to share your blog to my circles for getting awesome knowledge, keep your blog as updated.
    Regards,
    Oracle Training in Chennai|Oracle DBA Training in Chennai|Oracle Training Institutes in Chennai

    ReplyDelete