Re: SQL Tuning Regarding System CPU Stats

andrew.markiewicz@xxxxxxxxx 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

My own personal opinion on this is that if you've got a running Prod
system, then *all* optimizer stats should be copied to your dev / test
environments. (I'll return to 'all' in a moment).

As with most shops, our dev/test infrastructure is a far cry from our
production setup, and hence the (calculated) system stats between them
are totally different. But ultimately, dev and test serve to build and
test a well-performing *production* system. Nothing make a IT shop look
more stupid than having something signed off in dev/test as "working
fine" only to have it bog down to a crawl in production.

Ultimately of course, its about response times. Independent of the
server setup we want our dev/test boxes to yield execution plans as
close to what will happen on production as possible, and hand in hand
with that, we strive to have our dev/test environments running fill size
production copies wherever its possible to do so. During testing, we
can then use some collected metrics to predict production response times
from dev/test response times.

Back to "all"... Naturally in dev/test you'll have objects which are new
or altered from what's in production, so they need their own stats
collected - but even in these cases, you'll want to seed them with what
you think will be production volumes (and hence likely production
stats).

Even on our multiterabyte production systems, we've found its vital to
have full size copies on their non-production equivalents (even if due
to costs, it has to be on a tiny number of massive SATA disks). Full
size non-production environments PLUS equivalent execution plans across
environments (which means copied system and object level stats), gives
you a lot more credibility with the client when you say "this code WILL
work when we deploy it to the live system".

hth
Connor

--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------
.

Relevant Pages

  • RE: How to link tables via the field names themselves, not their c
    ... you can query the database to do what you want easily. ... There are approximately 30 different aspects (or statistics) which are ... Beacuse there are so many stats per contract, ...
    (microsoft.public.access.queries)
  • Re: SQL Tuning Regarding System CPU Stats
    ... Since the system statistics are used per database ... different system performance stats. ... statistics on the production machines and import those system ...
    (comp.databases.oracle.server)
  • Re: SQL Tuning Regarding System CPU Stats
    ... Since the system statistics are used per database ... Different machines for production and development may have ... execution plans to run on a production system. ...
    (comp.databases.oracle.server)
  • SQL Tuning Regarding System CPU Stats
    ... Since the system statistics are used per database ... Different machines for production and development may have ... different system performance stats. ...
    (comp.databases.oracle.server)
  • Oracle DBA, Denver, Branta Group Contract
    ... Oracle DBA, Denver, Branta Group Contract ... sites focusing on database performance and availability. ... first and second tier resolution of production issues. ... procedures supporting a mission critical 24x7 production environment ...
    (comp.databases.oracle.marketplace)