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 series of images 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.
