Re: db block Size for Indexes Tablespaces in 9.2 ?

Please pardon the forthcoming rant...

<RANT>
Block size is one of the last things to consider in tuning indexes. Yes, it's a knob that can be twiddled, but it should be one of those out-of-the-way knobs that hardly ever get touched. It's much the same as adjusting DB_BLOCK_BUFFERS in order to improve performance -- after a certain point you are just rearranging deck chairs on the Titanic (i.e. waste of time and energy)...

This list is for education, and it is important to understand how new features (like multiple block sizes and the multiple cache sizes to support them) can be used, so the discussion is useful. But these particular features are essentially intended for enabling transportable tablespaces between databases with differing block sizes. Not performance tuning. If they were actually intended for performance tuning, then why aren't there KEEP and RECYCLE buffer pools for each block size, to make them equivalent to the "default" buffer cache? How difficult would it have been to have those features in the other block size?

Of course, this is not to say that a feature can't be adapted to purposes for which it wasn't intended, but it is food for thought, something to keep in mind...

Time is more profitably spent ensuring that indexes are built only on columns that have data with high selectivity, ensuring that they are rebuilt periodically or that REVERSE indexes are used for monotonically-ascending data, that column-level CBO statistics are gathered where data skew exists, that indexes on columns with low selectively are used only in selecting "unpopular" data values, etc. After all these issues are resolved, then decisions about block size *might* produce a noticeable result. Tuning the block size for certain sets of indexes might yield a marginal improvement in performance at the very most, but shrewd SQL tuning commonly produces improvements of hundred-fold or thousand-fold, at the very least. Efficient SQL and wisely chosen indexes will beat out the perfect block size, any day of the week...
</RANT>

OK, I'm better now...

......Mmmmm......donuts......

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 17, 2002 10:33 PM

> Hi Arup , List
>
> Your point is Correct about High "buffer busy wait" Contention During
Large OLTP Insert /Updates.
>
> High "buffer busy wait" on Corresponding INDEX during INSERT Operations
was Observed
> during our previous benchmark which overcame by Converting to REVERSE
Index as the Field Value
> was Sequentially Increasing .
>
> Following Article advocates HIGHER Block Size for Index , Strangely :-
>
> http://www.tusc.com/oracle/download/author.html#loneyk
>
> What may be the Commonly followed Best practice for DB_BLOCK_SIZE for
Index Tablespaces in 9.2 ?
>
> Thanks
>
>
> -----Original Message-----
> Sent: Wednesday, December 18, 2002 12:14 AM
> To: Multiple recipients of list ORACLE-L
>
>
> I can't point to a paper or article on that, but based on tests I have
done,
> the block size of index tablespaces for a high volume transaction system
> should be lower than the table block sizes. 2000 tps with 25 changes each
> does not sound like a really huge transaction rate. You could set the
> tables' block size as 8k and indexes' as 4k.
>
> The reason for smaller block sizes in index is simple - to reduce the
> dreaded buffer busy waits that will inevitably happen for this type of
> transaction rate. A smaller block size will make sure less number of rows
> are in a block - making the chance that two sessions are trying to get the
> same buffer low.
>
> Caveat - if your database has a large percentage of DSS type quries (you
> mentioned hybrid), then they will suffer slightly as the block gets will
> increase.
>
> HTH
>
> Arup Nanda
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, December 17, 2002 11:28 AM
>
>
> >
> > What is the Best Practice for DB_BLOCK_SIZE for Index Tablespaces
> Undergoing High Volume of Insert , Update , Selects ? Why ?
> >
> > Any Docs , Links for the Same ?
> >
> >
> > NOTE - This is for a Hybrid Application Benchmark undergoing 2000
Banking
> Transactions per second
> > approx.
> >
> > OLTP Transaction sample = 1 Cash Deposit Transaction undergoing Insert
> Update Delete Operations on Total of 5 Tables Each having About 5 Indexes
> >
> > Thanks
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: VIVEK_SHARMA
> INET: VIVEK_SHARMA_at_infosys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).