Leo Mannhart wrote:
>>> and what exactly will this help regarding performance?
>>> (this is urban legend)
>>>    
>
>  
>> A flamebaiter on the list? ;-)
>>  
>
>  
>> Seriously, depending on the number of indexes and the speed of your disk
>> controller vs. the speed of your disks, you can get a 50% increase in
>> write speeds.
>>  
>
> Yes because of pure luck. Depending on the write requests you can get 0% performance increase. Oracle is writing first to the buffer cache and the db-writer later writes the blocks to disk. So wichever file db-writer is writing to, as long as they are separeted, you'll see a performance gain. But this has nothing to do with index versus data tablespace. It is all about the write pattern of the application.
>
> For sure you can find a lot of information on the web, but why not start with
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:901906930328
>
> Cheers, Leo
>  
Just to add to Leos comments:

The "Index on one disk, tables on another" rule came back from before
the era of the wait interface -
where you nowadays have the possibility to determine where the time is
actually spend.

This rule was so widely acknowledged that Oracle Applications adopted it
as best practise - resulting in
400+ tablespaces (!).
And guess what: Oracle Applications version 11.5.10 now comes with only
4or 6 tablespaces.

I recall that several sources has refered to the ability to read
simulteaneuously from more disks
if you placed indexes and tables on seperate disks - this is what we at
my previous job (Oracle
Consulting) refered to as the famous "crystal ball interface" :-)

To add more: the widespread use of SAN's among many features also
delivers a level of virtualization -
you are simply not supposed to spend time on what disk IO goes to (and
therefore you are most
likely not able to tell what file goes where)
This makes discussions about IO-(re)distribution with placing tables and
indexes in certain tablespaces
more or less useless.

Of course you may end up with better performance if you separate indexes
and tables - but that
is so much dependent on the specific application and its use that you
can't use the rule anymore as
a general recommendation for performance.


--
Med venlig hilsen

Martin Berg

email: martin@(protected)
web: www.berg-consult.com
mobil: 30 35 58 68


--
To unsubscribe, email: suse-oracle-unsubscribe@(protected)
For additional commands, email: suse-oracle-help@(protected)
Please see http://www.suse.com/oracle/ before posting