From oracle-l-bounce@freelists.org Mon Dec 12 17:08:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jBCN88U6008095 for ; Mon, 12 Dec 2005 17:08:08 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id jBCN7vAX008081 for ; Mon, 12 Dec 2005 17:07:58 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 23568252A6C; Mon, 12 Dec 2005 17:56:48 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31438-06; Mon, 12 Dec 2005 17:56:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8686D2530C7; Mon, 12 Dec 2005 17:56:47 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 12 Dec 2005 17:56:45 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 89FAF252837 for ; Mon, 12 Dec 2005 17:56:45 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31438-05 for ; Mon, 12 Dec 2005 17:56:45 -0500 (EST) Received: from uproxy.gmail.com (uproxy.gmail.com [66.249.92.201]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A7B36252711 for ; Mon, 12 Dec 2005 17:56:43 -0500 (EST) Received: by uproxy.gmail.com with SMTP id j3so155262ugf for ; Mon, 12 Dec 2005 14:56:41 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:from:to:cc:references:subject:date:mime-version:content-type:x-priority:x-msmail-priority:x-mailer:x-mimeole; b=mlym5QKVjnUcSlJchwJB3+nLmvmu5pSGJ3n00J//bnL+fIFGOOLfqTMaH3Nn7wxXP7fFDChzMvahJLGgVEaR0n55tNFRf7cHaiOzzt3S2DgsARNdh0LMa3by0ApZ55fO2OTKE7CxCi5omtkxX4zeZ10U4t+HRqyo4ohjH029CCI= Received: by 10.67.23.12 with SMTP id a12mr800395ugj; Mon, 12 Dec 2005 14:56:40 -0800 (PST) Received: from IBME1D11967173 ( [82.48.83.243]) by mx.gmail.com with ESMTP id j3sm2035055ugd.2005.12.12.14.56.37; Mon, 12 Dec 2005 14:56:38 -0800 (PST) Message-ID: <039301c5ff6f$4ff360d0$3808310a@IBME1D11967173> From: "Radoulov, Dimitre" To: "Jonathan Lewis" Cc: References: <00a801c5fb74$a5b52f20$2608310a@IBME1D11967173> <031201c5ff6c$86eb5e10$6902a8c0@Primary> Subject: Re: DBMS_STATS.GATHER_SYSTEM_STATS samples only BADSTATS Date: Mon, 12 Dec 2005 23:56:35 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0390_01C5FF77.AF40AE50" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-archive-position: 29108 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: cichomitiko@gmail.com Precedence: normal Reply-To: cichomitiko@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Not scanned: please contact your Internet E-Mail Service Provider for details X-MailScanner-From: oracle-l-bounce@freelists.org ------=_NextPart_000_0390_01C5FF77.AF40AE50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > 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 -----=20 From: "Jonathan Lewis" To: ; Sent: Monday, December 12, 2005 11:36 PM Subject: Re: DBMS_STATS.GATHER_SYSTEM_STATS samples only BADSTATS >=20 >=20 > 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). >=20 > What do you get from > select * from sys.aux_stats$ >=20 > Regards >=20 > Jonathan Lewis >=20 > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > The Co-operative Oracle Users' FAQ >=20 > http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html > Cost Based Oracle: Fundamentals >=20 > http://www.jlcomp.demon.co.uk/appearances.html > Public Appearances - schedule updated 29th Nov 2005 >=20 > ----- Original Message -----=20 > From: "Radoulov, Dimitre" > To: > Sent: Wednesday, December 07, 2005 9:24 PM > Subject: DBMS_STATS.GATHER_SYSTEM_STATS samples only BADSTATS >=20 >=20 > The same 9.2.0.4 instance on Solaris 8 (do you remember the "hash join = vs=20 > nested loops" thread :)) > Trying to gather system statistics in a period with common workload, = CPU=20 > load 30-70%, only one instance on this node. > I get only BADSTATS. >=20 > Metalink says that we have BADSTATS when: >=20 > =3D> collection is completed but not pertinent, because there was no = > workload > that could justify any statistics >=20 > =3D> start the collection again when the workload is relevant. >=20 > 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. >=20 >=20 > I'll try with an interval > 8h, but, meanwhile, any suggestion is = welcome. >=20 >=20 >=20 > Regards, > Dimitre Radoulov >=20 >=20 >=20 >=20 > ------=_NextPart_000_0390_01C5FF77.AF40AE50 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
> What do you get = from
>   =20 select * from sys.aux_stats$

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

STATID         =             &= nbsp; =20 :=20 OLTP
TYPE          &= nbsp;           &n= bsp;  =20 :=20 S
VERSION          &= nbsp;           =20 :=20 4
FLAGS          &nb= sp;           &nbs= p; =20 :=20 1
C1           =             &= nbsp;   =20 :=20 BADSTATS
C2          = ;            =      =20 : 12-09-2005=20 13:24
C3          &n= bsp;           &nb= sp;    =20 : 12-10-2005=20 10:01
C4          &n= bsp;           &nb= sp;    =20 :=20 CPU_SERIO
C5         &nbs= p;            = ;     =20 :
N1           =             &= nbsp;   =20 :=20 -1
N2           = ;            =     =20 :=20 -1
N3           = ;            =     =20 :=20 588
N4          &nbs= p;            = ;    =20 :=20 138598982
N5         &nbs= p;            = ;     =20 :=20 53294180
N6          = ;            =      =20 :=20 160604371
N7         &nbs= p;            = ;     =20 :=20 46108340
N8          = ;            =      =20 :=20 243873588
N9         &nbs= p;            = ;     =20 :=20 421111191
N10         &nb= sp;           &nbs= p;    =20 :=20 0
N11           = ;            =    =20 :=20 15
N12          &nbs= p;            = ;   =20 :=20 2619857577
D1         &nb= sp;           &nbs= p;     =20 :
R1           =             &= nbsp;   =20 :
R2           =             &= nbsp;   =20 :
CH1           = ;            =    =20 :
-----------------
STATID       = ;            =     =20 :=20 OLTP
TYPE          &= nbsp;           &n= bsp;  =20 :=20 S
VERSION          &= nbsp;           =20 :=20 4
FLAGS          &nb= sp;           &nbs= p; =20 :=20 1
C1           =             &= nbsp;   =20 :
C2           =             &= nbsp;   =20 :
C3           =             &= nbsp;   =20 :
C4           =             &= nbsp;   =20 :=20 PARIO
C5          &n= bsp;           &nb= sp;    =20 :
N1           =             &= nbsp;   =20 :=20 -1
N2           = ;            =     =20 :=20 -1
N3           = ;            =     =20 :
N4           =             &= nbsp;   =20 :
N5           =             &= nbsp;   =20 :
N6           =             &= nbsp;   =20 :
N7           =             &= nbsp;   =20 :
N8           =             &= nbsp;   =20 :
N9           =             &= nbsp;   =20 :
N10           = ;            =    =20 :
N11           = ;            =    =20 :
N12           = ;            =    =20 :
D1           =             &= nbsp;   =20 :
R1           =             &= nbsp;   =20 :
R2           =             &= nbsp;   =20 :
CH1           = ;            =    =20 :
-----------------
 
PL/SQL procedure successfully = completed.





Best regards,
Dimitre=20 Radoulov




----- Original Message -----
From: = "Jonathan=20 Lewis" <jonathan@jlcomp.demon.co.uk>
To: = <cichomitiko@gmail.com>;=20 <oracle-l@freelists.org>
Sent: Monday, December 12, 2005 11:36=20 PM
Subject: Re: DBMS_STATS.GATHER_SYSTEM_STATS samples only=20 BADSTATS


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