Library Cache Miss Ratio is High

[mvanhouten]

Hi,

I am receiving Alters from Oracle Enterprise Manager that the library cache miss ration is high, Current Ration: 4.1. My SGA is setup as follows:

shared_pool_size = 100 mg
shared_pool_reserved_size = 5 mg
large_pool_size = .5 mg
db_block_buffers = 3200
db_block_size = 16384
sort_area_size = 2 mg
sort_area_retain_size = 1 mg

My server has 4gig of memory, 2 processors and is running two instances that are setup identically.

Does anybody have any suggestions where I can look to fix this problem?

Thank you for your help.

Mike

[birdax1]

A high library cache miss ratio could be caused by excessive parsing. If your application does not use bind variables then it will perform lots of parsing and have the possibility of not getting the library cache latch.

To determine if this is the case look at sql_text in v$sqlarea and check if bind variables are being used. Also you can check the value of "parse count" in v$sysstat. Additionally, you can query v$session_wait at regular intervals to determine if any sessions are waiting on the library cache latch.

If you have lots of different sql or pl/sql going through your database then you will need a big shared pool, although from my experience 250Mb is big.

[Shestakov]

birdax1 describe first and most probable problem with Library Cache.
next (not last) problem is number of "reloads" of objects.
u can check it :

select namespace, reload from v$librarycache;
or
select owner,name,loads,executions from v$db_object_cache;
--
-- where loads > xx
-- or
-- where executions - loads > xxx
--