RE: should I analyze system statistics on ORACLE 10GR2?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, <oratune@xxxxxxxxx>, <deshpande.subodh@xxxxxxxxx>, <mccdba1@xxxxxxxxx>
- Date: Fri, 4 Mar 2011 17:09:24 -0500
This is a common confusion between collecting statistics on the objects in
the sys and system schemas versus collecting system statistics, which are
the metrics of how the resources of the operating system respond.
If someone asks about system statistics the default understanding is the
latter. I didn't decide to overload the use of these words, I'm just
reporting the usage as I understand it for clarity. (Shades of partition for
storage versus aggregate functions).
If I recall correctly JL has a post describing more in depth what I'll write
as briefly as can: It is hazardous to performance to actually collect system
statistics, because the tendency is to collect them any time other than when
the system is at peak load. Plugging in decent numbers representative of the
system capabilities at peak load will tend to help the optimizer to get
things right. Feeding the optimizer unrealistic system statistics by any
means, including collecting them at a period of near idleness will tend to
make the optimizer add things up into bad choices.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Taylor, Chris David
Sent: Friday, March 04, 2011 10:11 AM
To: 'oratune@xxxxxxxxx'; 'deshpande.subodh@xxxxxxxxx'; 'mccdba1@xxxxxxxxx'
Cc: 'oracle-l@xxxxxxxxxxxxx'; 'oracle-db-l@xxxxxxxxxxxxxxxxxxxx'
Subject: RE: should I analyze system statistics on ORACLE 10GR2?
This is one of the areas that is really frustrating as a DBA.having to treat
the SYS objects differently and THEN having to treat them differently in
different databases. Some apps work well with SYS statistics, some fail
miserably.
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and
may also be privileged. If you are not the named recipient, please notify
the sender immediately and delete the contents of this message without
disclosing the contents to anyone, using them for any purpose, or storing or
copying the information on any medium.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David Fitzjarrell
Sent: Friday, March 04, 2011 8:17 AM
To: deshpande.subodh@xxxxxxxxx; mccdba1@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; oracle-db-l@xxxxxxxxxxxxxxxxxxxx
Subject: Re: should I analyze system statistics on ORACLE 10GR2?
Collecting system statistics is really an 'it depends' situation since if
you're running Siebel or SAP applications in 10g and later releases creating
a job to collect system statistics can cause problems with performance ( the
SAP team where I worked my last contract did NOT collect system statistics
because when they tried it caused some critical query plans to change for
the worse ). In other cases it may be acceptable and improve the
performance of some queries so it's best to test this tactic before you
implement in a production environment so the users aren't caught by surprise
with dismal execution plans for queries that once ran acceptably.
David Fitzjarrell
_____
From: Subodh Deshpande <deshpande.subodh@xxxxxxxxx>
To: mccdba1@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; oracle-db-l@xxxxxxxxxxxxxxxxxxxx
Sent: Thu, March 3, 2011 11:03:19 PM
Subject: Re: should I analyze system statistics on ORACLE 10GR2?
if you using cost based optimizer then you should collect stats..
I think...over a period earlier stats can be purged
thanks....subodh
On 17 February 2011 01:26, dba1 mcc <mccdba1@xxxxxxxxx> wrote:
I saw a ORACLE document "Performance Tuning Guide for Siebel CRM
Application on Oracle Part I - Cost-Based Optimization Guide" On this
article it say "don't analyze system statistics":
==================================================
System Optimizer Statistics
In 9i, Oracle introduced cpu_costing, a mechanism that allows the CPU cost
of an operation to be included as part of the overall estimate. This feature
is enabled in 9i only if you collect system statistics. This feature in 10g
is enabled by default. Oracle Database 10g gathers two types of system
statistics - statistics captured without a workload (noworkload) and
statistics captured with a workload. In addition to CPU cost, disk I/O and
memory utilization are also captured, to ensure that CBO picks the plan with
the lowest estimated cost relative to other plans.
Many Oracle DBAs attempt to leverage system optimizer statistics for further
tuning of Siebel CRM application. Such actions may cause unpredictable
degradation in performance of certain Siebel CRM application views and
queries, and are strongly discouraged. We recommend that DBAs refrain from
collecting system optimizer statistics, or altering any Oracle default
settings pertaining to systems statistics.
============================================================
I also see another ORACLE document say you need "analyze system statistics"
to avoid stange thing happen.
Can Anyone tell me should I or Should NOT 'analyze system statistics"?
Thanks.
--
//www.freelists.org/webpage/oracle-l
--
==============================
DO NOT FORGET TO SMILE TODAY
==============================
Other related posts:
