Basicfile LOBs

I wrote a short series a little while ago about some of the nasty things that can happen (and can’t really be avoided) with Basicfile LOBs and recently realised that it needed a directory entry so that I didn’t have to supply 6 URLs if I wanted to point someone to it; so here’s the catalogue:

• Part 1 – Statement of problem
• Part 2 – Introduction to LOB Indexes and Freepools
• Part 3 – Design strategies and testing
• Part 4 – First signs of contention problems
• Part 5 – Losing space in the LOB data segment
• Part 6 – Losing time due to asymmetrical insert/delete patterns

At some stage I may also write a similar series about Securefile LOBs – because you do hit problems if you have a system that does a lot of work modifying a LOB segment whether it’s Basicfile or Securefile, and you need a strategy for damage limitation.

Footnote

At the time of creating this catalogue I’ve had an SR open with Oracle for about 4 months on the problem that triggered this series, basically asking if there was a way to limit the number of chunks that could be taken off the reusable part of the index. So far I haven’t had an answer to that question; however the client was able to switch the table into a partitioned table and now drops old partitions rather than deleting old data.

The problem is now recorded against version 12.1.0.2 as bug 22973766 so I’ve closed the SR and will keep an occasional eye on bug number:

Bug 22973766 : LARGE DELETE/INSERT CYCLE FOR LOBS AND EXTENDED VARCHARS WASTES A LOT OF SPACE

Update on bug:  (see comments below): it turned out that the bug had nothing to do with the problem raised in the original SR; but I didn’t look too closely when I got the message, and I no longer remember the SR number.

Update (Jan 2021)

Logging on to MOS a little while ago I found that the original SR for this problem had been flagged up as recently changed. The SR summary reported “14 days ago” and the most recent entry showed 8+ hours ago. I’ll guess that I hadn’t received the usual email message about this because the SR had my old email address attached.

The gist of the update was that the bug had been fixed and the relevant bug number was 27108036. It’s not a public bug, but there is a descriptive note:  Doc ID 27108036.8 Excessive reads of LOB index blocks during Basicfile LOB block space reclaim which says the bug is fixed in 19.1 with the introduction of a new parameter: _reclaim_lob_index_scan_limit which seems to default to zero (probably meaning no limit) with a suggested value of 128.  Backports may be made available for older versions on request, but will make use of one of the “spare” parameters.