Re: PCTFREE/PCTUSED for table with updates to LOBs?
Date: Wed, 14 May 2008 21:19:08 +0100
"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:
-- 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.htmlReceived on Wed May 14 2008 - 15:19:08 CDT