Re: Gathering accurate system stats prior to 10g upgrade

On May 25, 6:28 am, Ralph.in..._at_googlemail.com wrote:
> 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

It is common for oracle to calculate the mreadtim to be smaller than sreadtim for various storage types impacted by caching and read ahead.

If you take corrective actions and make adjustments ... proceed carefully. Create some test cases that correspond to expected behavior of some critical parts of your applications is my recommendation.

Take a look at some of the recent updates in this area provided by Jonathan Lewis. Received on Fri May 25 2007 - 08:43:07 CDT