Oracle Scratchpad

August 5, 2016

Basicfile LOBs 1

Filed under: Infrastructure,LOBs,Oracle — Jonathan Lewis @ 4:12 pm BST Aug 5,2016

I got a call to a look at a performance problem involving LOBs a little while ago. The problem was with an overnight batch that had about 40 sessions inserting small LOBs (12KB to 22KB) concurrently, for a total of anything between 100,000 and 1,000,000 LOBs per night. You can appreciate that this would eventually become a very large LOB segment – so before the batch started all LOBs older than one month were deleted.

The LOB column had the following (camouflaged) declaration:

 LOB (little_lob) STORE AS BASICFILE (
        TABLESPACE lob_ts

The database was 11gR2, the tablespace was defined with ASSM with uniform 1MB extents and a blocksize of 8KB (so the LOBs were all 2 or 3 chunks) and the undo retention time was 900 seconds. The effect of the “enable storage in row” is that the LOBINDEX didn’t have to hold any details of current LOB chunks (for in-row, the first 12 chunks are listed in the LOB Locator in the base table).

So, examining an AWR report covering the critical interval, reviewing the captured ASH data, and checking the database, a few questions came to mind:

  • With 200 GB of current LOB data in the segment, why was the segment roughly 800GB ?
  • With no need for current LOBs to be indexed, how had the LOB Index reached 500,000 blocks in size ?
  • There had been 500,000 inserts that night – so why had Oracle done 6 Billion (cached) buffer gets on the (nocache) LOB segment ?
  • Given that the LOB Segment had not changed size during the night, why had there been millions of HW enqueue wait on the inserts ?

Knowing the stuff that I did know about basicfile LOBs it seemed likely that the most significant problem was that the segment hadn’t been created with multiple freepools which, according to the very sparse (and not entirely self-consistent) documentation, exist to allow improved concurrency. So I thought I’d search the Internet for any useful information about freepools, how they worked, what impact they might have on this problem, why their absence might produce the symptoms I’d seen, and what the best course of action would be to address the problem.

Of course the “correct” solution according to MoS would be to convert from basicfile to securefile – with a strange insistence on using online redefinition, but no explanation of why a simple CTAS or alter table move is undesirable or dangerous. Unfortunately there are a couple of notes on MoS describing performance issues with “high” levels of concurrent inserts that need to be addressed by setting hidden parameters so I’m not (yet) keen on rebuilding 700GB of a production system to produce a change that might still not work quickly enough; especially since I couldn’t find anything on MoS that could quantify the time needed to do the conversion.

To my surprise I couldn’t find a single useful piece of information about the problem. The only articles I could find seemed to be little bits of cut-n-paste from the Oracle manual pages about using multiple freepools, and the best of those actually demonstrated rebuilding or changing the freepools settings on a LOB of a few megabytes. The most significant MoS note did say that the process “could be slow” and would lock the table. But surely someone, somewhere, must have tried it on a big system and had some idea of “how slow”.

In the end I had to start building some simple models and doing a few experiments to find out what happens and where the time goes and what causes the strange results and – most importantly – how freepools might help. Fortunately, following a call to the Oak Table for any ideas or links to useful documents, I got a pointer to the original Oracle patents which were enormously helpful in showing why freepools could help and why, in the wrong circumstances, you could still end up with a (slightly smaller) disaster on your hands.


To be continued …


If you’re interested, the patent numbers are: 5,999,943 and 6,061,678.  Now I just need someone to tell me the numbers for the securefile LOBs patents.



  1. Jonathan,

    At one customer site I have been asked to see why a particular error message table went from few GB of disk space to more than 200GB (if my memory serves me well) in a single night. This table has a BasicFile LOB and the number of rows of this table has not practically changed between the day before and the day after the table size explosion. After several discussions with the developer I understand that this table stores error messages by unique identifier. When a same error occurs for the same unique identifier the corresponding old rows is deleted and replaced by the new error message. We found then that during this particular night there were about millions of error messages for identical unique identifiers. This means a millions of delete/insert of the same LOB. Unfortunately the undo image of deleted LOB are not stored into the UNDO tablespace but into the LOB tablespace itself. Which explains the sudden increase of the LOB tablespace size.

    I decided to write this comment because you wrote:

    1) all LOB older than one month are deleted
    2) with 200GB of current LOB data in the segment why was the segment roughly 800GB

    In this particular customer site it was easy to backup the existing message and truncate the table. I have also asked the developer to use the de-duplicate (and compression) option of the LOB to avoid the eventual dramatic increase of LOB size because of the undo image. I almost have, by that time i.e. about one year ago, finished a draft of a blog article concerning this issue but failed to publish it.

    Best Regards
    Mohamed Houri

    Comment by hourim — August 5, 2016 @ 7:44 pm BST Aug 5,2016 | Reply

    • Mohamed,

      I think deduplication and compression apply only to securfile LOBs and they may both be part of a licenced option.

      It sounds as if the original basicfile LOB may have been using “retention” rather than “pctversion” to limit the storage of old versions – though if there were no deletes during millions of updates I guess that would explain why the segment grew so dramatically.

      Comment by Jonathan Lewis — August 5, 2016 @ 8:54 pm BST Aug 5,2016 | Reply

  2. I would love to see the “Jonathan Lewis Deep Dive on secure files”.

    Comment by Christo Kutrovsky — August 9, 2016 @ 4:25 pm BST Aug 9,2016 | Reply

  3. frankly, developer needs to know one thing: LOBs are bad, regardless of all the hype surrounding it.
    The problem is twofold:
    – handling large objects will always be a problem
    – throwing LOBs support into a polished relational database code is an awkward marriage

    Comment by laimisndLaimis — August 10, 2016 @ 6:42 am BST Aug 10,2016 | Reply

    • I wouldn’t go (quite) so far as saying LOBs are bad; I would say you have to think and test very carefully (and realistically) before you put them into production.

      As far as I know the UK passport office has a sound requirement for LOBs and one that couldn’t be met sensibly without LOBs: every passport includes a photograph and a signature so the “passport detail” table has** a set of columns for the “well-structured” data, and two LOB columns – one for the photo and one for the signature. That’s a very large number of “small” LOBs, with no updates and (probably) no deletes, and a relatively low rate of new data.

      ** I say “has” – this is the approach I suggested a couple of decades ago when I helped a company tender for the project, and I believe they implemented most of my suggestions. That was more than 20 years ago, so things may have changed since then.

      Comment by Jonathan Lewis — August 15, 2016 @ 4:25 pm BST Aug 15,2016 | Reply

  4. […] a steady state with “N” days worth of data stored. The driver behind this modelling is a problem I was asked to examine a little while […]

    Pingback by Basicfile LOBs | Oracle Scratchpad — August 18, 2016 @ 1:21 pm BST Aug 18,2016 | Reply

  5. […] Part 1 – Statement of problem […]

    Pingback by Basicfile LOBs | Oracle Scratchpad — January 26, 2017 @ 12:03 pm GMT Jan 26,2017 | Reply

  6. […] Part 1 – Statement of problem […]

    Pingback by LOB Catalogue | Oracle Scratchpad — August 20, 2022 @ 4:50 pm BST Aug 20,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: