RE: Anyone using multi-block sizes for their databases

The documented design goal for this feature is said to be support of transportable tablespaces. Having ported Oracle and run audited TPC-C for many years for a very high end SMP company, I can say that the real impetus for this feature was TPC-C. Same as the recycle pool. Same as hash clusters. That is not to say that these features are worthless for production. Quite to the contrary. It is, however, important to get a grasp of the origin of a lot Oracle features. At least in my book.

So, why TPC-C for variable block sizes? Nearly 27% of the IO load is to one table; customer. The rows are just a smidge under 2K... Recycle pool? Well, the TPC-C workload seldom revisits customer rows, so why burdon the SGA with the blocks? No, setting NOCACHE on the table doesn't do anywhere near the same cache protection as a recycle pool. Plus, each pool has its own set of cache buffers lru latches. A good thing, really.

ASM has been a feature for ~18months and has never been used in an audited TPC benchmark (read FDRs). Features can be used 6 months before they are in a production release as per the TPC bylaws.

crawling back into my hole...

Kevin Closson
Chief Architect, Database Solutions
PolyServe

>>>-----Original Message-----
>>>From: oracle-l-bounce_at_freelists.org
>>>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Murching, Bob
>>>Sent: Thursday, July 21, 2005 11:34 AM
>>>To: 'lex.de.haan_at_naturaljoin.nl';
>>>'juancarlosreyesp_at_gmail.com'; 'Oracle-L_at_freelists.org'
>>>Subject: RE: Anyone using multi-block sizes for their databases
>>>
>>>I more or less agree with the below sentiment. We moved to
>>>multiple block sizes pretty aggressively out of the gate
>>>with 10g, then quickly ended up cutting our losses and going
>>>back to an database-wide standard. It was mostly a
>>>manageability issue - with multiple block sizes, now we had
>>>one more thing to worry about: tuning the different buffer
>>>cache pools. Once you take a few moments to look at the
>>>capability and its primary weakness, you quickly realize
>>>that it's only a matter of time before Oracle auto-allocates
>>>RAM to the different buffer cache pools. I think we'll be
>>>taking advantage of multiple block sizes if and when that
>>>functionality becomes available. Until then, "just pick 8k
>>>or 16k, then go back to tuning the SQL and building the
>>>right data model" remains the most effective mechanism for tuning....
>>>
>>>-----Original Message-----
>>>From: Lex de Haan [mailto:lex.de.haan_at_naturaljoin.nl]
>>>Sent: Thursday, July 21, 2005 12:52 PM
>>>To: juancarlosreyesp_at_gmail.com; Oracle-L_at_freelists.org
>>>Subject: RE: Anyone using multi-block sizes for their databases
>>>
>>>Juan,
>>>
>>>with all due respect, I believe this is *not* a good advice.
>>>I actually think it is a *bad* advice. you try to achieve
>>>something with the wrong technique, at a way too high price.
>>>
>>>let me first say that the only *intended* reason for
>>>multiple blocksizes is to make transportable tablespaces
>>>more flexible. any other reason might have its merits, but
>>>should be considered with caution.
>>>
>>>the biggest disadvantage of a segmented cache is that free
>>>space gets segmented too, obviously -- typically leading to
>>>much more memory wastage than with a single unsegmented buffer cache.
>>>
>>>kind regards,
>>>
>>>Lex.
>>>
>>>------------------------------------------------------------------
>>>Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html
>>>------------------------------------------------------------------
>>>-----Original Message-----
>>>From: oracle-l-bounce_at_freelists.org
>>>[mailto:oracle-l-bounce_at_freelists.org]
>>>On Behalf Of Juan Carlos Reyes Pacheco
>>>Sent: Thursday, July 21, 2005 18:45
>>>To: Oracle-L_at_freelists.org
>>>Subject: Re: Anyone using multi-block sizes for their databases
>>>
>>>Hi,
>>>This works really nice, this allows to create separate areas
>>>of memory for different things.
>>>For example big blobs documents, in a 32k normal tables 8k
>>>indexes 16k, etc.
>>>etc.
>>>
>>>You have to set the database memory cache in the init.ora
>>>for each different block size.
>>>--
>>>http://www.freelists.org/webpage/oracle-l
>>>--
>>>http://www.freelists.org/webpage/oracle-l
>>>

--
http://www.freelists.org/webpage/oracle-l