ASM Performance

[sivakumarocp]

Hi Tim,

If we are keeping our database into normal filesystem, we used to store DATA and INDEX tablespace into different filesystem. To reduce IO contentions on the physical disk.

But in ASM storage we are not following that! We had migrated database from filesystem to ASM (its a OLTP db), before migration we kept index and data tablespace into different filesystem and after migration we kept all tablespace into single diskgroup +DATAFILE.

but still I am wondering why the performance not degraded, when heavy IO's are happening on single diskgroup (+DATAFILE).

[Tim...]

Hi.

There has never really been any science behind the "tables in one tablespace and indexes in another" argument.

If you think of how an index is used, blocks from the index are read. When the correct leaf node is identified, the ROWID from that leaf node is used to access the table block that contains the row. At no point are the index and table being accessed at the same time, so at no point can a single operation cause contention between them.

In a multiuser system, many operation can be accessing the same table or the same index, which would cause contention, but this is not increased or decreased by it's location in a specific tablespace. If you have two very busy objects in the same tablespace, which is on the same physical disk, this may result in contention, but this could be two tables, two indexes or a mix. The point is, you may benefit from separating busy segments, but just splitting tables and indexes is very arbitrary and may not result in performance improvement.

ASM recommend, like many storage vendors, having one large disk group with all your spindles in it. The increased number of spindles results in improved performance, since many disks can work together to provide the information, rather than a single spindle.

Cheers

Tim...