Re: should I analyze system statistics on ORACLE 10GR2?
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: deshpande.subodh@xxxxxxxxx, mccdba1@xxxxxxxxx
- Date: Fri, 4 Mar 2011 06:17:29 -0800 (PST)
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:
