RE: redo_size values in v$sysstat
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)" <[EMAIL PROTECTED]>
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 /
>
> NAMEVALUE
>
> 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
> 012EEE31
> 18446744052688746229
> FFFB1B0FF6F5
>
> 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')
> > /
> > NAMEVALUE
> >
> > 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
RE: redo_size values in v$sysstat
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 /
NAMEVALUE
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
012EEE31
18446744052688746229
FFFB1B0FF6F5
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')
> /
> NAMEVALUE
>
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: redo_size values in v$sysstat
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
012EEE31
18446744052688746229
FFFB1B0FF6F5
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')
> /
> NAMEVALUE
>
> 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.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Glenn Travis
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anjo Kolk
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
redo_size values in v$sysstat
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')
/
NAMEVALUE
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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Glenn Travis
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
