潛水珽的異想世界

Row Cache is another name of Dictionary Cache which is the part of the Shared Pool that contains Data Dictionary objects definitions available in memory to speed up Dicionary operations. Before Oracle7, every Dictionary object had a seperate init.ora parameter that controls it, these parameters were known as the "DC" parameters,but starting from Oracle7 all Dictionary cache objects are automatically controlled within the shared pool by the one parameter Shared_Pool_Size.

Monitoring Row Cache
--------------------

The V$ROWCACHE view contains statistics for each single type of the data dictionaryitems. These statistics reflect all data dictionary activity since the most recent instance startup. These columns in the V$ROWCACHE view reflect the efficiency of the Row Cache:

PARAMETER
Identifies a particular data dictionary item. For each row, the value in
this column is the item prefixed by dc_.

GETS
Shows the total number of requests for information on the correspondingitem.

GETMISSES
Shows the number of data requests which were not satisfied by the cache.

MODIFICATIONS
Shows the number of times data in the dictionary cache was updated.

Use the following query to monitor the statistics in the V$ROWCACHE view over a period of time while your application is running:

SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;



Another important thing to watch is Row Cache Objects Latch, this latch comes into play when a process attempts to access or update an entry in the Dictionary Cache. This latch contention may cause slow performance or Database operations to hang.

How to Tune Row Cache
---------------------

Tuning Row cache is so limited, some resources point to it as non-tunable,so if the pct_succ_gets above is not getting closer to "1" or if you detect a high "Row Cache Objects" Latch contention in performance tuning reports then you need to add more shared pool by either increasing SHARED_POOL_SIZE or avoid Sharde Pool Fragmentation.Alternatively, configuring the library cache to an acceptable size usually ensures that the data dictionary cache is also properly sized. So tuning Library Cache will tune Row Cache indirectly. However ,the following tips have direct and important effect on tuning Row Cache:

1. Use Locally Managed tablespaces for your application objects especially indexes, this will decrease Row Cache locks in a surprising fashion and consequently avoid common hanging problems.

2. Review and amend your database logical design , a good example is to merge or decrease the number of indexes on tables with heavy inserts.