Re: should I analyze system statistics on ORACLE 10GR2?

From: Wayne Smith <wts_at_maine.edu>
Date: Thu, 3 Mar 2011 12:12:25 -0500
Message-ID: <AANLkTimR0yDCa_Mqw_24kmBZTAJVNViiR=hn50QHg=Dr_at_mail.gmail.com>

This was important to our PeopleSoft/Oracle 10.2.0.3 DB performance. See bug 5103126.8 and note 375944.1 (fixed in 11g, as I recall). Again, as I recall, that when statistics were deleted from 3 objects in SYS, performance of certain functions were dramatically improved. Cheers, Wayne

On Wed, Feb 16, 2011 at 2:56 PM, dba1 mcc <mccdba1_at_yahoo.com> 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.
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Mar 03 2011 - 11:12:25 CST