Lesson 13	Shared pool hit ratios
Objective	Monitor the efficiency of the shared pool.

Shared Pool hit Ratios

Two key indicators can be used to monitor the efficiency of the shared pool. One indicator is the dictionary cache hit ratio, which tells you how frequently data dictionary information is found in memory versus being read from disk. The other indicator is the library cache hit ratio, which tells you how often SQL statements are found in the library cache versus having to be reparsed.

Computing Dictionary and library cache hit ratios

The following slide show describes how to compute both of these ratios:

1) Shared Pool Hit-Ratios 1

2) Shared Pool Hit-Ratios 2

3) Shared Pool Hit-Ratios 3

4) Shared Pool Hit-Ratios 4

❮

❯

Improving the results

The usual way to improve either of these two hit ratios is to increase the size of the shared pool. You do that by increasing the value of the

SHARED_POOL_SIZE

initialization parameter. Remember that you need to stop and restart the database in order for that change to take effect.
Sometimes a poor library cache hit ratio can be caused by a poorly designed application, one that constantly builds and executes different SQL statements. The library cache facilitates the reuse of execution plans for SQL statements that are identical to those issued previously. If an application is generating a different statement each time, the library cache hit ratio will be poor.

Caveats

All the same caveats apply to the dictionary cache and library cache hit ratios as apply to the buffer cache hit ratio. They are likely to be initially low after starting a database, so you should look at them after the database has been up and running for a while. You may also want to compute them for specific periods of time, rather than using the cumulative values from when the database last started.
In the next lesson, you will generate statistics for your database.