Monitoring shared Pool Statistics
There are at least two statistics related to the shared pool that you should monitor on a regular basis:
- the GETHIT ratio and
- the GETMISS ratio .
The gethit ratio tells you how often a SQL statement, or PL/SQL block, is found in the shared pool, already parsed, and with an execution plan that can be reused.
This query returns the gethit ratio for several different object types:
SELECT namespace, gethitratio
The closer the gethit ratio is to 1, the better. Values above 0.90 are generally accepted as indicators of good performance.
A value of 0.90 means that only 10% of SQL statements need to be parsed, which means the shared pool is large enough to hold the 90% that are most frequently used. Another statistic to look at is the getmiss ratio. This applies to the data dictionary cache, and tells you how often Oracle has to go to disk for data dictionary information as opposed to finding that information already in memory, in the shared pool.
The following query will give you the getmiss ratio:
SELECT SUM(getmisses)/SUM(gets) getmiss_ratio
In this case, lower values are better. Oracle recommends keeping this number below 0.15.