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

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: deshpande.subodh@xxxxxxxxx
  • Date: Fri, 4 Mar 2011 08:04:43 +0000


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@xxxxxxxxx
> 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@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.
>> --
> --
> ==============================
> ==============================

Niall Litchfield
Oracle DBA

Other related posts: