Shared Pool Used never exceeds 91% in Oracle 11g (4260510)

Since moving from Oracle 9i database to 11g using the same version of Spotlight I am seeing many more SQL libray cache miss alarms but Shared Pool Used has never exceeded 91% and is typically closed to 80%. It took a few weeks for the usage reported to grow from 35% to 80%.

Im confused as to how those two things could exist at the same time, the alarms and < 100% used. In our 9i database the Shared Pool Used was always 100% Used.

What am I not understanding, how could we not be using all of the Shared Pool or is not all usage being captured in 11g?

The Spotlight Library cache miss rate is not directly related to the shared pool used. The shared pool used percentage is calculated using the following:

SELECT t.mb total_mb,
ROUND (f.mb, 2) free_mb,
ROUND (100 * (1 - (f.mb / t.mb)), 2) used_pct
FROM (SELECT bytes / 1024 / 1024 mb
FROM v$sgastat
WHERE name = free memory AND pool = shared pool) f,
(SELECT current_size / 1024 / 1024 mb
FROM v$sga_dynamic_components
WHERE component = shared pool) t

The library cache miss rate is calculated using v$librarycache.

As for why there is a high library cache miss rate when the shared pool is not 100% full - it seems Oracle will not extend the SQL area indefinitely to fill the shared pool in 11g ** This would be a question for Oracle **

It is possible there is an application that is not using bind variables and is using literals

Try upgrading to Spotlight on Oracle 6.1 in order to get the new Parse drilldown (under SQL &amp; Application workload) - this will give insight into what applications / SQLs / Users are causing this and perhaps the application could be modified or try setting cursor_sharing to SIMILAR or FORCE.

Spotlight on Oracle v6.1 can be downloaded from Support Link (see steps below)

1. Visitwww.quest.com
2. Click on Support -> Downloads and Updates
3. In the Products search field, find Spotlight on Oracle 6.1
4. Download and install the latest Spotlight on Oracle 6.1 (see documentation attached to case and this email)