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