Re: Tuning index tablespaces

Tarby777 wrote:

> Thanks Daniel. I have read some of what some of those people have
> written on the subject, and I'm fine with the the basic argument, which
> seems to be that because access is sequential (index block THEN data
> block) rather than concurrent, no single process will execute faster by
> virtue of having the data and the index on separate disks. However, I
> would still maintain that process A (hammering indexes) and process B
> (hammering data) will experience lower contention if the data and the
> indexes are separated.

You can maintain it but it wouldn't be correct. Here's what really happens.

Process "A" gets some index blocks for you and then goes off to handle a request from one of the other 100+ connections to the database. Process "A" couldn't care less about you and your next index read as it goes about servicing other requests. And then of course the next request is for an insert anyway so it even muddier still.

Process "B" similarly will handle requests as it receives them and couldn't care less about you versus any other request.

A block is a block is a block. And neither Oracle nor the operating system cares abpout the difference between one type of block and the other.

Reconsider your belief system.

> My question wasn't so much about the real or mythical benefits of
> separation - it was simply to ask whether it is possible to achieve a
> performance improvement in a dedicated index tablespace through
> non-default physical properties.
>
> Regards,
> Tarby

And my response was to indicate that it is impossible to achieve a performance improvement doing what you are doing ... so stop doing it.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)