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

From: Schoen Volker <v.schoen_at_inplan.de>
Date: Mon, 28 May 2001 04:44:23 -0700
Message-ID: <F001.00311054.20010528041521@fatcity.com>

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). Received on Mon May 28 2001 - 06:44:23 CDT