On Mon, 17 Nov 2003 12:50:13 +0000, Richard Foote wrote:
> "Hans Forbrich" <forbrich_at_yahoo.net> wrote in message
> news:3FB6E237.FA9608F_at_yahoo.net...
>> At the risk of being shot, drawn and quartered:
>>
>> I know (and agree with) the fundemental discussion that separating
>> indexes and tables into separate tablespaces should not be done for
>> performance reasons - in pre-Oracle9i environments!
>>
>> However, with Oracle9i and it's support for multiple block sizes: Is
>> there a possible performance benefit to be obtained by placing the
>> tables and [some] indexes in separate tablespaces, IF the tablespaces
>> have different blocksizes?
>>
>> (If this has been previously discussed, please just point me to the
>> approximate time frame so I can review the archives.)
>>
> Hi Hans,
>
> I hate to be a party poop, but there's a fundamental issue that everyone has
> missed thus far.
>
> The vast majority of Oracle databases sit on top of O/S file systems and
> file systems use buffered I/O (unless direct I/O is set). Therefore,
> although having large block sizes for indexes sounds like a great idea in
> principle, in practice it will actually *hurt* performance. The correct,
> optimal block size for these environments is the I/O buffer size. Setting
> the block size any larger, say twice the I/O buffer size (eg. 8K on AIX)
> would result in every logical I/O on the index requiring two O/S calls.
> Having double (or more) O/S calls per logical read on your indexes is going
> to have a detrimental effect, even if such larger blocks succeed in reducing
> the index's overall height.
>
> The whole thing is counter productive and will have a negative impact on
> overall performance. The concept of having multiple block sizes to aid
> *performance* is generally questionable. Multi block sizes are there to aid
> transportable tablespaces, not performance per se.
>
> That said, there are *very* specific examples where multi sized blocks may
> provide some benefit. Interestingly going the other way, small block sizes
> for many numbers of tiny sized, read only lookup tables (smaller than a
> 'small' block, say 2K) as commonly defined in large financials type
> applications could be advantages, but I emphasize the benefits are not
> generally significant. And any benefits in reading and effectively caching
> such data needs to be carefully balanced with any writes that may occur
> (hence why R/O is better).
>
> With raw I/O, larger is nearly always better. Not necessarily just for
> indexes, but for larger row tables, ASSM objects, undo segments, higher data
> density, etc.
>
> In summary, using multi sized blocks to aid performance, especially in
> buffered I/O environments is likely to end in tears.
>
> Cheers
>
> Richard
Of course, you know that the good, old International Business Machines
have an excellent file system called JFS which supports both async I/O
and direct I/O both on AIX and Linux, so that if you set
filesystemio_options parameter to "setall" (both direct and async I/O),
things will work almost as if it was a raw device? I am against larger
blocks for indexes, but I do strongly advocate larger blocks for the
tables that are frequently read by using full table scan and don't have
much update activiy on them.
--
None of us is as dumb as all of us.
(http://www.despair.com/meetings.html)
Received on Mon Nov 24 2003 - 04:30:06 CST
