Re: SQL Tuning Regarding System CPU Stats

On May 17, 3:55 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On May 17, 11:55 am, andrew.markiew...@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

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

.

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: Access unter Citrix - Probleme beim Komprimieren
    ... Hat mir doch keine Ruhe gelassen, war ja auch nicht in MSDN oder KB, sondern in der TechNet drin: Microsoft Jet Database Engine Programmer's Guide, Chapter 4. ... The optimizer in the Jet database engine is a cost-based optimizer, which means the optimizer assigns a cost to each task and then chooses the least expensive list of tasks to perform that will generate the desired result set. ... The algorithms that the optimizer uses depend on the accuracy of the statistics provided by the underlying engine. ... Each time you prepare a request for information, either by sending an SQL statement as an argument to the OpenRecordset method of a Database object or by saving a QueryDef object in your database, Microsoft Jet runs through a complex series of analysis and optimization steps. ...
    (microsoft.public.de.access)
  • Re: Access unter Citrix - Probleme beim Komprimieren
    ... Microsoft Jet Database Engine Programmer's Guide, ... The optimizer is one of the most complex components of the query engine. ... uses statistics to determine the most efficient way to execute a query. ... Each time you prepare a request for information, ...
    (microsoft.public.de.access)
  • Need to aggregate t-test stats
    ... My main goal is to "bless" a new database such ... "name" lengths for each country. ... them would have high p-values (seems like a great way to ... statistics obtained from all the different tests and compare ...
    (sci.stat.math)
  • Re: 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)
Loading