Re: PCTFREE/PCTUSED for table with updates to LOBs?

From: Jonathan Lewis <>
Date: Wed, 14 May 2008 21:19:08 +0100
Message-ID: <>

"neil kodner" <> wrote in message

I'm using on Solaris. Is out-of-line CLOB storage a good option considering that the avg(length() of my clobs are 364 and 2131 charcters? Additionally, in a 2.17M row table(682872 8k blocks), clob ltr_xml is null 854k rows, and content is null in 2.08M rows. Understanding that this isn't the best table design(and outside of my control), is this something I should be altering once I migrate the db?

That's a little difficult to answer.

Looking at the table - one id and 3 versions of the "letter" it looks like the design has already 'put the letter out of line' to avoid bulking up a table of more regular data. If this is the case, then you probably want to avoid extra random I/Os by keeping the lobs in line, rather than deliberately putting them out of line.

It's then a question of picking a good value for PCTFREE for this table - based on the way you insert, update and delete rows - so that you don't get excessive row chaining.

This might be a case where you choose to waste space in a block to make sure that most calls to this table read only one block. It certainly looks like a case where you might consider putting the table into the RECYCLE pool - or even picking a different size data block to get a totally isolated buffer cache and an ideal block size.

There's an example of a script on my blog I used to analyze lob sizing for a vaguely related problem - it might be useful to find out how that average of 5K spreads out in (for example) 2Kb steps:


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Wed May 14 2008 - 15:19:08 CDT

Original text of this message