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_at_vicr.com> To: <JBECKSTROM_at_gcrta.org>; <oracle-l_at_freelists.org>; <ORACLE-L_at_IC.SUNYSB.EDU>; <oracledba_at_LazyDBA.com>; <oracle-rdbms_at_yahoogroups.com> 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_at_gcrta.org]=20 Sent: Monday, December 13, 2004 2:05 PM
To: oracle-l_at_freelists.org; ORACLE-L_at_IC.SUNYSB.EDU; oracledba_at_LazyDBA.com; oracle-rdbms_at_yahoogroups.com 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