Re: separate tablespaces for tables and indexes

Hi Mladen,

Please don't beg, I hate it when people beg. Let's make this really really 
really simple:

Disk 1 Tables Only => 1000 I/Os per second

Disk 2 Indexes Only => 100 I/Os per second

I'm not suggesting:

Disk 1 Tables and Indexes => 1100 I/Os per second

Disk 2 Nothing

but something like

Disk 1 1/2 Tables and Indexes => 550 I/Os per second

Disk 2 other 1/2 Tables and Indexes => 550 I/Os per second

How is Disk 1 or 2 "hotter still" in your words ?

The number of times people claim to improve performance by separating 
indexes/tables only to find they've added a heap of extra disks whilst 
separating.

Perhaps the extra (or in your case the reduction) of disks may just be a 
contributing factor ...

Cheers

Richard

----- Original Message ----- 
From: "Mladen Gogala" <gogala@xxxxxxxxxxxxx>
To: <richard.foote@xxxxxxxxxxx>
Cc: <DGoulet@xxxxxxxx>; <JBECKSTROM@xxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>; 
<ORACLE-L@xxxxxxxxxxxxx>; <oracledba@xxxxxxxxxxx>; 
<oracle-rdbms@xxxxxxxxxxxxxxx>
Sent: Tuesday, December 14, 2004 10:41 PM
Subject: Re: separate tablespaces for tables and indexes


Richard, I beg to differ.

On 12/14/2004 04:54:43 AM, Richard Foote wrote:
> In some cases, separating your indexes can actually *increase*
> contention.
>
> Why ?
>
> Because, generally one accesses many more "table" blocks than "index"
> blocks
> and index blocks have a greater tendency to remain cached or be  cached
> when
> required. Therefore, there are generally many more PIOs associated
> with your
> table tablespaces than their associated index tablespaces if you
> separate
> them. A look at most statspack reports will reveal this.

If you look at the total amount of I/O, then leaving tables and indexes
together will cause the number of I/O requests equal to the sum of
total I/O  requests needed to read/write indexes and requests needed to
read/write tables. So, if you leave them together, your tablespace
files will be hotter still.There are two main principles used for
separating objects in different tablespaces:

1) Separating by logical grouping (putting related objects together)
2) Separating by size (tablespaces for small, medium, large, XL and XXL
  objects). This was recommended by Guy Harrison, back in the time
  when T-Rex was ruling the earth.


Either of the two types of separation causes tablespace to be hotter if
it  contains both types of objects, because total amount of I/O will be
larger. Also, separating the two increases resilience of the database.
If tablespace containing only indexes becomes terminally corrupted, you
can simply rebuild indexes elsewhere without data loss. If it happens
to a data tablespace, one has to do recovery.

-- 
Mladen Gogala
Oracle DBA




--
http://www.freelists.org/webpage/oracle-l

Other related posts: