Re: Huge numbers in SYS,AUX_STAT$
From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Wed, 7 Sep 2011 15:33:02 -0700 (PDT)
Message-ID: <9f322324-f920-47ad-817a-dbc8dbbeebb6_at_g30g2000vbu.googlegroups.com>
On Sep 7, 5:43 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Wed, 07 Sep 2011 21:16:39 +0000, Mladen Gogala wrote:
> > This is 11.2.0.2 database, with the April PSU applied, on Linux 32bit.
> > These are the numbers I see in SYS.AUX_STATS$:
>
> > SQL> select pname,pval1 from sys.aux_stats$
> > 2 where pname like '%READTIM';
>
> > PNAME PVAL1
> > ------------------------------ ---------- SREADTIM
> 90819.71
> > MREADTIM 72565.924
>
> > I thought that these were durations of an average single/multi block
> > reads, expressed in milliseconds. This doesn't work, even in
> > microseconds. Also, SREADTIM is larger than MREADTIM, which I find
> > rather strange. Has anybody noticed that? Did the units change? Should
> > I start playing with DBMS_STATS.SET_SYSTEM_STATS? Ask Tom is busy as
> > usual, impossible to ask question there.
>
> I should have searched Metalink, before asking the question:
> Bug 9842771 - Wrong SREADTIM and MREADTIM statistics in AUX_STATS$ [ID
> 9842771.8]
>
> Both versions of 11.2.0 are affected. It's going to be fixed in 11.2.0.3
> and the workaround is to set it manually. This will do wonders for the
> folks who upgrades database and expects the queries to work normally.
Date: Wed, 7 Sep 2011 15:33:02 -0700 (PDT)
Message-ID: <9f322324-f920-47ad-817a-dbc8dbbeebb6_at_g30g2000vbu.googlegroups.com>
On Sep 7, 5:43 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Wed, 07 Sep 2011 21:16:39 +0000, Mladen Gogala wrote:
> > This is 11.2.0.2 database, with the April PSU applied, on Linux 32bit.
> > These are the numbers I see in SYS.AUX_STATS$:
>
> > SQL> select pname,pval1 from sys.aux_stats$
> > 2 where pname like '%READTIM';
>
> > PNAME PVAL1
> > ------------------------------ ---------- SREADTIM
> 90819.71
> > MREADTIM 72565.924
>
> > I thought that these were durations of an average single/multi block
> > reads, expressed in milliseconds. This doesn't work, even in
> > microseconds. Also, SREADTIM is larger than MREADTIM, which I find
> > rather strange. Has anybody noticed that? Did the units change? Should
> > I start playing with DBMS_STATS.SET_SYSTEM_STATS? Ask Tom is busy as
> > usual, impossible to ask question there.
>
> I should have searched Metalink, before asking the question:
> Bug 9842771 - Wrong SREADTIM and MREADTIM statistics in AUX_STATS$ [ID
> 9842771.8]
>
> Both versions of 11.2.0 are affected. It's going to be fixed in 11.2.0.3
> and the workaround is to set it manually. This will do wonders for the
> folks who upgrades database and expects the queries to work normally.
There are apparently two related bugs, the comment by Sokrates at the
following link states that there is a patch for 9842771, but I do not
recall locating that patch:
http://hoopercharles.wordpress.com/2011/05/04/how-to-collect-statistics/
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Sep 07 2011 - 17:33:02 CDT