Re: db block Size for Indexes Tablespaces in 9.2 ?

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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).