Donald K. Burleson

Oracle and Expert Systems Technology

Oracle - Tuning the Dictionary Cache

The data dictionary cache is used to hold rows from the internal Oracle metadata tables, including SQL stored in packages.  The data dictionary data is maintained in a separate cache called the dictionary cache which is stored in 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.

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 V$ROWCACHE view is used to measure dictionary cache activity. Three columns are of interest: Data Dict., gets, and getmisses. The first column, Data Dict., describes the type of dictionary object that has been requested. The second parameter, gets, provides the total number of requests for objects of that type. The last column, getmisses, counts the number of times Oracle had to perform a disk I/O to retrieve a row from its dictionary tables.

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.

The data dictionary cache hit ratio can be measured using the script shown below.

dict.sql - displays the dictionary cache hit ratio

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;
 

=========================
DATA DICT HIT RATIO
=========================
(should be higher than 90 else increase shared_pool_size in init.ora) 

Fri Feb 23                                                      page    1
                               dbname Database
                          Data Dictionary Hit Ratios 

Data Dict. Gets Data Dict. cache misses DATA DICT CACHE HIT RATIO
-----------------------------------------------------------------
409,288          11,639                  97
 

1 row selected. 

col parameter heading 'Parameter Name'    format a20       justify c trunc
col count     heading 'Entries|Allocated' format 9999990   justify c
col usage     heading 'Entries|Used'      format 9999990   justify c
col gets      heading 'Gets'              format 9999990   justify c
col getmisses heading 'Get|Misses'        format 9999990   justify c
col pctused   heading 'Pct|Used'          format 990.0     justify c
col pctmisses heading 'Pct|Misses'        format 990.0     justify c
col action    heading 'Rec''d|Action'     format a6        justify c 

select
  parameter,
  count,
  usage,
  100*nvl(usage,0)/decode(count,null,1,0,1,count) pctused,
  gets,
  getmisses,
  100*nvl(getmisses,0)/decode(gets,null,1,0,1,gets) pctmisses,
  decode(
    greatest(100*nvl(usage,0)/decode(count,null,1,0,1,count),80),
    80, ' Lower',
    decode(least(100*nvl(getmisses,0)/decode(gets,null,1,0,1,gets),10),
    10, '*Raise', ' Ok')   ) action
from
   v$rowcache
order by   1
; 
prompt
prompt
prompt Rec'd Action is based on maintaining >80% Entries Used and <10% Gets

SQL>@dict
 

Entries  Entries   Pct              Get     Pct   Rec'd
   Parameter Name    Allocated   Used    Used    Gets    Misses  Misses Action
-------------------- --------- -------- ------ -------- -------- ------ ------
dc_column_grants             1        0    0.0        0        0    0.0  Lower
dc_columns               13524    13461   99.5   577710    11741    2.0  Ok
dc_constraint_defs         334      318   95.2      614      318   51.8 *Raise
dc_constraint_defs           1        0    0.0        0        0    0.0  Lower
dc_constraints               8        3   37.5      135       48   35.6  Lower
dc_database_links            7        2   28.6       16        2   12.5  Lower
dc_files                     1        0    0.0        0        0    0.0  Lower
dc_free_extents           2163     1281   59.2   155862     8183    5.3  Lower
dc_histogram_data            1        0    0.0        0        0    0.0  Lower
dc_histogram_defs           25        0    0.0     3699     3699  100.0  Lower
dc_indexes                 179      169   94.4     1810      234   12.9 *Raise
dc_object_ids              155      145   93.5     1405      586   41.7 *Raise
dc_objects                1394     1386   99.4    13688     1784   13.0 *Raise
dc_profiles                  1        0    0.0        0        0    0.0  Lower
dc_rollback_segments        35       33   94.3    11465       32    0.3  Ok
dc_segments                328      313   95.4    14066     1009    7.2  Ok
dc_sequence_grants           1        0    0.0        0        0    0.0  Lower
dc_sequences                14        2   14.3      112        2    1.8  Lower
dc_synonyms                 23       18   78.3       92       18   19.6  Lower
dc_table_grants           4819     4783   99.3    12076     4791   39.7 *Raise
dc_tables                  729      713   97.8    73300     1147    1.6  Ok
dc_tablespace_quotas        23        6   26.1     8957        6    0.1  Lower
dc_tablespaces              13        6   46.2     6853        6    0.1  Lower
dc_tablespaces              19        6   31.6     6053        6    0.1  Lower
dc_truncates                93       86   92.5       86       86  100.0 *Raise
dc_used_extents           4776     4766   99.8    10339     9664   93.5 *Raise
dc_user_grants              53       24   45.3    20321       24    0.1  Lower
dc_usernames                21       20   95.2     5370       20    0.4  Ok
dc_users                    29       27   93.1    22510       27    0.1  Ok
dc_users                     1        0    0.0        0        0    0.0  Lower
 

30 rows selected.

Rec'd Action is based on maintaining >80% Entries Used and <10% Gets

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.  Click here for more books by Donald K. Burleson.