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
- The OLTP app has lots of small rows (<2k)
- 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)
- The OLTP app has 1,000 connected users -- each
wants their current row(s) they
are using in the cache.
- 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.
- The indexes for the critical access paths fit
exactly into an N-layer tree and allow an optimum
buffering strategy when memory is limited.
- 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.
- 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
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> Date: 02 October 2001 21:56
>
>
> |If your application allows it, and if the
> Application
> |will not change in the future, then use a 2k block
> |size for OLTP database.
> |
> |If you are not sure on the application needs, then
> |stick with 4k to be safe.
> |
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 Tue Oct 02 2001 - 18:52:34 CDT
