Library Cache Miss Ratio is High
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Library Cache Miss Ratio is High

  1. #1
    Join Date
    Oct 2001
    Location
    New Jersey
    Posts
    20

    Question

    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


  2. #2

    Talking

    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.
    The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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
    --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width