RelationalDBDesign RelationalDBDesign

Oracle Instance  «Prev  Next»
Lesson 5Tuning the data dictionary cache
ObjectiveQuery 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 (should be higher than 90 else increase shared_pool_size in        
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",

An excerpt of it is shown below.
  1. This describes the sum of the data dictionary "gets" for objects. Here, there have been 409,000 total "gets" for dictionary objects
  2. 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).
  3. This is the ratio of gets to get misses. Overall, the data dictionary cache hit ratio is 97%, which is quite acceptable

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