Re: db block Size for Indexes Tablespaces in 9.2 ?

I have a rule - don't hand out advise with limited information - it may be "factually challenged". and sometimes I break my own rules - as in this case. But then again, this is not "advise"; merely my opinion based on the very limited background information I had.

I agree - the panacea to an index "buffer busy waits" is not simply the block size; rather a complex co-determination of several factors and solutions like reverse key indexes, block size, free lists, elimination of synthetic keys, the load pattern, etc. But given that

  a.. selects are 60% of the queries
  b.. transaction rate is high to very high
  c.. OLTP is primary for performance tuning

what options do we have? Elimination of synthetic keys _MAY_ not be an option in this case, due to third party application, etc. Reverse key Indexes have their own problems - they consume more space and sometimes the optimizer does a index full scan instead of a row get. So the only thing a DBA can possibly do at this time is in block sizes. Again, this is, of course, my assumption.

So given the information by the questioner - the biggest bang for the buck is by specifying the smaller block size. It may however be different if a bench mark is performed at the site using the specific customer profile.

As to the article, Vivek, I read it thoroughly and I still stand by what I said. The author primarily advocates that

  a.. more blevels are bad for performance
  b.. a higher block size contains more rows per block
  c.. more rows implies less blevel
  d.. and, therefore, a large block size implies better performance

However, this probably ignores the buffer busy waits condition. More rows in a block implies that two sessions are more likely to access rows in the same block - creating a buffer busy condition - more so in a RAC environment. So I advocate less number of rows in a block. The author also relies heavily on the index rebuilds a lot to eliminate the blevels, not just block sizes.

Caveat - see the most waits occurring in your system - if after a smaller block size your index "db file sequential read" increases, then you are better off using a larger block size and suffer buffer busy waits. Benchmark, benchmark. There is no substitute for benchmark specific to your situation.

In my shop, we have 9.2 in a DW environment where the block size is the same (16K) for indexes and tables. For other two OLTP based apps, I have defined 8K tables and 4K indexes for a third party app. I did a benchmark to see the effects and that was the conclusion. Unfortunately I didn't save it.

HTH. Arup Nanda

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 18, 2002 6:19 AM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.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).