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,
- 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.
- 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
Received on Thu May 17 2007 - 10:55:33 CDT