Re: IO question

> Now-a-days because of SAN technology, people starting doing SAME -
> stripe and mirror everything and mix everything together. It is
> much economical and easier to manage from sys admin point of view.
> So there is no longer such concept of a separate disk.

Rephrased:

Now-a-days because of MMTS (more money than sense) technology, people starting doing TDSOLTDIP-OTI (too damn stupid or lazy to do it properly - or test it). It is much easier than managing it from a sysadmin point of view.

> Strickly speaking, separating index and table really does not buy
> you anything. Oracle never reads index and table at the same time,
> so there shouldn't be any contention anyway.

There is a considerable opportunity to get disk head contention if indexes and data are on the same disk as the head moves from index to table and back to index. Traditionally this was an important issue. It may not be such an important issue now because of the existence of high cache hit ratios and the improvement in device speeds. To some extent, a RAID 5 system reduces the extent of the problem because e.g. in a 5 disk array, the head only has to move 1/4 of the distance it would move on a single disk.

Many years ago, I halved the runtime for various batch processes by splitting data and indexes. I've not repeated the experiment recently. Note however that any effect will depend on the way in which the extents of the data are allocated and filled. Traditionally, tables were sized at to their full size and created in advance of indexes (this allows the tables to be created and populated before the indexes are created) - this approach maximises the consequences of head contention.

There is a similar logic to the age old debate about a large number of extents in an object. It is not the large number of extents in itself that causes a problem, so creating a table with small extent sizes is not a meaningful test. Rather it is that the object data becomes spread over the disk surface as new extents are added to different tables over the course of time. One day I'll get around to testing this but I suspect that it may not matter these days. Received on Mon Jan 21 2002 - 13:05:31 CST