RE: separate tablespaces for tables and indexes
- From: "William B Ferguson" <wbfergus@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 14 Dec 2004 06:38:08 -0700
I think in these days, with RAID and disk arrays, most of the old-time
arguments for creating separate tablespaces are invalid. As several =
others
have stated, the main reason was to reduce 'DISK' contention.
Think about the old days for a bit. With tablespaces on different disks,
you increase the number of read/write heads available for getting data
from the disk. If everything was on one disk, whether one big tablespace
or several smaller ones, there was still only one head available to get
the data. If it was busy reading blocks from disk, it didn't matter how
many tablespaces/data files there were, the other processes still had to
wait for that one disk head to become available again to move somewhere
else.
However, with that said, there is now different reason to 'think' about
having different tablespaces/data files. It won't be the same for
everbody, as everybody's data is different, but analyzing your data (and
the application's use of it) may give you insights on reasons why to =
have
separate tablespaces. Different tablespaces allows having different =
block
sizes, different logging options, etc. A separate tablespace for RAW or
LOB data would make sense, having the largest possible block size. =
Having
a separate tablespace with logging for static lookup tables and a =
smaller
block size may make sense. Conversely, it may also make sense to have a
separate tablespace for the indexes and a large block size, depending on
how often the indexes are used and if they generally all of the =
requested
data, so tables wouldn't be accessed.
It all depends on the particular database, the data, the design, and =
it's
use.
------------------------------------------------------------
Bill Ferguson
U.S. Geological Survey - Minerals Information Team
PO Box 25046, MS-750
Denver, Colorado 80225
Voice (303)236-8747 ext. 321 Fax (303)236-4208
~ Think on a grand scale, start to implement on a small scale ~
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of niall.litchfield@xxxxxxxxx
Sent: Tuesday, December 14, 2004 6:06 AM
To: gogala@xxxxxxxxxxxxx
Cc: richard.foote@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: separate tablespaces for tables and indexes
On Tue, 14 Dec 2004 12:41:38 +0000, Mladen Gogala <gogala@xxxxxxxxxxxxx>
wrote:
> Richard, I beg to differ.
>=20
> On 12/14/2004 04:54:43 AM, Richard Foote wrote:
> > In some cases, separating your indexes can actually *increase* =20
> >contention. =3D20
> > Why ?
> >=3D20
> > Because, generally one accesses many more "table" blocks than =
"index"
> > blocks
> > and index blocks have a greater tendency to remain cached or be =
=3D20
> > 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.
>=20
> If you look at the total amount of I/O, then leaving tables and=20
> indexes together will cause the number of I/O requests equal to the=20
> sum of =3D20 total I/O requests needed to read/write indexes and=20
> requests needed to =3D20 read/write tables. So, if you leave them=20
> together, your tablespace =3D20 files will be hotter still.There are =
two=20
> main principles used for =3D20 separating objects in different=20
> 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.
--=20
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
