0 Replies Latest reply on Jul 12, 2018 11:46 AM by 3156144

    advanced compression of securefile blob (image files) Does not perform any compression

    3156144

      Hello,

       

      DB Version:12.1.0.2

      Exadata Machine x5

       

      we have one huge Table part of oracle web content schema that Store Secure file BLOB (scanned image with .tif format),current table size is 16TB,we have performed the following test:

       

      1-Export million row of this table (size was 5GB)

      2-Enabled securefile advanced compression by performing table redefinition

      3-Check size of BLOB,size did Not change

       

      Below are the test results:

       

      Before compress:

       

      --size of lob:

      ###########

      SQL> select bytes/1024/1024/1024 from dba_segments where segment_name ='FILESTORAGE_BFILEDATA' and owner='DEV_OCS';

       

      BYTES/1024/1024/1024

      --------------------

            5.61444092

       

      --size of table

      SQL> select bytes/1024/1024 from dba_segments where segment_name ='FILESTORAGE' and owner='DEV_OCS';

       

      BYTES/1024/1024

      ---------------

               32

       

       

      compress medium:

      ####################

       

      SQL> select bytes/1024/1024/1024 from dba_segments where segment_name ='FILESTORAGE_BFILEDATA' and owner='DEV_OCS';

       

      BYTES/1024/1024/1024

      --------------------

            5.59100342

       

      SQL> select bytes/1024/1024 from dba_segments where segment_name ='FILESTORAGE' and owner='DEV_OCS';

       

      BYTES/1024/1024

      ---------------

               32

       

       

      conpress high:

      ##################

       

      SQL> select compression from dba_lobs where table_name='FILESTORAGE' and owner='DEV_OCS';

       

      COMPRE

      ------

      HIGH

       

      SQL> select bytes/1024/1024/1024 from dba_segments where segment_name ='FILESTORAGE_BFILEDATA' and owner='DEV_OCS';

       

      BYTES/1024/1024/1024

      --------------------

            5.58807373

       

      SQL> select bytes/1024/1024 from dba_segments where segment_name ='FILESTORAGE' and owner='DEV_OCS';

       

      BYTES/1024/1024

      ---------------

               32

       

      We also ran compression advisor

      Number of blocks used (compressed)     : 75140

      Number of blocks used (uncompressed)     : 75134

      Compression ratio             : .9   <<<<< compression ratio

      Compression type             : "Compress High"

      Number of rows in a block (compressed)     : 5029

       

      Does above ration means that size should be reduce to 4.5 GB ?

      from previous old thread PDF's storing in Compressed BLOB Securefile doesn't save space

       

      "

      The reason you are not seeing any compression is because the SecureFiles tries to estimate how much gain you are going to get by compression and if is below is a given threshold (20%), we don't bother compressing the file assuming the storage gains do not justify the CPU cost. For this particular files, GZIP level provides you about 7% compression, which is lower than the minimum threshold of 20%.

       

      Please note however if if this file were get larger during subsequent steps, you might see the compression suddenly kicking in"

       

      is there any parameter that we can use to let compression kick in ?

      if we increase the sample size we could see different results?

      was there any documented test case made for Images store as securefile that show better results ?

       

      Best Regards,

      Dalia