RE: Question re System Statistics

Allen,

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?

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
-----Original Message-----

From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com] Sent: Friday, June 15, 2007 12:07 PM
To: William Wagman; oracle-l_at_freelists.org 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 transactions).

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 automatically).

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.

HTH,
Brandon

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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.

--
http://www.freelists.org/webpage/oracle-l