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

It seems to me that 2 things are being confused in the replies here.

The quote that dba1 mcc refers to is specifically about system statistics (better referred to as the CPU costing model) where statistics about the capability of the underlying hardware are used to modify the cost calculations that the optimizer users to calculate the optimal execution plan. These are different from object statistics which are the information about database objects that the CBO uses to calculate optimal execution plans. Specifically object statistics for the SYS schema are not "system statistics" but a special case of object statistics. Object statistics need to be representative of the object and data in use, which typically means periodic updates and may well mean special treatment for specific database objects (I recently blogged about the fact that column statistics for density can be badly unrepresentative with default sampling for specific columns until 11.2 for example) . System statistics however should be representative of the hardware behaviour under load and so typically should probably only be recollected if the load or hardware changes significantly.

On Fri, Mar 4, 2011 at 5:03 AM, Subodh Deshpande <deshpande.subodh_at_gmail.com
> wrote:

> 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_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
>>
>>
>>
>
>
> --
> ==============================
> DO NOT FORGET TO SMILE TODAY
> ==============================
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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