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