-
Title
Spotlight reports SQL-Library-Cache missrate 100% -
Description
When Oracle Parameter cursor_sharing is set to EXACT, then Spotlight
alerts me about "SQL-Library-Cache missrate 100%" -
Cause
Our alarms are based on deltas of the following query:
SELECT namespace, gets, gethits, pins, pinhits, reloads
FROM v$librarycache
ORDER BY namespace
The problem can be illustrated by running it twice (to gather deltas for the alarm), about 15 seconds apart.
Example:
Data extract - result set 1 (subset)
NAMESPACE GETS GETHITS
--------------- ---------- ----------
SQL AREA 370390 189016
Data extract - result set 2 (subset)
NAMESPACE GETS GETHITS
--------------- ---------- ----------
SQL AREA 370410 189016
Alarm is based in deltas of the above, effectively 100 * (delta(Gets)-delta(Gethits))/delta(Gets). Since the Gethits don't change, the result is 100% miss rate.
So the issue would appear to be that when cursor_sharing is in EXACT mode, Gethits from v$librarycache are not changing. -
Resolution
If CURSOR_SHARING is set to FORCE or SIMILAR then we expect some or all of the misses to be removed and the alarm to stop firing.
The high miss rate in the SQL area can lead to a high parse overhead (excessive CPU) and possibly to library cache latch contention. If either of these are occurring then there is a legitimate performance issue probably caused by an application which does not use bind variables. If, however, neither of these symptoms are present - or the user simply determines that there is nothing that they can do about the problem - then they can turn off the alarm (by adjusting the thresholds or snoozing).
There is no reason to believe that Spotlight is reporting incorrectly, however.