RE: Question re System Statistics

  • To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 15 Jun 2007 16:57:06 -0700


Thanks. Now the next question. Looking through the dbms_stats
documentation, unless I missed it I don't see a way to average stats. Do
you do this manually and then reload system stats based on your
calculations or is there a way to get dbms_stats (or some other utility)
to do this for you?


Bill Wagman
Univ. of California at Davis
IET Campus Data Center
(530) 754-6208
-----Original Message-----
From: Allen, Brandon [mailto:Brandon.Allen@xxxxxxxxxxx] 
Sent: Friday, June 15, 2007 12:07 PM
To: William Wagman; oracle-l@xxxxxxxxxxxxx
Subject: RE: Question re System Statistics

The point of system stats is to give the CBO the most accurate
information about the CPU and I/O performance of your system so I'd say
it's only necessary to gather new system stats whenever the performance
of your system changes so, e.g. if you make any hardware changes,
signficant code changes (e.g. add/remove/patch/upgrade an application),
or load changes (e.g. add/remove a significant amount of users or

I like to gather my stats over several "typical" business days and then
average them and manually set my system stats to the average  values
during a typical load.  Of course if you have wide variations in load,
e.g. OLTP by day and batch/OLAP/DSS by night, then you might want to
switch between two different sets of stats (set a job to do this

Aside from gathering new system stats after significant changes to the
system, I think a good approach is to periodically (maybe quarterly?)
gather your system stats in a staging table, and then compare those to
your system stats that are currently in effect and investigate any major
differences and then update your real system stats if warranted by the
results of your investigation.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of William Wagman

I would be interested in folks thoughts about how frequently system
statistics should be gathered 

Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do
not consent to Internet email for messages of this kind. Opinions,
conclusions and other information in this message that do not relate to
the official business of this company shall be understood as neither
given nor endorsed by it.


Other related posts: