High libray cache reloads
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: High libray cache reloads

  1. #1
    Join Date
    Apr 2011
    Posts
    2

    High libray cache reloads

    Hi,

    We are experiencing from time to time an oracle error : ORA-04031.

    We've detected that are %reloads is very high of the BODY namespace . Is there anything we can check what is causing this?

    Command:
    SELECT NAMESPACE, PINS, PINS-PINHITS LOADS, RELOADS,INVALIDATIONS, 100 * (RELOADS - INVALIDATIONS) / (PINS-PINHITS) "%RELOADS" FROM V$LIBRARYCACHE WHERE PINS >0 ORDER BY NAMESPACE;

    NAMESPACE PINS LOADS RELOADS INVALIDATIONS %RELOADS
    BODY 694.662 4.176 2.690 0 64
    CLUSTER 4.786 86 0 0 0
    INDEX 53.067 913 0 0 0
    SQL AREA 81.820.418 111.392 23.930 5.143 17
    TABLE/PROCEDURE 31.314.703 100.736 25.518 0 25
    TRIGGER 2.046.015 6.184 3.355 0 54

    Any help is welcome. Thanks.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    how about looking into AWR and ADDM reports?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Assuming this is at least an Ora10g database I would start by checking 4031 trace file in either user_dump_dest or background_dump_dest directories - that would be helpful to pinpoint root cause.

    Either way, even if it is hard to read the unformatted report I think it shows zero invalidations for BODY namespace, is that correct? If this is the case chances are shared_pool is too small but, better investigate trace file before doing anything else.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Apr 2011
    Posts
    2
    Thx for the replies.

    It is an Oracle9i database. The invalidations are indeed 0 for body namespace. Resizing the shared_pool is probably the best option?

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