| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
com.banilejas_at_codetel.net.do
---
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
3 from ( select total_waits misses
4 from sys.v_$system_event
5 where event = 'db file sequential read'),
6 ( select value physical
7 from sys.v_$sysstat
8 where name = 'physical reads'),
9 ( select sum(value) logical
10 from sys.v_$sysstat
11 where name like '%consistent read gets'
12 or name = 'db block gets');
MISS_RATE
---------
0.90%
Regards
MOHAMMAD RAFIQ
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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
2 from ( select total_waits misses
3 from 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
2 from ( select total_waits misses
3 from 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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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: andreyb_at_elrontelesoft.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: cspence_at_FuelSpot.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).
_________________________________________________________________
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: rafiq9857_at_hotmail.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: cspence_at_FuelSpot.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).
_________________________________________________________________
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: rafiq9857_at_hotmail.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ramon Estevez
INET: com.banilejas_at_codetel.net.do
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 Tue Aug 07 2001 - 15:35:47 CDT
 |
 |