Hi Christopher,
Like I said, Oracle experts can argue this issue until
they are blue in the face, kinda like the
Certification debate. Without any information on the
data or application, I suggest a 2k block size.
Everyone is entitled to thier own opinion, and I hope
the author of the original post becomes successful in
choosing his/her block size.
By the way, you are agreeing and disagreeing with
Jonathan Lewis at the same time. The index efficiency
on small block size is Jonathan's point he made a
couple of years ago when he was stating why he
recommends a 2k block size on an OLTP type database.
The other index point (scanning an index on a small
block size) is from Thomas Kyte on the same
thread...If you disagree with him, you can write him
an e-mail so you can correct him.
I hope you can appreciate the irony.
Regards,
Satar
PS Nice website, I like the goth look! Now you should
play some Bauhaus or Christian Death in the
background. ;)
- Christopher Spence <cspence_at_FuelSpot.com> wrote:
> I disagree in the 2k for OLTP as well, for similar
> reasons Jonathan
> mentioned, as well as a few of the obvious. Most
> OLTP are not PERFECTLY
> tuned to only do indexes scans either. And indexes
> are much more efficient
> on the larger block sizes as well
>
>
> Christopher R. Spence
> Oracle DBA
>
> -----Original Message-----
> Sent: Tuesday, October 02, 2001 8:55 PM
> To: Multiple recipients of list ORACLE-L
>
> Hi Jonathan,
>
> Sweeping statement...maybe. It all depends on your
> application. That's why I put an emphasis on his/her
> application (meaning both physical structure and
> data)
> requirements. As a GENERAL rule of thumb, I
> (personally) suggest (if possible) 2k for OLTP
> databases. It's like if you ask me what car to buy,
> I
> would say a "Honda". But if you were to tell me that
> you need speed, style and sex on wheels, I would
> suggest a Benz.
>
> You and I, (especially you!) can argue the
> benefits/disadvantages of using 2k block
> size...Which
> I don't care to do. I've put the benefits/reasons of
> a
> 2k blocksize at the bottom of this reply just to
> show
> some reverse examples of the ones you replied with.
> Some of the reasons should look familiar to you! ;)
>
> Regards,
> Satar
>
> 1. The OLTP app has lots of small rows (<2k)
>
> 2. The OLTP app does massive scattered reads (you
> don't full scan in OLTP). It
> reads a block here, a block there based on some
> primary key (after doing lots of
> scattered reads to walk an index structure)
>
> 3. The OLTP app has 1,000 connected users -- each
> wants their current row(s) they
> are using in the cache.
>
> 4. A smaller block size in a database that does lots
> of scattered IO's (eg: an OLTP system) and random
> keyed reads can definitely benefit from smaller
> blocksizes.
>
> 5. The indexes for the critical access paths fit
> exactly into an N-layer tree and allow an optimum
> buffering strategy when memory is limited.
>
> 6. In almost all cases the data for a single index
> value will be (significantly) less than 2K and will
> all be found in the same block.
>
> 7. Memory is at a premium.
>
> --- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
> wrote:
> >
> > That's a fairly sweeping statement to make without
> > any justification - after all, at 2K:
> >
> > The block header is a much larger percentage
> > of the block size - so you lose space.
> >
> > The probability of wasting space from the
> > PCTFREE
> > setting increases - so you lose space.
> >
> > The memory overhead due to x$bh is fixed per
> > block,
> > so you use more memory to hold the same volume
> > (block size x block count) of data.
> >
> > Index depth may increase through having fewer
> > entries per branch block.
> >
> > Latch activity is likely to increase on range
> > scans
> > because leaf blocks hold fewer entries - you
> get
> > the same effect on tables with clustered data
> -
> > and the effectiveness of IOTs will
> particularly
> > be
> > reduced.
> >
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone.
http://phone.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: satar naghshineh
INET: satarnag_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
Received on Wed Oct 03 2001 - 13:20:28 CDT
