Part 2: Oracle SecureFile LOB is better than BasicFile, isn’t it?

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 ;)

4 Responses to “Part 2: Oracle SecureFile LOB is better than BasicFile, isn’t it?”

  1. Alain says:

    BasicFile are even worst: space is NEVER re-used, even after delete.
    Let’s take a simple table x holding a few megabytes and 1 blob column xlob, 487 rows.
    Create a simple assm tablespace, 4Gb, create an ampty table xx (same structure as x + 1 number), basic file lob., and a sequence start with 1 by 1 nocache.
    begin Loop insert into xx(xlob,id) select xlob, myseq.nextval from x; commit; end loop; end;/
    until TS is full. (just fill in the table).

    Then delete from xx where id 999 rows deleted
    So we have deleted at least 2 times the content of x.
    insert into xx(xlob,id) select xlob, myseq.nextval from x;=> Error,lob segment cannot be extended.

    Using secure file, this space-re-use test works.

  2. admin says:

    Alain, thanks for visiting my blog and posting this comment. As you can see in the title, this is going to be a lengthy and – I hope – never ending series of posts regarding which one is better and where ;) I just need to get some more time for research ;)

  3. admin says:

    Hi Alain, part 3 http://jakub.wartak.pl/blog/?p=798 has been published , be sure to see the results ;)

  4. Donat Callens says:

    @Alain You have not mentioned the retention/pctversion parameter of your basicfile LOB column for your test table. It will thus be the default value of RETENTION, if the database is in the default in automatic undo mode. When set to RETENTION, this parameter will ensure your deleted LOB segments are kept for as long as the UNDO_RETENTION time… You will thus have to wait a little longer to see them being put on the free list.

Leave a Reply