High libray cache reloads
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?
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.
how about looking into AWR and ADDM reports?
Try hard to get what you like OR you will be forced to like what you get.
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.
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?
Click Here to Expand Forum to Full Width