Re: separate tablespaces for tables and indexes

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.

Therefore your table disk(s) are far hotter leading to possible contention 
issues than the far more quieter index disk(s) if such separation is not 
thought through carefully.

Those that simply separate table / indexes in the mistaken belief that it 
reduces contention may actually inadvertently be increasing contention 
problems.

Something to consider....

Cheers

Richard


----- Original Message ----- 
From: "Goulet, Dick" <DGoulet@xxxxxxxx>
To: <JBECKSTROM@xxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>; 
<ORACLE-L@xxxxxxxxxxxxx>; <oracledba@xxxxxxxxxxx>; 
<oracle-rdbms@xxxxxxxxxxxxxxx>
Sent: Tuesday, December 14, 2004 5:27 AM
Subject: RE: separate tablespaces for tables and indexes


In my opinion, yes.  The original idea was to place the objects on
different drives to protect against failure and contention.  But today I
still think it's valid as large tablespaces take more time to scan than
smaller ones.  It may not reduce contention, but speed still counts. And
then there's always the problem of the fumbled fingers at the os level.=20


Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Jeffrey Beckstrom [mailto:JBECKSTROM@xxxxxxxxx]=20
Sent: Monday, December 13, 2004 2:05 PM
To: oracle-l@xxxxxxxxxxxxx; ORACLE-L@xxxxxxxxxxxxx;
oracledba@xxxxxxxxxxx; oracle-rdbms@xxxxxxxxxxxxxxx
Subject: separate tablespaces for tables and indexes

We have started using locally managed tablespaces for all new
tablespaces.  We create the tablespaces with autoallocate. =20
=20
Since the tablespaces are locally managed, is there a need to separate
the tables and indexes anymore?
=20
=20
Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: