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_at_sbcglobal.net> To: <richard.foote_at_bigpond.com> Cc: <DGoulet_at_vicr.com>; <JBECKSTROM_at_gcrta.org>; <oracle-l_at_freelists.org>; <ORACLE-L_at_IC.SUNYSB.EDU>; <oracledba_at_LazyDBA.com>; <oracle-rdbms_at_yahoogroups.com> 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