Re: negative value for buffer cache hit ratio
Infact one of our production database (8.0.x) has a buffer cache hit ratio of 2% for the last 2 years. All others are about 90%+. Strange thing is that users are so happy with the performance of this database with 2% hitratio, they do not want us to increase the buffer cache!! Rama Rachel Carmichael wrote: > Ramon, > > if you find the performance of the database is good, why are you trying to > fix it?? > > Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit > ratio is high, and you have large numbers of wait events, then your database > needs tuning. > > If the cache hit ratio is low, but there are no wait events then your > database does not need tuning. > > Rachel > > >From: "Ramon Estevez" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: RE: negative value for buffer cache hit ratio > >Date: Wed, 08 Aug 2001 06:27:11 -0800 > > > >Thanks, > > > >As far as I know, the correct value is as close to 100% as possible. > >So, if mine is 0.68 > > > >-* > >MISS_RATEI > >-I > > 0.67%I > > I > >This is now 9:15 am. I > >-* > > > >I find the performance of the DB good, but as the result of the query > >I think that it could get better. > > > >I executed this script that was posted yesterday in the list. > > > > > >select to_char(100 * misses / (logical - physical + misses), '9990.00') > >|| > > 2 '%' miss_rate > > 3from ( select total_waits misses > > 4from sys.v_$system_event > > 5 where event = 'db file sequential read'), > > 6 ( select value physical > > 7 from sys.v_$sysstat > > 8where name = 'physical reads'), > > 9 ( select sum(value) logical > >10 from sys.v_$sysstat > >11where name like '%consistent read gets' > >12 or name = 'db block gets'); > > > > > > > >Ramon Estevez > >[EMAIL PROTECTED] > > > > > > > > > > > >-Mensaje original- > >De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K > >Gopalakrishnan > >Enviado el: Tuesday, 07 August, 2001 5:07 PM > >Para: Multiple recipients of list ORACLE-L > >Asunto: RE: negative value for buffer cache hit ratio > > > > > >Hi, > > > >Generally speaking.. HIT RATIOS does not give the true > >picture always. I have seen databases performing > >extremely good with 50% and bad databases with 99% hit > >ratio. > > > >You should never decide the database performance based > >on hit ratio and 90% hit ratio does not mean that 90% > >of the data is ALWAYS read from the cache. It > >translates in to something like this..A block is read > >(consistent get) 9-10 times before written to disk.. > > > >You should check your system wide wait statistics for > >better tuning.. > > > > > > > > > >--- Ramon Estevez <[EMAIL PROTECTED]> > >wrote: > > > Hi, > > > > > > My misses is > > > > > > MISS_RATE > > > - > > > 0.68% > > > > > > Is that good or bad, Oracle 8.0.5 Standard Edition, > > > Windows 2000. > > > > > > Is there a 8.1.7 Standard Edition Version ? > > > > > > Ramon Estevez > > > [EMAIL PROTECTED] > > > > > > > > > > > >= > >Have a nice day !! > > > >Best Regards, > >K Gopalakrishnan, > >Bangalore, INDIA. > > > >__ > >Do You Yahoo!? > >Make international calls for as low as $.04/minute with Yahoo! Messenger > >http://phonecard.yahoo.com/ > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: K Gopalakrishnan > > 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 spellin
RE: negative value for buffer cache hit ratio
Thanks Rachel good point. :-) Ramón Estévez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Rachel Carmichael Enviado el: Wednesday, 08 August, 2001 12:25 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Ramon, if you find the performance of the database is good, why are you trying to fix it?? Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit ratio is high, and you have large numbers of wait events, then your database needs tuning. If the cache hit ratio is low, but there are no wait events then your database does not need tuning. Rachel >From: "Ramon Estevez" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: negative value for buffer cache hit ratio >Date: Wed, 08 Aug 2001 06:27:11 -0800 > >Thanks, > >As far as I know, the correct value is as close to 100% as possible. >So, if mine is 0.68 > >-* >MISS_RATEI >-I > 0.67%I > I >This is now 9:15 am. I >-* > >I find the performance of the DB good, but as the result of the query >I think that it could get better. > >I executed this script that was posted yesterday in the list. > > >select to_char(100 * misses / (logical - physical + misses), '9990.00') >|| > 2 '%' miss_rate > 3from ( select total_waits misses > 4from sys.v_$system_event > 5 where event = 'db file sequential read'), > 6 ( select value physical > 7 from sys.v_$sysstat > 8where name = 'physical reads'), > 9 ( select sum(value) logical >10 from sys.v_$sysstat >11where name like '%consistent read gets' >12 or name = 'db block gets'); > > > >Ramon Estevez >[EMAIL PROTECTED] > > > > > >-Mensaje original- >De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K >Gopalakrishnan >Enviado el: Tuesday, 07 August, 2001 5:07 PM >Para: Multiple recipients of list ORACLE-L >Asunto: RE: negative value for buffer cache hit ratio > > >Hi, > >Generally speaking.. HIT RATIOS does not give the true >picture always. I have seen databases performing >extremely good with 50% and bad databases with 99% hit >ratio. > >You should never decide the database performance based >on hit ratio and 90% hit ratio does not mean that 90% >of the data is ALWAYS read from the cache. It >translates in to something like this..A block is read >(consistent get) 9-10 times before written to disk.. > >You should check your system wide wait statistics for >better tuning.. > > > > >--- Ramon Estevez <[EMAIL PROTECTED]> >wrote: > > Hi, > > > > My misses is > > > > MISS_RATE > > - > > 0.68% > > > > Is that good or bad, Oracle 8.0.5 Standard Edition, > > Windows 2000. > > > > Is there a 8.1.7 Standard Edition Version ? > > > > Ramon Estevez > > [EMAIL PROTECTED] > > > > > > >= >Have a nice day !! > >Best Regards, >K Gopalakrishnan, >Bangalore, INDIA. > >__ >Do You Yahoo!? >Make international calls for as low as $.04/minute with Yahoo! Messenger >http://phonecard.yahoo.com/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: K Gopalakrishnan > 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: Ramon Estevez > 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
RE: negative value for buffer cache hit ratio
Ramon, if you find the performance of the database is good, why are you trying to fix it?? Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit ratio is high, and you have large numbers of wait events, then your database needs tuning. If the cache hit ratio is low, but there are no wait events then your database does not need tuning. Rachel >From: "Ramon Estevez" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: negative value for buffer cache hit ratio >Date: Wed, 08 Aug 2001 06:27:11 -0800 > >Thanks, > >As far as I know, the correct value is as close to 100% as possible. >So, if mine is 0.68 > >-* >MISS_RATEI >-I > 0.67%I > I >This is now 9:15 am. I >-* > >I find the performance of the DB good, but as the result of the query >I think that it could get better. > >I executed this script that was posted yesterday in the list. > > >select to_char(100 * misses / (logical - physical + misses), '9990.00') >|| > 2 '%' miss_rate > 3from ( select total_waits misses > 4from sys.v_$system_event > 5 where event = 'db file sequential read'), > 6 ( select value physical > 7 from sys.v_$sysstat > 8where name = 'physical reads'), > 9 ( select sum(value) logical >10 from sys.v_$sysstat >11where name like '%consistent read gets' >12 or name = 'db block gets'); > > > >Ramon Estevez >[EMAIL PROTECTED] > > > > > >-Mensaje original- >De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K >Gopalakrishnan >Enviado el: Tuesday, 07 August, 2001 5:07 PM >Para: Multiple recipients of list ORACLE-L >Asunto: RE: negative value for buffer cache hit ratio > > >Hi, > >Generally speaking.. HIT RATIOS does not give the true >picture always. I have seen databases performing >extremely good with 50% and bad databases with 99% hit >ratio. > >You should never decide the database performance based >on hit ratio and 90% hit ratio does not mean that 90% >of the data is ALWAYS read from the cache. It >translates in to something like this..A block is read >(consistent get) 9-10 times before written to disk.. > >You should check your system wide wait statistics for >better tuning.. > > > > >--- Ramon Estevez <[EMAIL PROTECTED]> >wrote: > > Hi, > > > > My misses is > > > > MISS_RATE > > - > > 0.68% > > > > Is that good or bad, Oracle 8.0.5 Standard Edition, > > Windows 2000. > > > > Is there a 8.1.7 Standard Edition Version ? > > > > Ramon Estevez > > [EMAIL PROTECTED] > > > > > > >= >Have a nice day !! > >Best Regards, >K Gopalakrishnan, >Bangalore, INDIA. > >__ >Do You Yahoo!? >Make international calls for as low as $.04/minute with Yahoo! Messenger >http://phonecard.yahoo.com/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: K Gopalakrishnan > 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: Ramon Estevez > 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: negative value for buffer cache hit ratio
Interesting. In 8.1.7 32-bit oracle binary, select * from v$type_size where type = 'UB4' tells me it's 4 bytes. So it's 32 bit and the maximum is 4 billion. Glad to see Jonathan on this forum. Yong Huang [EMAIL PROTECTED] you wrote: > From: K Gopalakrishnan > > Welcome Jonathan, > > I think most of the counter are limited by ub4maxval > and that makes the negative hit ratio. > > Welcome again ! > > > --- Jonathan Lewis > wrote: > > > > It is possible that after 4 months your stats > > have wrapped around the ( ? 64 bit ?) limit > > value for your platform. Check the actual > > values from v$sysstat to see if some of them > > have gone negative or appear to be > > 'counting backwards'. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang 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: negative value for buffer cache hit ratio
Thanks, As far as I know, the correct value is as close to 100% as possible. So, if mine is 0.68 -* MISS_RATEI -I 0.67%I I This is now 9:15 am. I -* I find the performance of the DB good, but as the result of the query I think that it could get better. I executed this script that was posted yesterday in the list. select to_char(100 * misses / (logical - physical + misses), '9990.00') || 2 '%' miss_rate 3from ( select total_waits misses 4from sys.v_$system_event 5 where event = 'db file sequential read'), 6 ( select value physical 7 from sys.v_$sysstat 8where name = 'physical reads'), 9 ( select sum(value) logical 10 from sys.v_$sysstat 11where name like '%consistent read gets' 12 or name = 'db block gets'); Ramon Estevez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K Gopalakrishnan Enviado el: Tuesday, 07 August, 2001 5:07 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez <[EMAIL PROTECTED]> wrote: > Hi, > > My misses is > > MISS_RATE > - > 0.68% > > Is that good or bad, Oracle 8.0.5 Standard Edition, > Windows 2000. > > Is there a 8.1.7 Standard Edition Version ? > > Ramon Estevez > [EMAIL PROTECTED] > > = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: Ramon Estevez 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: negative value for buffer cache hit ratio
Hello Jonathan !
Thanks for reply !
First , i love your site and may i recommend it to all the listers :
http://www.jlcomp.demon.co.uk/#Index
Second, yes , U are right :
SQL> col HITS for 999,999,999,999,999,999
SQL> select sum(dbbget + conget - pread) hits from x$kcbwds
2where inst_id = userenv('Instance');
HITS
-2,959,172,014
It's 8.0.5. The DB has been up since March,2001
-Original Message-
Sent: Tuesday, August 07, 2001 10:16 PM
To: Multiple recipients of list ORACLE-L
It is possible that after 4 months your stats
have wrapped around the ( ? 64 bit ?) limit
value for your platform. Check the actual
values from v$sysstat to see if some of them
have gone negative or appear to be
'counting backwards'.
Jonathan Lewis
Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html
-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 07 August 2001 19:52
|
|Dear gurus !
|I have a negative value for buffer cache hit ratio in my DB which is
up for
|some 4 months .
|Any ideas why ?
|Thanks in advance.
|
|SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
| 2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
| 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer
cache hit
|ratio"
| 4 FROM v$sysstat a;
|
|buffer cache hit ratio
|--
| -52.99284
|
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Andrey Bronfin
| 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: Jonathan Lewis
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: Andrey Bronfin
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: negative value for buffer cache hit ratio
Hi Christopher !
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from x$kcbwds
7 where inst_id = userenv('Instance') )
8 /
MISS_RATE
-
-3.90%
Strange , isn't it ?
The actual results are :
SQL> select total_waits misses
2from sys.v_$system_event
3 where event = 'db file sequential read' ;
MISSES
--
111086623
SQL> col HITS for 999,999,999,999,999,999
SQL> select sum(dbbget + conget - pread) hits from x$kcbwds
2where inst_id = userenv('Instance');
HITS
-2,959,172,014
Why is it negative ?
Do U think it's a buffer overflow or something ?
Thanks a lot !!!
By the way , i forgot what should i run to create those sys.x_$* synonyms
, i.e. sys.x_$kcbwds.
I had to connect as SYS and use x$kcbwds instead.
Thanks again !!!
-Original Message-
Sent: Tuesday, August 07, 2001 8:14 PM
To: Multiple recipients of list ORACLE-L
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
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: negative value for buffer cache hit ratio
Thanks a lot , Srini ! I have this feeling too. Unfortunately , bouncing the instance is not an option at the moment (it's up since March , 2001). The other problem is , when i'll restart the instance , the statistics will disappear , so i never know my hit ratio ;-( -Original Message- Sent: Tuesday, August 07, 2001 9:47 PM To: Multiple recipients of list ORACLE-L Kirti, Audrey, I was under the impression that negative numbers are caused by the numbers wrapping around i.e., the database has been up for a while and the statistics pile up and eventually exceed the defined format (e.g. value of 1000+ for a format of 9(3) - to use old COBOL representation !). Makes sense ? I have seen his before. If you bounce the database, all will be well ! Srini Chavali Oracle DBA Cummins Inc "Deshpande, Kirti" <[EMAIL PROTECTED]>@fatcity.com on 08/07/2001 01:13:52 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, August 07, 2001 12:51 PM > To: Multiple recipients of list ORACLE-L > Subject: negative value for buffer cache hit ratio > > > Dear gurus ! > I have a negative value for buffer cache hit ratio in my DB which is up > for > some 4 months . > Any ideas why ? > Thanks in advance. > > SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / > 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + > 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache > hit > ratio" > 4 FROM v$sysstat a; > > buffer cache hit ratio > -- > -52.99284 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > 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: Deshpande, Kirti 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: 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: Andrey Bronfin 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: negative value for buffer cache hit ratio
Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez <[EMAIL PROTECTED]> wrote: > Hi, > > My misses is > > MISS_RATE > - > 0.68% > > Is that good or bad, Oracle 8.0.5 Standard Edition, > Windows 2000. > > Is there a 8.1.7 Standard Edition Version ? > > Ramon Estevez > [EMAIL PROTECTED] > > = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: negative value for buffer cache hit ratio
Hi,
My misses is
MISS_RATE
-
0.68%
Is that good or bad, Oracle 8.0.5 Standard Edition, Windows 2000.
Is there a 8.1.7 Standard Edition Version ?
Ramon Estevez
[EMAIL PROTECTED]
---
Christopher
Thanks. This script ran from 7.3.4 to 8.1.6 with following result...
I hope misses less than 1% is not bad...Any comment
SQL> select to_char(100 * misses / (logical - physical + misses), '9990.00')
||
2 '%' miss_rate
3from ( select total_waits misses
4from sys.v_$system_event
5 where event = 'db file sequential read'),
6 ( select value physical
7 from sys.v_$sysstat
8where name = 'physical reads'),
9 ( select sum(value) logical
10 from sys.v_$sysstat
11where name like '%consistent read gets'
12 or name = 'db block gets');
MISS_RATE
-
0.90%
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 12:23:02 -0800
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL> show user
USER is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL> show user
user is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache
hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
RE: negative value for buffer cache hit ratio
Christopher
Thanks. This script ran from 7.3.4 to 8.1.6 with following result...
I hope misses less than 1% is not bad...Any comment
SQL> select to_char(100 * misses / (logical - physical + misses), '9990.00')
||
2 '%' miss_rate
3from ( select total_waits misses
4from sys.v_$system_event
5 where event = 'db file sequential read'),
6 ( select value physical
7 from sys.v_$sysstat
8where name = 'physical reads'),
9 ( select sum(value) logical
10 from sys.v_$sysstat
11where name like '%consistent read gets'
12 or name = 'db block gets');
MISS_RATE
-
0.90%
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 12:23:02 -0800
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL> show user
USER is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL> show user
user is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache
hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
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, se
Re: negative value for buffer cache hit ratio
Welcome Jonathan, I think most of the counter are limited by ub4maxval and that makes the negative hit ratio. Welcome again ! --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > It is possible that after 4 months your stats > have wrapped around the ( ? 64 bit ?) limit > value for your platform. Check the actual > values from v$sysstat to see if some of them > have gone negative or appear to be > 'counting backwards'. > = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: negative value for buffer cache hit ratio
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL> show user
USER is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL> show user
user is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache
hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
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: Christopher Spence
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
Re: negative value for buffer cache hit ratio
It is possible that after 4 months your stats have wrapped around the ( ? 64 bit ?) limit value for your platform. Check the actual values from v$sysstat to see if some of them have gone negative or appear to be 'counting backwards'. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 07 August 2001 19:52 | |Dear gurus ! |I have a negative value for buffer cache hit ratio in my DB which is up for |some 4 months . |Any ideas why ? |Thanks in advance. | |SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / | 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + | 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache hit |ratio" | 4 FROM v$sysstat a; | |buffer cache hit ratio |-- | -52.99284 | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Andrey Bronfin | 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: Jonathan Lewis 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: negative value for buffer cache hit ratio
Nothing is wrong in your database. You don't have the required X_$ views created. Change X_$ to X$ or create X_$ views as select * from X$ views. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: negative value for buffer cache hit ratio
Kirti, Audrey, I was under the impression that negative numbers are caused by the numbers wrapping around i.e., the database has been up for a while and the statistics pile up and eventually exceed the defined format (e.g. value of 1000+ for a format of 9(3) - to use old COBOL representation !). Makes sense ? I have seen his before. If you bounce the database, all will be well ! Srini Chavali Oracle DBA Cummins Inc "Deshpande, Kirti" <[EMAIL PROTECTED]>@fatcity.com on 08/07/2001 01:13:52 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, August 07, 2001 12:51 PM > To: Multiple recipients of list ORACLE-L > Subject: negative value for buffer cache hit ratio > > > Dear gurus ! > I have a negative value for buffer cache hit ratio in my DB which is up > for > some 4 months . > Any ideas why ? > Thanks in advance. > > SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / > 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + > 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache > hit > ratio" > 4 FROM v$sysstat a; > > buffer cache hit ratio > -- > -52.99284 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > 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: Deshpande, Kirti 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: 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: negative value for buffer cache hit ratio
Ran this query under 8i(HP-UX 11)
SQL> show user
USER is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL> show user
user is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache
hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
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: Christopher Spence
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).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
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: negative value for buffer cache hit ratio
1.- Go to this link of Steve Adam's site: http://www.ixora.com.au/scripts/cache.htm You gonna find a script to estimate the miss rate of the buffer cache based on the assumptions that certain operations are not well compute by statistics as direct path operations. 2.- From 8.X you should rely on v$buffer_pool_statistics from the script $ORACLE_HOME/rdbms/admin/catperf.sql accounting on the several pools you could have. 3.- Remember that even on 8.X, when the database has been opened for a long time and have had a lot of transactions, the value of statistics gets so big that reaches the maximum value and then begins from 0 again. This usually happens with 7.X. Regards. --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > Andrey, > I am not into cache hit ratios, but got curious > about your negative value. > > So I casually searched Metalink for 'negative value > for hit ratio'. And got > a hit on 'MROC: I am receiving a negative Buffer > Cache hit ratio' thread. > You may want to check it out, there seems to be a > new formula for hit ratio > computation in 8i/9i. > > HTH, > > Regards, > > - Kirti Deshpande > Verizon Information Services >http://www.superpages.com > > > -Original Message- > > From: Andrey Bronfin > [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, August 07, 2001 12:51 PM > > To: Multiple recipients of list ORACLE-L > > Subject:negative value for buffer cache hit ratio > > > > > > > Dear gurus ! > > I have a negative value for buffer cache hit ratio > in my DB which is up > > for > > some 4 months . > > Any ideas why ? > > Thanks in advance. > > > > SQL> SELECT (1 - (SUM(DECODE(a.name,'physical > reads',value,0)) / > > 2 (SUM(DECODE(a.name,'consistent > gets',value,0)) + > > 3 SUM(DECODE(a.name,'db block > gets',value,0) * 100 "buffer cache > > hit > > ratio" > > 4 FROM v$sysstat a; > > > > buffer cache hit ratio > > -- > > -52.99284 > > > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Andrey Bronfin > > 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: Deshpande, Kirti > 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). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: negative value for buffer cache hit ratio
Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, August 07, 2001 12:51 PM > To: Multiple recipients of list ORACLE-L > Subject: negative value for buffer cache hit ratio > > > Dear gurus ! > I have a negative value for buffer cache hit ratio in my DB which is up > for > some 4 months . > Any ideas why ? > Thanks in advance. > > SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / > 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + > 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache > hit > ratio" > 4 FROM v$sysstat a; > > buffer cache hit ratio > -- > -52.99284 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > 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: Deshpande, Kirti 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: negative value for buffer cache hit ratio
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
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: Christopher Spence
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).
