Capacity Sizing vs Speed

  • From: Martic Zoran <zoran_martic@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 11 Jan 2005 04:22:51 -0800 (PST)

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 

Other related posts:

  • » Capacity Sizing vs Speed