Re: How to know the stats is corrupted
Date: Wed, 1 May 2013 07:02:52 -0700 (PDT)
Message-ID: <1367416972.12541.YahooMailNeo_at_web121602.mail.ne1.yahoo.com>
Using analyze creates what I call 'incomplete' statistics, as they are not as comprehensive as those generated by dbms_stats. Analyse is still around for two reasons -- to validate structure of indexes and to generate a list of chained rows, two functions which dbms_stats does not address. Combining them can create a mess of conflicting statistics which can cause performance problems (such as that which you reported).
Please use dbms_stats for statistics gathering. Leave analyze for the two tasks that dbms_stats doesn't do. David Fitzjarrell
From: K R <kp0773_at_gmail.com>
To: David Fitzjarrell <oratune_at_yahoo.com> Cc: "Oracle-L_at_freelists.org" <Oracle-L_at_freelists.org> Sent: Tuesday, April 30, 2013 2:41 PM
Subject: Re: How to know the stats is corrupted
thanks Everyone .
the version is 9.2.0.4 and running on solaris 9 . We gather the stats
using analyze most of the time and in some time we use dbms_stats to
gather stats. Could it be that we should be only using analyze and not
dbms_stats . or is it that mixing analyze and dbms_stats can cause the
issue.
the result we observe once in a while lot of physical reads/scattered read full tablescan , library cache pin goes up by 100% . . and then we end up deleting stats and again running it
On Tue, Apr 30, 2013 at 6:27 AM, David Fitzjarrell <oratune_at_yahoo.com>wrote:
> Since you haven't posted any Oracle version information it's difficult to
> answer such a question. As stated in other responses I doubt that the
> statistics are actually 'corrupt'; I would expect them to possibly be stale
> or gathered incorrectly for the data. There are known issues with
> statistics in some versions of Oracle however until we know which release
> you're using we can't consider those anamolies.
>
> Please post the Oracle version you're using, some fairly specific
> information on what performance issues arise from time to time and possibly
> some samples of the gathered statistics. From that information we can
> provide better assistance to you.
>
> David Fitzjarrell
>
>
> *From:* K R <kp0773_at_gmail.com>
> *To:* "Oracle-L_at_freelists.org" <Oracle-L_at_freelists.org>
> *Sent:* Tuesday, April 30, 2013 4:15 AM
>
> *Subject:* How to know the stats is corrupted
>
> Hi,
> At times when the performance goes slow some of DBA in our team says that
> the stats get corrupted . asking how do you determine dont bring any good
> explanation.
>
> Anyone please throw some insights on it .
>
> thanks
> Kart
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 01 2013 - 16:02:52 CEST