Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Capacity Sizing vs Speed

Capacity Sizing vs Speed

From: Martic Zoran <>
Date: Tue, 11 Jan 2005 04:22:51 -0800 (PST)
Message-ID: <>

Hi all,

Just curious about some nice examples (if any) about this kind of trade-off in Oracle.
I will try to make it simple.

We all know that to have as fast operations as possible we want to not have enough Oracle waits imposed by our transaction.

Lets take simple INSERT into the table with one complex PK index.
We have N processes doing these INSERTs in parallel. To have as much fasst as possible INSERT's, the usual optimization for hot spots is to spread them across as many as possible data/index blocks (or chains, ...).

>From the other side the I/O subsystem is going to
suffer if DBWR needs to write more blocks then when you put all records in as less blocks as possible in some time interval (to DBWR keep up with MTTR).

The difference can be drastical from the I/O subsystem sizing perspective if you have 500 blocks modified and if you have 50 blocks.

Anybody ever trade-off the design (the different column order in the index, ...) to slowdown INSERT's by imposing Oracle waits (to insert into less blocks as possible) to have downsized I/O subsystem that can resist the volume.

If my thinking is stupid let me know too :)

Also, I am aware that somebody will tell me why do you want to have less disks when disks are the cheapest.

Anyway I will spend the similar amount of CPU in both cases, just will have less I/O.

It looks that sometimes things like right-handed index or not optimal table/index design can save us of the disaster when I/O subsystem is not sized properly :)

Thanks in advance for all comments.

Zoran Martic

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
Received on Tue Jan 11 2005 - 06:18:01 CST

Original text of this message