Gathering accurate system stats prior to 10g upgrade

Hi All,

This is another question about system statistics. We are in the process of upgrading from 9.2.0.6 to 10g release 2. We do not currently use system statistics in 9.2, but now they are mandatory for 10g we are keen to set accurate and representative statistics for our testing and development environments as well as production. Once these are set we do not intend to have oracle re-sample unless we make changes to the hardware configuration. Our area of difficulty is around the IO stats.

In our environment (Solaris 9 64-bit) Oracle accesses our SAN based disks via Veritas ODM drivers. The sequential read wait event in live is around 6ms and we are comfortable to use that value. However the multiblock read time is significantly less at around 2.5ms, due to the read ahead routines employed by the backend storage devices. If we were to use these values Oracle would start to favour tablescans and this would not be correct.

We have a pre-production rig, though there are outstanding issues around the I/O response times on those servers which are beyond our control, so setting up and testing in that environment is frustratingly not possible.

We have read much material posted in various places and are yet to be entirely convinced that any of the methods described are providing accurate statistics. We have developed a couple of C programs to simulate the random read times of 8k blocks and multiblock reads, however as those do not use the Veritas ODM routines to access the disk we feel they are not representative. Also our production environment is highly protected and actually running these tests (which involve creating a file that is larger than the SAN cache...) in live will be a challenge.

As a result of this we have settled upon using a single block read time of 6 and, as Oracle does for its defaults, multiplying this figure by 2 to get a multiblock figure of 12. To be fair we are pretty confident that we have reasonable values, but feel that the method we have used to gain them warrants further scrutiny.

Any comments and ideas welcome.

Ralph Received on Fri May 25 2007 - 05:28:32 CDT