who could explain me with clear terms the difference between gets and pins stats for the library cache (v$librarycache).
I think the documentation is not clear on this point.
Ok so i have to start from the beginning.Some terms you must remember:
Pins: hits in memory(thats good)
Gets: hits from file .
Reloads: Errors in the sql that prevent execution. Suppose and object in the SQL is modified. The shared SQL area becomes invalid and the statement must be reparsed before execution. This process is called reloading.
Reloads->High and Invalidations
->Low means increase Memory
Reloads->Low and Invalidations
->High means misses
There are two types of hit ratios in Library cache. One is GETHITRATIO(parse phase) and the other is PINHITRAIO(excution phase).
The GETHITRAIO is equal to the GETHITS/GETS. This ratio needs to remain GREATER THAN 90%.
SQL>select gethits "Hits",gets "Misses",(gethits/gets)*100 "GETHITRATIO" from v$librarycache;
Should be greater than 90% or otrherwise increase the size of shared pool.
Calculating the PINHITRATIO:
sum(reloads) 'Cache Misses',
The PINHITRATIO should be greater than 99%.
Data Dictionary Cache( Row cache):
It's ratio is always low at database startup but soon increases after transactions and queries.
select parameter,getmisses, (gets/getmisses)*100 "Hits" from v$rowcache;
Should be >85%
GETS: Number of requests on a object.
GETMISSES: Number of requests resulting in cache misses.
If there are too many getmisses, increase the SHARED_POOL_SIZE.
You cannot succeed if you fear to face challenges.
BE(CS) , OCP 8/8i.
Click Here to Expand Forum to Full Width