Archive for April, 2014

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

Thursday, April 24th, 2014

OK, so far in our previous parts/tests regarding Oracle LOBs we had:

  • part 1 – showing how SecureFiles are fairly superior on DB version against BasicFiles in terms of space consumed and elapsed processing times when processing rather very small rows (most of them inline)
  • part 2 – showing how buggy SecureFiles were as a new feature, and actually how much space they actually wasted (on some legacy version

Part 3 is going to focus on showing which is better for storing rows/LOBs that are too big to fit into row/table segment (~3900 bytes limit) and must be technically stored in separate LOB segment. The enviorniment is very similiar to the one from Part 1, basically OLTP, 4 concurrent jobs doing INSERTs to single non-partitioned table in PL/SQL loop doing INSERT LOB + DELETE LOB with ID-16 (moving window for each job), DB version is on, LOB chunk size is 8k, tablespace block size is also 8k, LOB setting is to perform CACHING. The storage underneath the DB is on ram disk (tmpfs to be exact), every time tablespace is dropped and recreated, everything is automated, including AWR snapshot collections. The main difference is that now we are using a much larger LOB sizes (over 4k to be exact) *AND* we changed workload to now prefill the table and LOB with garbage data, then delete it without coallescing the LOB segment itself. The last statement as you will see is ciritical , and shows typical usage pattern where data is removed (e.g. queues).

The results are below, first time elapsed BasicFile vs SecureFile:

Second, space efficency:

As you can simply see – the overall performance benefit from BasicFiles – causes huge storage waste. Also workload profile in this particular test run is much different between SecureFiles and BasicFiles , the best image is the difference in AWR snapshots for the Top 5 events for workload generating between 6k and 12k LOBs:

Click to see more details

Later analysis reveals that majority of “buffer busy waits” are spent on “free list” managment itself. Armed with this knowledge (, securefile LOBs, buffer busy waits, free lists) we can start looking for more information, and we find a nice horror stories:

At least on SecureFile LOBs are still not working as fast as BasicFile but with added benefit of reusing space (much better than on some old PSUs e.g. One can hit an extreme “buffer busy waits” on SecureFile which might require rebuilding the table with hidden parameter. It is written that this is fixed in releases. In Part 4 or Part 5 I’m going to see how behaves under the identical workload.