For one thing, which you did not mention, when was the last time this
instance was restarted? It is normal to see low cache hit ratios
shortly after a startup. Second did someone do something, like select *
from the largest table, that could have caused the cache to completely
flush? Also do you have something going on that could cause a lot of
full table scane, like bad statistics? There are a lot of other user
level things like this that can cause problems. I have DB's that stay
up for a year at a time don't have low cache hit ratios without good
cause.
Dick Goulet
-- Reply Separator --
Author: Schoen Volker [EMAIL PROTECTED]
Date: 5/28/01 4:15 AM
Hi list,
I have a curious problem with my buffer hit ratio on Oracle 8.0.5, Unix
HP
UX 11.0. When I run the following select, I get a hit Rati about 8 %.
select sum(decode(NAME, 'consistent gets',VALUE, 0)) ConsistentGets,
sum(decode(NAME, 'db block gets',VALUE, 0))DBBlockGets,
sum(decode(NAME, 'physical reads',VALUE, 0)) PhysicalReads,
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) HitRatio
from sys.v_$sysstat;
Output:
CONSISTENTGETS DBBLOCKGETS PHYSICALREADS HITRATIO
37104235 166477 35109886
5,8
I know the application which works with this instance. I know that hit
ratio
have to be higher, cause application is tuned and there is not much data
in
the database. In other environments (Windows NT) I get buffer hit ratios
about 90 - 99 %. So my question: Is there something on OS-level (HP UX
11.0)
I have to configure to get higher buffer hit ratios?
Here is the environment:
Oracle 8.0.5 on HP UX 11.0
512 RAM
INIT.ORA parameters:
compatible 8.0.0
cpu_count1
db_block_size4096
db_block_buffers 5
shared_pool_size 7000
hash_area_size 131072
sort_area_size 131072
large_pool_size 0
log_buffer 163840
TIA
Volker Schön
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Schoen Volker
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).
