I recently started getting these high numbers in my statspack
statistics. We have Oracle 8.1.6.0.0 on Solaris 2.6 platform.
Shakir
- "Reardon, Bruce (CALBBAY)" <Bruce.Reardon_at_comalco.riotinto.com.au>
wrote:
> Glenn,
> Without commenting on the value of the hit ratio, I can comment on
> the suggestion that the bug affects all platforms.
> I am running 8.1.7.1.4 on NT4 and your query gives the following:
> SQL> col name for a20
> SQL> col value for 999,999,999,999,999,999,999
> SQL> select name,value from v$sysstat
> 2 where name in ('redo size', 'physical reads', 'db block gets')
> 3 /
>
> NAME VALUE
> -------------------- ----------------------------
> db block gets 872,439,682
> physical reads 74,967,581
> redo size 32,655,440,244
>
> SQL> select sysdate-startup_time from v$instance;
>
> SYSDATE-STARTUP_TIME
> --------------------
> 107.677072
>
> SQL>
>
> So, we have generated over 2Gb redo and our other counters aren't
> wrapping.
>
> This is consistent for another NT4 81714 database we have as well.
> I don't have access to any other platforms besides Windows so can't
> comment on the situation elsewhere.
>
> Hope this helps,
> Bruce Reardon
>
> -----Original Message-----
> Sent: Friday, 12 April 2002 0:59
>
> Glenn,
>
> The buffer cache hit ratio is meaning less, not only after startup
> but any time you calculate it. I am pretty sure that I am not the
> first one and probably not the last one saying that on this mailing
> list.
>
> Now about the claim of why you need to wait until 10i to get this
> fixed, has probably something to do with the fact of how the SGA is
> allocated on the HP platform. Any change in the layout of the fixed
> SGA will mean a recompile of the code on HP.
>
> Now it looks to me that the upper 4 bytes of the 8 bytes have been
> set to -1:
> 18446744069434437169
> FFFFFFFF012EEE31
> 18446744052688746229
> FFFFFFFB1B0FF6F5
>
> So you probably could adjust for that ....
>
> Anjo.
>
>
> Glenn Travis wrote:
>
> > I sent a message last week regarding our values in the v$sysstat
> table being WAY too large;
> > physical_reads = 18,446,744,069,434,437,169
> > db_block_gets, physical_reads_direct, physical_writes_direct also.
> >
> > This prevents us from running the db cache hit ratio queries.
> >
> > I logged a tar with Oracle and they said it was a bug (#1713403).
> It is caused by an overflow in v$sysstat when the amount of generated
> redo grows over 2GB. They say this bug can't be fixed (at least not
> until 10i!). I am running on 8.1.7 (HP-UX11).
> >
> > If you are on 8i, could you query the v$sysstat table and let me
> know if anyone else is seeing this problem?
> >
> > col name for a20
> > col value for 999,999,999,999,999,999,999
> > select name,value from v$sysstat
> > where name in ('redo size', 'physical reads', 'db block gets')
> > /
> > NAME VALUE
> > -------------------- ----------------------------
> > db block gets 18,446,743,996,920,309,855
> > physical reads 18,446,744,052,688,746,229
> > redo size 17,049,609,736
> >
> > I find it unacceptable that Oracle would ignore this until 10i.
> The only time I can get a cache hit ratio is when I first start up
> the database (which doesn't mean anything). I know hit ratios are
> overrated and we look at waits more for performance tuning (read all
> the articles), but it is still frustrating nonetheless.
> > Author: Glenn Travis
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: Bruce.Reardon_at_comalco.riotinto.com.au
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammed Shakir
INET: mshakir08816_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Apr 11 2002 - 19:25:14 CDT