Shared Pool Sizing

This blog entry talks about how the library cache and dictionary cache statistics can be monitored to ensure they are optimally sized.

• Shared pool: Library cache statistics

When sizing the shared pool, the goal is to ensure that SQL statements that will be executed multiple times are cached in the library cache, without allocating too much memory.

The statistic that shows the amount of reloading (that is, reparsing) of a previously cached SQL statement that was aged out of the cache is the RELOADS column in the  V$LIBRARYCACHE view. In an application that reuses SQL effectively, on a system with an optimal shared pool size, the RELOADS statistic will have a value near zero.

The INVALIDATIONS column in V$LIBRARYCACHE view shows the number of times library cache data was invalidated and had to be reparsed. INVALIDATIONS should be near zero. This means SQL statements that could have been shared were invalidated by some operation (for example, a DDL). This statistic should be near zero on OLTP systems during peak loads.

The following query gives info about reloads and invalidations:

sql> col  namespace format a30
SQL> SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
  FROM V$LIBRARYCACHE ORDER BY NAMESPACE;

NAMESPACE                            PINS    PINHITS    RELOADS INVALIDATIONS
------------------------------ ---------- ---------- ---------- -------------
APP CONTEXT                           525        524          0             0
BODY                                 7114       6993          0             0
CLUSTER                               529        496          0             0
DBINSTANCE                              0          0          0             0
DBLINK                                  0          0          0             0
EDITION                               482        479          0             0
INDEX                                  44          0          0             0
OBJECT ID                               0          0          0             0
QUEUE                                7316       7300          1             0
RULESET                                 7          6          0             0
SCHEMA                                  0          0          0             0
NAMESPACE                            PINS    PINHITS    RELOADS INVALIDATIONS
------------------------------ ---------- ---------- ---------- -------------
SQL AREA                         55853208   58144246         35           112
SUBSCRIPTION                           12          7          0             0
TABLE/PROCEDURE                     32025      28475         91             0
TRIGGER                               156        145          0             0
USER AGENT                              1          0          0             0
16 rows selected.

 

Another key statistic is the amount of free memory in the shared pool at peak times. The amount of free memory can be queried from V$SGASTAT, looking at the free memory for the shared pool. Optimally, free memory should be as low as possible, without causing any reloads on the system.

You can find out the free memory for the shared pool with the following query:

SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';


Lastly, a broad indicator of library cache health is the library cache hit ratio. This value should be considered along with the other statistics discussed in this section and other data, such as the rate of hard parsing and whether there is any shared pool or library cache latch contention.

To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)

The following query displays the library cache hit ratio:

select sum(pinhits)/ sum(pins) from v$librarycache;

• Shared pool: Dictionary cache statistics

Typically, if the shared pool is adequately sized for the library cache, it will also be adequate for the dictionary cache data.

Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.

Each row in the V$ROWCACHE view contains statistics for a single type of data dictionary item. These statistics reflect all data dictionary activity since the most recent instance startup. The columns in the V$ROWCACHE view that reflect the use and effectiveness of the data dictionary cache are listed below.

PARAMETER
 Identifies a particular data dictionary item. For each row, the value in this column is the item prefixed by dc_. For example, in the row that contains statistics for file descriptions, this column has the value dc_files.

GETS
 Shows the total number of requests for information about the corresponding item. For example, in the row that contains statistics for file descriptions, this column has the total number of requests for file description data.

GETMISSES
 Shows the number of data requests which were not satisfied by the cache, requiring an I/O.

MODIFICATIONS
 Shows the number of times data in the dictionary cache was updated.


Use the following query to monitor the statistics in the V$ROWCACHE view over a period while your application is running. The derived column PCT_SUCC_GETS can be considered the item-specific hit ratio:

column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter  , sum(gets)  , sum(getmisses)  , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
     , sum(modifications)                     updates
  FROM V$ROWCACHE
 WHERE gets 0
 GROUP BY parameter;


It is also possible to calculate an overall dictionary cache hit ratio using the following formula; however, summing up the data over all the caches will lose the finer granularity of data:
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;