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"
An excerpt of it is shown below.
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.