Analyzing Oracle SGA Shared Pool (Data Dictionary Cache)

Posted: February 24th, 2008 | Author: TnT Admin | Filed under: Analyze | Tags: | No Comments »

Like the Library Cache, the measure of the effectiveness of Data Dictionary Cache is expressed in terms of hit-ratio percentage. This Data Dictionary Cache hit ratio shows how frequently the application finds the data dictionary information it needs in memory, instead of having to read it from the disk.

The counters that we should be watching are as followed:

  • V$ROWCACHE: Data Dictionary Hit Ratio
  • STATSPACK > Dictionary Cache Stats  DB/Inst: Pct Miss: 1- dc_xxxxxxxxxx

What we want to see of the counters are:

  • Break the Data Dictionary Cache to its individual component, dc_xxxxxxxx (e.g. dc_free_extents, dc_object_ids).  Examine cache activity by monitoring the GETS and GETMISSES columns.  For frequently accessed dictionary caches, the ratio of total GETMISSES to total GETS should be less than 10% or 15%, depending on the applicatio.  Else it warrants tuning on the Oracle recommends that consideration should be given to tuning the Shared Pool. (Source: Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2))

The following queries will be used to determine the hit-ratio:

  • SELECT parameter, 1 – (SUM(getmisses)/SUM(gets)) “Data Dictionary Hit Ratio” FROM V$ROWCACHE;
  • SELECT parameter, (SUM(getmisses)/SUM(gets)) “Data Dictionary Hit Ratio” FROM V$ROWCACHE;

To know more about the counters, read on.

[1] V$ROWCACHE

Two of the columns contained in the V$ROWCACHE view, GETS and GETMISSES, can be used to calculate the Data Dictionary Cache hit ratio. The statistics in this view are not generally examined individually, but as a combined equation query shown below.  In the below query, we are calculating the hit-ratio of successful GETS from the Data Dictionary Cache by subtracting the value of the total GETMISSES over total of GETS by 1.  Not subtracting by 1 will result in the GETMISSES ratio.  The first SQL gives the ratio for GETS and the latter gives the ratio for GETMISSES.

SELECT 1 – (SUM(getmisses)/SUM(gets)) “Data Dictionary Hit Ratio” FROM V$ROWCACHE;

SELECT parameter, 1 – (SUM(getmisses)/SUM(gets)) “Data Dictionary Hit Ratio” FROM V$ROWCACHE;

[2] STATSPACK

Under the Dictionary Cache Stats of the DB Instance section of the STATSPACK output, look at Pct Miss which represent the miss ratio.  For each components of the Data Dictionary Cache, minus the value shown in STATSPACK (Pct Miss) will result in the hit-ratio for the component of the Data Dictionary Cache. Threshold fo the Pct Miss will still be the same for V$ROWCACHE.

(Source: OCP Oracle 9i Performance Tuning Study Guide)

Related Posts

Leave a Reply