So we start an example from real life of using SecureFiles (without deduplication and without encryption) on large scale system (say no-no to a “laptop database” tests) version Oracle non-RAC 11.2.0.2.3 (yup, a pretty old release but the DB has been already migrated since gathering this info):
primary:sys@XXXX> desc YYYYYYYY.magic_table;
Name Null? Type
[..]
HEADERS BLOB
PAYLOAD BLOB
primary:sys@XXXX>
primary:sys@XXXX> select num_rows, blocks, AVG_ROW_LEN from dba_tables where table_name='MAGIC_TABLE';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
3791737 891649 204
The table is on 8kB tablespace, so our MAGIC_TABLE with 2 LOB columns is taking ~ 891649*8192/1024/1024/1024 =~ 6.8GB. We verify our math to real usage as shown by dba_segments:
primary:sys@XXXX> select bytes/1024/1024/1024 mb from dba_segments where segment_name='MAGIC_TABLE';
MB
----------
6,9375
The value is pretty close to the standard Oracle theory, so we are ok so far. We proceed with analysis what uses space in that table (we are going to concentrate on single LOB for PAYLOAD column for simplicity):
primary:sys@XXXX> SELECT CASE WHEN DBMS_LOB.GETLENGTH(PAYLOAD) > 3964 THEN 'Out-of-line' ELSE 'Inline' END x, COUNT(*) FROM YYYYYYYY.magic_table GROUP BY CASE WHEN BMS_LOB.GETLENGTH(PAYLOAD) > 3964 THEN 'Out-of-line' ELSE 'Inline' END;
X COUNT(*)
----------- ----------
Inline 3100800
Out-of-line 732826
primary:sys@XXXX>
FACT1: so 24% of rows have PAYLOAD LOB stored out-of-line, out-of-line probably means different place, let’s check it.
primary:sys@XXXX> SELECT table_name, column_name, segment_name, round(a.bytes/1024/1024,1) mb FROM dba_segments a
JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name LIKE 'MAGIC_TABLE%';
TABLE_NAME COLUMN_NAME SEGMENT_NAME MB
------------------------------ -------------------- ------------------------------ ----------
MAGIC_TABLE HEADERS SYS_LOB0000099004C00007$$ ,1
MAGIC_TABLE PAYLOAD SYS_LOB0000099004C00008$$ 1769095
primary:sys@XXXX>
FACT2: this 24% of rows takes 1769095 MB (sic!) which means 1.72TB consumed for just 1/4 of rows.
primary:sys@XXXX> select table_name, tablespace_name, chunk, pctversion, retention, in_row, securefile, retention from dba_lobs where segment_name = 'SYS_LOB0000099004C00008$$';
TABLE_NAME TABLESPACE_NAME CHUNK PCTVERSION RETENTION IN_ SEC RETENTION
------------------------------ ------------------------------ ---------- ---------- ---------- --- --- ----------
MAGIC_TABLE LOB4K 4096 YES YES
primary:sys@XXXX>
primary:sys@XXXX>
FACT3: 24% of rows have data stored in separate tablespace dedicated for LOBs with 4kB blocksize.
primary:sys@XXXX> SELECT CASE
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 3964 THEN '< 4kB'
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 6*1024 THEN '<6kB'
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 7*1024 THEN '<7kB'
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 8*1024 THEN '<8kB'
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) <16*1024 THEN '<16kB'
ELSE 'even bigger'
END x,
COUNT(*) cnt
FROM YYYYYYYY.magic_table GROUP BY CASE
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 3964 THEN '<4kB'
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 6*1024 THEN '<6kB'
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 7*1024 THEN '<7kB'
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 8*1024 THEN '<8kB'
WHEN DBMS_LOB.GETLENGTH(PAYLOAD) <16*1024 THEN '<16kB'
ELSE 'even bigger'
END; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
X CNT
----------- ----------
<7kB 76
<4kB 3215841
<6kB 701916
primary:sys@XXXX>
We can double-check Oracle on it’s math here. There is not a single row in that table that has LOB entry bigger than 7kB, still everything is stored out-of-line (technically bigger than something like 3600-3900 bytes which is max size for inline LOBs) in LOB segment named SYS_LOB0000099004C00008$$ is taking 1769095 MB (sick!).
So actually 701916+76 rows potentially consume 1769095 MB in datafiles, which gives us 2.5MB of waste per row. This table is really heavy duty OLTP table which means really lots of INSERT/UPDATE/DELETE concurrent stuff, I’m not even sure if the application responsible, is doing updates against they PAYLOAD column, from my analysis it is just inserts row once (with LOB via JDBC) and sometime later deletes it. All I can say about the application in scope is the fact that it is “database interdependent”.
Let’s now calculate theory behind space calculation (A.K.A what should be really used if there would be no overhead). Space that should be used for PAYLOAD column stored in LOBSEGMENT should be:
- 3215841 rows stored inside tablespace with the rest of data; table segment for MAGIC_TABLE already stores 6.7GB which contains data for those 3215841 rows, so we can skip it
- (701916+76) rows stored in separated tablespace LOB4K, which means that for rows bigger than 3600..3900 this translates to at least 1 CHUNK (4kB) and maximum up to 2 CHUNKs (8kB) because we do not have anything bigger than 7kB; we can assume even worse scenario that single CHUNK(4kB) would require up to 2 blocks in 4kB tablespace due to some overheads. So even in the worst case we have (701916+76)*4096*2/1024/1024/1024 =~ 5.36GB
So what we really have is 1727 GB used to store 5.36GB. Closing this part i would like to give you an idea how hard SecureFile LOB processing is for Oracle, they seem to be struggling with it for couple years. The list of bugs for 11.x series include:
- Bug 14849553 : MOVE LOB SLOWER FOR SECURE FILES THAN BASIC FILES
- Bug 13593640 : DATABASE IS SLOW DUE TO BUFFER BUSY WAITS ON SECUREFILE FOR INSERTS
- Bug 10197852 : SLOW PERFORMANCE ACCESSING SECUREFILE BLOBS WITH XA CONNECTIONS
- Bug 9792464 : DEGRADED PERFORMANCE INSERTING LARGE BLOB WITHIN AN XA TRANSACTION
- Bug 13005760 : BUG:EXCESSIVE ENQ TX-CONTENTION WAIT ON SECUREFILE
- Bug 8620054 : LOBS WASTE STORAGE USING LOB TYPE DISABLE STORAGE IN ROW
- Bug 14723894 : SECUREFILE BLOB NOT REUSING SPACE
- Bug 8487668: RETENTION NONE NOT HONORED CORRECTLY IN SECUREFILES
- Bug 10097920: SPACE NOT RELEASED USING SECUREFILES
- Bug 8603465: AGGRESSIVE ALLOCATION OF SPACE IN SECUREFILES
- Bug 12582664: DEDUPLICATION WITH SECUREFILES NOT WORKING AS EXPECTED
- Bug 12588744: SECUREFILE WASTE STORAGE WHEN SPECIFY INITIAL EXTENT
- Bug 12656535: ORA-1555 DURING DIRECT PATH READ OF SECUREFILE LOB
- Bug 12662040: SECUREFILE LOB SEGMENT KEEPS GROWING IN CASE OF PLENTY OF FREE SPACE
I’m certain you could find even more. Even with the most recent 11.2.0.3.x SecureFilles are still being patched, as they fixed bug 14176879 in 11.2.0.3.6 in Critical Patch Updates April 2013. Bug 14176879 stands for “Extensive growth of Securefiles segments undergoing updates”.
To make it even more appealing to use SecureFiles (irony intended), here’s the next real the BIG THING: in section “Using ALTER TABLE with SecureFiles LOBs” in official documentation, it is written: “Note that the SHRINK option is not supported for SecureFiles LOBs”. Yeah, now you can scream 
