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

"neil kodner" <nkodner_at_gmail.com> wrote in message news:5b6e41fd-0d59-4e84-a43e-e4d234c3b762_at_d45g2000hsc.googlegroups.com...

I'm using 10.2.0.3 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:

http://jonathanlewis.wordpress.com/2007/05/11/lob-sizing/

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html