I think there are too many generic arguments available for picking the 'right' block size for your indexes.
The one that is most appropriate is likely to depend on the nature of the activity (load vs. query), nature of the index (unique, nearly unique, far from unique), data clustering, order of data arrival, frequency of data arrival, pattern of data deletion/update, stability of volume, nature of queries (big or small range scans), potential of modifying number of branches, buffering effects, and whether or not you are using a filesystem with or without direct i/o. Given another 10 minutes I might come up with a few more ideas. Your strategy should be to identify the extreme, and critical, characteristics of your system and play to them - small block size may be appropriate, reverse indexes may be appropriate, getting rid of the synthetic key that is likely to cause a problem may be appropriate. But don't assume that anything as trivial as tweaking a block size is a driving feature of making your index work well. Which test case would you like to see - the one I did for company X that showed they needed a small block size, or the one I did for company Y that showed they needed a large block size ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____England______January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 18 December 2002 05:59 >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 > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
