Diving Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2) Oracle Tips by Mike Ault

Monitoring Library and Data Dictionary Caches

The output from the script in Figure 11 is shown in Figure 12.

Date: 11/21/98                                              Page:   1

Time: 02:59 PM               DD Cache Hit Ratio             SYSTEM

                              ORTEST1 database

    RATIO
---------
.01273172

Figure 12: Example Output From Data Dictionary Script

The ratio reported from the script in Figure 11 should always be less than 1. The ratio corresponds to the number of times out of 100 that the database engine sought something from the cache and missed. A dictionary cache miss is more expensive than a data block buffer miss so if your ratio gets near 1 increase the size of the shared pool since the internal algorithm isn't allocating enough memory to the data dictionary caches.

Guideline 8: In any shared pool, if the overall data dictionary cache miss ratio exceeds 1 percent, increase the size of the shared pool.

---

For more information on this topic, I recommend Don Burleson's latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.