Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: separate tablespaces for tables and indexes

Re: separate tablespaces for tables and indexes

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 15 Dec 2004 00:25:49 +1000
Message-ID: <032d01c4e1e8$cf085ab0$0100000a@FOOTE>


Hi Niall,

If only you're email arrived a little earlier, I might have saved some typing !!

Exactly.

I couldn't have said my words any better.

Cheers

Richard
----- Original Message -----
From: "Niall Litchfield" <niall.litchfield_at_gmail.com> To: <gogala_at_sbcglobal.net>
Cc: <richard.foote_at_bigpond.com>; <oracle-l_at_freelists.org> Sent: Tuesday, December 14, 2004 11:06 PM Subject: Re: separate tablespaces for tables and indexes

On Tue, 14 Dec 2004 12:41:38 +0000, Mladen Gogala <gogala_at_sbcglobal.net> wrote:
> 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.
> >=20
> > Why ?
> >=20
> > Because, generally one accesses many more "table" blocks than "index"
> > blocks
> > and index blocks have a greater tendency to remain cached or be =20
> > 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 =20
> total I/O requests needed to read/write indexes and requests needed to
> =20
> read/write tables. So, if you leave them together, your tablespace =20
> files will be hotter still.There are two main principles used for =20
> separating objects in different tablespaces:

I'm not going to put words into Richard's mouth (don't know enough David Bowie lyrics for that), but I don't think that your comparison is quite correct. If you are going to split at all, you are going to have > 1 disk (or volume), otherwise the effort is pointless. Consider the (simplest) case where you have 2 disks for data and indexes. In the case of creating just one tablespace one would still utilize 2 datafiles and all the IO would be spread across both disks. If one decided to create a data and an index tablespace then one would simplistically create one datafile for each and allocate them to different disks. Then the IO would still be spread across disks, but it would no longer be spread approximately evenly because of the different caching characteristics that Richard mentions, instead the single data disk would suffer more contention. This is of course solvable by the expedient of using twice as many datafiles (2 for each tablespace) to achieve the same IO distribution as the original non-split solution. In the more general case with n disks and m tablespaces one needs n*m datafiles to achieve the same split as n datafiles would for a single tablespace.

all of the above assumes random IO to the files. This is a bad assumption - index access in my experience is quite liable to suffer from local hotspots.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 14 2004 - 07:15:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US