Spreading the disk I/O is not an issue. Majority of cases involves some
kind of SAN solution, which completely hides any underlying disk structure.
Your volumes consist of striped and mirrored disk devices cached by the
SAN box itself. At the time when the DBA sees data files, disk spindles are
completely indiscernible.
The reason to separate is ease of administration of my large objects. I have
several (3) tables in the XXL tablespace, with EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512MB. Indexes are much smaller and are in separate tablespace,
which has allocation policy set to AUTOALLOCATE.
--
Mladen Gogala
Ext. 121
-----Original Message-----
From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com]
Sent: Tuesday, December 14, 2004 8:06 AM
To: gogala_at_sbcglobal.net
Cc: richard.foote_at_bigpond.com; oracle-l_at_freelists.org
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.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 14 2004 - 08:53:30 CST
