To determine what options we have to tune Row Cache in Oracle7 through
SCOPE & APPLICATION
This article is intended for Oracle Support Analysts, Oracle Consultants and
What is Row Cache?
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 dictionary
items. 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:
Identifies a particular data dictionary item. For each row, the value in this
column is the item prefixed by dc_.
Shows the total number of requests for information on the corresponding item.
Shows the number of data requests which were not satisfied by the cache.
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:
, 100*sum(gets – getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
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
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.
Oracle8i Designing and Tuning for Performance
Oracle9i Performance Guide and Reference
Bookmarks Admin Profile Feedback Sign Out Help