Lesson 5	Tuning the data dictionary cache
Objective	Query the data dictionary cache.

Query the Oracle Data Dictionary Cache

The data dictionary cache is used to hold rows from the internal Oracle metadata tables, including SQL stored in packages. The data for the data dictionary is maintained in a separate cache called the dictionary cache which is stored in the shared SQL area. This is accessed for each SQL statement at parse time and also at runtime for dynamic storage allocation. Cache hits avoid the necessity for recursive calls and performance on SQL statements improves.

Dictionary Cache Misses

When a package is invoked, Oracle first checks the dictionary cache to see if the package is already in memory. Of course, a package will not be in memory the first time it is requested, and Oracle will register a dictionary cache miss. Consequently, it is virtually impossible to have an instance with no dictionary cache misses, because each item must be loaded once. The data dictionary cache hit ratio is used to measure the ratio of dictionary hits to misses. Bear in mind, however, that this ratio is only good for measuring the average hit ratio for the life of the instance.

Measure Data Dictionary cache activity

The V$ROWCACHE view is used to measure dictionary cache activity. The data dictionary cache hit ratio can be measured using the script, dict.sql, shown below.

PROMPT
PROMPT
PROMPT         ========================= 
PROMPT         DATA DICT HIT RATIO 
PROMPT         ========================= 
PROMPT (should be higher than 90 else increase shared_pool_size in        
       init.ora)
PROMPT
COLUMN "Data Dict. Gets"            FORMAT 999,999,999
COLUMN "Data Dict. cache misses"    FORMAT 999,999,999
SELECT sum(gets) "Data Dict. Gets",
       sum(getmisses) "Data Dict. cache misses",
       trunc((1-(sum(getmisses)/sum(gets)))*100)

       "DATA DICT CACHE HIT RATIO"
FROM V$ROWCACHE;

An excerpt of it is shown below.

This describes the sum of the data dictionary "gets" for objects. Here, there have been 409,000 total "gets" for dictionary objects

This counts the number of times Oracle had to perform a disk I/O to retrieve a row from its dictionary tables. Here, there were 11,000 caches misses (where the object was once loaded, but had to be re-loaded since it was flushed from the pool).

This is the ratio of gets to get misses. Overall, the data dictionary cache hit ratio is 97%, which is quite acceptable

Data Dictionary Gets Metric

The Data Dictionary gets Oracle metric is the total number of consistent gets (logical I/O) of the RAM data block buffer. This alert looks at the Oracle data dictionary to compute data dictionary gets, data dictionary cache misses, and the data dictionary hit ratio, and then alerts the DBA to times when requests for data dictionary metadata are high. This problem can sometimes be relieved by increasing the shared_pool_size init.ora parameter, using the following syntax.

Alter system set shared_pool_size = 101m;

In the next lesson, we will discuss tuning implications for session memory.