OK, so far in our previous parts/tests regarding Oracle LOBs we had:
- part 1 – showing how SecureFiles are fairly superior on DB version 11.2.0.3.5 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 11.2.0.2.3)
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 11.2.0.3.8, 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 (11.2.0.3.8, securefile LOBs, buffer busy waits, free lists) we can start looking for more information, and we find a nice horror stories:
- High Buffer Busy Waits – Securefiles
- “Securefiles DMLs cause high ‘buffer busy waits’ & ‘enq: TX – contention’ wait events leading to whole database performance degradation (Doc ID 1532311.1)”, which is mentioning increasing “_securefiles_concurrency_estimate” (default at 12) and statements like those “LOBs are mainly designed for “once inserted + many times queried”. They have not been designed for such great number of UPDATEs.”
- “Bug 13593640 : DATABASE IS SLOW DUE TO BUFFER BUSY WAITS ON SECUREFILE FOR INSERTS – an exact match for this workload mentioning freelists! still not fixed in 11.2.0.3.x?
- “Bug 17479510 : SECUREFILES DMLS CAUSE HIGH ‘BUFFER BUSY WAITS’ & ‘ENQ: TX – CONTENTION’ – still not fixed in 11.2.0.3.x?
- threads like this http://www.freelists.org/post/oracle-l/storing-lobs-as-secure-files-any-gotchas
- and this https://groups.google.com/forum/#!topic/comp.databases.oracle.server/9982gQfTGDk
Summary:
At least on 11.2.0.3.8 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. 11.2.0.2.3). 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 11.2.0.4.x releases. In Part 4 or Part 5 I’m going to see how 11.2.0.4.x behaves under the identical workload.
-J.