Re: DBMS_STATS.GATHER_SYSTEM_STATS samples only BADSTATS

The only think I can think of is that the N4 and N5 values are the single block read count and single block read time (in milliseconds), and the average single block read from your figures is:

    N4                            : 138598982
    N5                            : 53294180

    n5/n4 = 53294180 / 138598982
        =  0.3845 m/s

Possibly a time less than 1m/s is not allowed, and leaves you with 'BADSTATS'.

You have the same problem with the multiblock reads (n6 and n7). It looks like you may be getting a lot of data from a file-system buffer. (Or possibly your system has been up for so long that you are getting an odd 32-bit wrap on some counters).

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 29th Nov 2005

• Original Message ----- From: "Radoulov, Dimitre" <cichomitiko_at_gmail.com> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Monday, December 12, 2005 10:56 PM Subject: Re: DBMS_STATS.GATHER_SYSTEM_STATS samples only BADSTATS

> What do you get from
> select * from sys.aux_stats$

I gathered the stats in a separate stats table, so:

STATID                        : OLTP
TYPE                          : S
VERSION                       : 4
FLAGS                         : 1
C1                            : BADSTATS
C2                            : 12-09-2005 13:24
C3                            : 12-10-2005 10:01
C4                            : CPU_SERIO
C5                            :
N1                            : -1
N2                            : -1
N3                            : 588
N4                            : 138598982
N5                            : 53294180
N6                            : 160604371
N7                            : 46108340
N8                            : 243873588
N9                            : 421111191
N10                           : 0
N11                           : 15
N12                           : 2619857577
D1                            :
R1                            :
R2                            :
CH1                           :
-----------------
STATID                        : OLTP
TYPE                          : S
VERSION                       : 4
FLAGS                         : 1
C1                            :
C2                            :
C3                            :
C4                            : PARIO
C5                            :
N1                            : -1
N2                            : -1
N3                            :
N4                            :
N5                            :
N6                            :
N7                            :
N8                            :
N9                            :
N10                           :
N11                           :
N12                           :
D1                            :
R1                            :
R2                            :
CH1                           :
-----------------

PL/SQL procedure successfully completed.

Best regards,
Dimitre Radoulov

• Original Message ----- From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> To: <cichomitiko_at_gmail.com>; <oracle-l_at_freelists.org> Sent: Monday, December 12, 2005 11:36 PM Subject: Re: DBMS_STATS.GATHER_SYSTEM_STATS samples only BADSTATS

>
>
> As far as I know, there is only one thing that
> actually results in BADSTATS, and that's an
> absence of any single block reads in the time
> period. (But there may be other oddities that
> could be version dependent - like multiblock
> read times being faster than single block read
> times).
>
> What do you get from
> select * from sys.aux_stats$
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> Cost Based Oracle: Fundamentals
>
> http://www.jlcomp.demon.co.uk/appearances.html
> Public Appearances - schedule updated 29th Nov 2005
>
> ----- Original Message -----
> From: "Radoulov, Dimitre" <cichomitiko_at_gmail.com>
> To: <oracle-l_at_freelists.org>
> Sent: Wednesday, December 07, 2005 9:24 PM
> Subject: DBMS_STATS.GATHER_SYSTEM_STATS samples only BADSTATS
>
>
> The same 9.2.0.4 instance on Solaris 8 (do you remember the "hash join vs
> nested loops" thread :))
> Trying to gather system statistics in a period with common workload, CPU
> load 30-70%, only one instance on this node.
> I get only BADSTATS.
>
> Metalink says that we have BADSTATS when:
>
> => collection is completed but not pertinent, because there was no
> workload
> that could justify any statistics
>
> => start the collection again when the workload is relevant.
>
> Tried with interval 5 min,15 min, 1h, 2h, 8h, and I get only BADSTATS.
> Tried auto(interval)and manual(start/stop) mode, always BADSTATS.
>
>
> I'll try with an interval > 8h, but, meanwhile, any suggestion is welcome.
>
>
>
> Regards,
> Dimitre Radoulov
>
>
>
>
>

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