Learning Dynamic Performance Table (--v$rowcache)

Learning Dynamic Performance Table

Section - article --v$rowcache 2007.6.12

This view displays statistics for the data dictionary cache (also called Rowcache). Each record contains different types of data dictionary cache data Statistics, note that the data dictionary cache has a hierarchical difference, so the same cache name may appear more than once.

V$rowcache Common Columns

• PARAMETER: Cache Name
• Count: Total Cache entries
• USAGE: Number of cache entries that contain valid data
• GETS: Total number of requests
• Getmisses: Number of request failures
• SCANS: Number of Scan requests
• Scanmisses: Number of failed scan requests
• Modifications: Add, modify, delete operand
• Number of DLM_REQUESTS:DLM requests
• DLM_CONFLICTS:DLM Number of conflicts
• DLM_RELEASES:DLM Release Number

using V$rowcache Data

1> Verify that the data dictionary cache has the appropriate size. If the shared pool is too small, the data dictionary cache is insufficient to have the appropriate size to cache the requested information.

2>: Verify that the app is effectively accessing the cache. If the application design fails to use the data dictionary cache effectively (for example, the Big Data dictionary cache does not help resolve performance issues). For example, the dc_users cache has a large number of gets in the past, and it looks like a large number of different users are created in the database, and the application logs the user to log in and log off frequently. The above data can be verified by checking the logon ratio and the number of system users. The resolution ratio can also be high, if this is the middle tier of a large OLTP system, it may be more efficient to manage individual accounts in the middle tier, allowing the middle tier to be a single-user login as the application owner. It is equally effective to reduce the logon/logoff ratio by keeping active connections.

3>. Confirm that dynamic space allocation is occurring. Dc_segments, Dc_used_extents, and dc_free_extents a large number of similar size changes will indicate the existence of a large amount of dynamic space allocation. Possible solutions include specifying the next area size or using the local management table space. If a spatial allocation is a temporary tablespace, you can specify a true temporary tablespace (if the space allocation is occurring on the temp tablespace and then use a true temporary TA Blespace for the temp. )。

A change in the 4>.dc_sequences value indicates whether a large number of sequence are being generated.

5>. Collect hard-to-parse evidence. Hard parsing often manifests itself in a large number of dc_columns, dc_views and dc_objects caches.

Example:

1. Grouping statistical data dictionary statistics items

Select Parameter,sum ("COUNT"), sum (usage), sum (gets), sum (getmisses),

SUM (Scans), SUM (scanmisses), sum (modifications),

SUM (dlm_requests), sum (dlm_conflicts), sum (dlm_releases)

From V$rowcache

GROUP by parameter;

2. Check the hit ratio of the data dictionary

Select 1-sum (getmisses)/sum (gets) "Data dictionary hitratio" from V$rowcache;

Learning Dynamic Performance Table (--v$rowcache)