Oracle, VMWare and Why You Should Add Indexes to Improve Performance

posted 12 Jun 2011, 05:16 by Tristan Self
I recently completed a migration of an 8 year old Oracle server to a nice new VM running on VMware Vpshere 4.1, this went without too many problems. However when people started to use it, the performance suffered and the server ground to a halt. The software vendor whose database it was imediately said, "ah you've virtualised, therefore you set your SAN up wrong".

But no! I spent many days measuring IOPS of the server and desiging the RAID groups on the SAN, this means that we had the correct storage performance in place, it was the database drawing more than it should. We checked out the performance logs and found the DB was using around 100Mbit to the SAN. Gasp! That was more disk activity that all the other VMs (50+) in our environment including our Exchange server with 850 users.

The imediate thought was an index was missing, we got our supplier to check the DB and look for slow queries, and yes there was one, taking about 4 seconds, and was run constantly. The software supplier added an index and then performance was improved by 95%, wow!

Heres the graph, guess when the index was enabled.... Er. 12:25PM perhaps!