Re: SQL Tuning Regarding System CPU Stats

On May 17, 3:55 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On May 17, 11:55 am, andrew.markiew..._at_gmail.com wrote:
>
>
>
> > Hello all.
> > We are in the process of upgrading from 9i to 10g and starting to use
> > system statistics. Since the system statistics are used per database
> > instance and not per user/schema, what approach is recommended for
> > tuning a production system within a development environment?
>
> > A few issues we are contending with,
>
> > 1) Different machines for production and development may have
> > different system performance stats. We are planning to collect system
> > statistics on the production machines and import those system
> > statistics to the development database so programmers will be tuning
> > execution plans to run on a production system. That may make tuning a
> > bit more confusing since it may not be using the optimal plan for that
> > machine, but we are mainly concerned with production performance. If
> > the stats for the test and production machines are close enough, this
> > is a non-issue, but we do not know that at the moment nor can be
> > guaranteed that future hardware upgrades won't upset that balance.
>
> > 2) OLTP stats and batch processing stats seem to be different enough
> > that we may need to use a different set of statistics in production at
> > each of these times. But since the database is only allowed one set of
> > system statistics, how do we allow developers that may be working
> > concurrently on both OLTP and batch programs to use the system
> > statistics each will need to tune each of their respective SQL?
>
> > Short of providing multiple test databases, each with the system
> > statistics representative of the type of work the database will do at
> > that time, we are having a some difficulty coming up with a single
> > test environment that will work for all developers at once.
>
> > Any suggestions or experience in this regard?
> > Thanks
> > Andrew Markiewicz
>
> The developers should be tuning for best performance, and not for the
> best looking execution plan. Thus, the optimal execution plan may
> look different on the development and production systems, while
> delivering the optimal performance on each system. Make certain that
> CPU statistics are gathered when the database instance is under normal
> to heavy load, otherwise the statistics gathered will be
> inappropriate, and will cause inappropriate execution plans to be
> developed due to incorrectly determined execution cost - this may be
> hard to accomplish on the test database instances. It is possible to
> manually specify the CPU statistics, if necessary.
>
> You may need to resort to using Outlines to maintain plan stability
> between the two databases.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Thanks Charles.
We are not tuning for a specific plan, but given the same inputs (CPU stats from the production db) the optimizer on the test system will get the same plans as production. If the same stats are not used, but instead CPU stats for the test machine, then the developers will tune to make sure the app works fine in test, but what then about production? Just hope it works? Or a staging database perhaps. I know the CBO should do the right thing, but I've experienced enough situations where statistics (bad or missing) can change the execution plan and I'm guessing it will be a similar situation with the system statistics. Perhaps outlines may be needed for stability.

But there still remains the issue of OLTP stats vs batch stats. Currently I am collecting stats at intervals throughout the day for OLTP and batch processing to get a better sampling of our system usage. Perhaps we can work with one set of stats for both OLTP and batch, but if they are determined to be different enough, then we would somehow have to provide developers with the system statistics the program would use when it is run. But with one development system, two sets of stats can not be provided. (Avoiding a second development system is preferrable.)

Andrew Received on Thu May 17 2007 - 17:11:06 CDT