Dictionary cache

The Oracle data dictionary is a collection of database tables, owned by the SYS and SYSTEM schemas, that contain the metadata about the database, its structures, the privileges and roles of database users. The data dictionary cache holds cached blocks from the data dictionary. Data blocks from tables in the data dictionary are used continually to assist in processing user queries and other DML commands. If the data dictionary cache is too small, requests for information from the data dictionary will cause extra I/O to occur; these I/O-bound data dictionary requests are called recursive calls and should be avoided by sizing the data dictionary cache correctly.

Fast Access

Oracle constantly accesses the data dictionary during database operation to validate user access and to verify the state of schema objects. All information is stored in memory using the least recently used (LRU) algorithm. The data dictionary cache is accessed for each SQL statement at parse time and again at runtime when the SQL gathers dynamic storage for execution.

Dictionary Cache Hit Ratio

The data dictionary cache hit ratio is used to measure the ratio of dictionary hits to misses meaning how many times dictionary cache has the dictionary tables data blocks for requested information and how many times Oracle has to do I/O for missing on cache. For optimal performance, the overall dictionary cache hit ratio should be greater than 90%

To monitor the efficiency of the Data Dictionary Cache, below sql statement can be used:

select (1-(sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 "Dictionary Hit Rate" from v$rowcache;

A figure of 90–95 % should be maintained; if the rate starts to drop, SHARED_POOL_SIZE should be increased