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

According to Oracle Technical Marketing SecureFiles are superior in every way to the older BasicFile LOB implementation. Part1 of this articles is going to try to prove it for strictly OLTP like workloads (having LOB column as requirement for application that is doing a lot of heavy/duty short & quick transaction requiring to store data that VARCHAR2 type is not capable of). This series is not going to concentrate on the LOBs used for anything bigger than 5300 bytes. We also want to take profit from the LOB inline storage option that stores LOB data together with the table data. The benchmarks were performed on 2 sockets 8 cores Intel Xeon box running Oracle 11.2.0.3.5 with some local RAID1 storage. Before each iteration shared pool and buffer cache were flushed, log switch forced (BTW: DB running without archive redo logs), dedicated tablespaces recreated, workload has been parallel (like in OLTP) using 8 jobs hitting single table creating (intended) contention, each run has been run at least 3 times to guarantee at least some kind of “repeatability”. The table has not been partitioned and it looked like this:

create table t_blob ( id number primary key, l1 clob ) tablespace blobs
lob ( l1 ) store as &blob_type (
tablespace blobs &inrow storage in row chunk &chunk &cache &logging
)
;

there are several variables set like this:

  • &inrow – always “enable” for purposes of this test
  • &chunk – always set for 4k for purposes of this test
  • &cache – always set to CACHE
  • &logging – always set
  • &blob_type – securefile, basicfile
  • tablespace – the tested values were 4k and 8k, each time setting db_buffer_cache for the appriopiate buffer to 1.5GB
  • payload for LOB data – always generated randomly using dbms_random package, with with length of minimum from 300 bytes (incremented with step of 2000 bytes) up to 5300 bytes (incremented also by 2000)

The results are below, first space efficiency BasicFile vs SecureFile:

Second, elapsed time of execution comparing BasicFile VS SecureFile:

But that’s not the end, stay tuned for part 2 where I’ll show that the world is not so beautiful as it looks like :)

-J.

Comments are closed.