ORACLE cache; SQL invalidations
- Article Type: General
- Product: Aleph
- Product Version: 20
Description:
We've recently had some issues with ORACLE database cache. Our university IT staff checked but didn't find anything unusual....
This is a custom check to monitor the library cache miss ratio in Oracle. This generally indicates that there may be performance problems due to insufficient shared pool space, but from what I've been able to tell, there is plenty of space in the shared pool. So, it might be that ALEPH is generating non-resuable SQL that is causing the misses or similar. The V$LIBRARYCACHE view also hints that there are quite a number of "INVALIDATIONS" which means shareable SQL was invaliadated due to some other operation.
Here's a doc from Oracle explaining some of the shared pool, cache and other memory concepts:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm
You might want to contact ALEPH and see if they have any advice and might explain why we have so many SQL invalidations.
sys@ALEPH20> select namespace,pins,pinhitratio,reloads,invalidations from v$librarycache;
NAMESPACE PINS PINHITRATIO RELOADS INVALIDATIONS
--------------- ---------- ----------- ---------- -------------
SQL AREA 68499143 .686148482 11269408 2717699
TABLE/PROCEDURE 220409977 .975238331 2607442 307
BODY 41623749 .998066128 36093 0
TRIGGER 1632580 .993419618 1587 0
INDEX 1713490 .813222721 71122 0
CLUSTER 613653 .984391831 218 0
OBJECT 0 1 0 0
PIPE 0 1 0 0
JAVA SOURCE 0 1 0 0
JAVA RESOURCE 0 1 0 0
JAVA DATA 0 1 0 0
Resolution:
Our recommendations, according to the number of users on the server and the size of the database and server's resources:
Enlarge the number of pc and web server to 10 each (currently 5).
Enlarge the SGA size to 3.5G (currently 700M)
Although Oracle suggests that the invalidations number be near zero, Aleph works this way and it will not be changed unless a specific problem is encountered which can be fixed by reducing the number of invalidations.
- Article last edited: 10/8/2013