Re: separate tablespaces for tables and indexes
- From: "Richard Foote" <richard.foote@xxxxxxxxxxx>
- To: "Mladen Gogala" <gogala@xxxxxxxxxxxxx>
- Date: Wed, 15 Dec 2004 00:22:38 +1000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
