|Lesson 13||Shared pool hit ratios|
|Objective|| Monitor the efficiency of the shared pool.|
Shared Pool hit Ratios
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
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.
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.