Re: Buffer Hit Ratio < 10% on UNIX HP/UX 11.0

From: <dgoulet_at_vicr.com>
Date: Mon, 28 May 2001 18:33:19 -0700
Message-ID: <F001.003114C7.20010528181522@fatcity.com>

 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 <v.schoen_at_inplan.de> 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_count            1
db_block_size        4096
db_block_buffers     50000
shared_pool_size     70000000
hash_area_size       131072
sort_area_size       131072
large_pool_size      0
log_buffer           163840

TIA Volker Schön
E-Mail: mailto:v.schoen_at_inplan.de
http://www.inplan.de

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Schoen Volker
  INET: v.schoen_at_inplan.de

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:
  INET: dgoulet_at_vicr.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 Mon May 28 2001 - 20:33:19 CDT