Re: separate tablespaces for tables and indexes
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: gogala@xxxxxxxxxxxxx
- Date: Tue, 14 Dec 2004 13:06:29 +0000
On Tue, 14 Dec 2004 12:41:38 +0000, Mladen Gogala <gogala@xxxxxxxxxxxxx> 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.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
