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 DATA DICT HIT RATIO
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",
"DATA DICT CACHE HIT RATIO"