Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Tuning the Dictionary Cache


Oracle Tips by Burleson
 

The data dictionary cache is used to hold rows from the internal Oracle metadata tables, including SQL stored in packages. Let's take a look at how packages interact with the dictionary cache.

When a PL/SQL 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 data dictionary data is maintained in a separate RAM buffer called the dictionary cache, which is stored in the shared SQL area. The data dictionary cache is accessed for each SQL statement at parse time and again at runtime when the SQL gathers dynamic storage for execution.

The data dictionary cache statistics originate in the x$kqrst structure, where it participates in the v$rowcache view. This data is transferred from v$rowcache into stats$rowcache_summary when a snapshot is requested.

The stats$rowcache_summary table is used to measure dictionary cache activity. Two columns are of interest: gets, and getmisses. The gets column provides the total number of requests for objects of that type. The getmisses column 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 following STATSPACK report displays the summaries for the data dictionary cache for all times when the data dictionary hit ratio has dropped below 95 percent:

rpt_dict_alert.sql

set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.'              format a16
column c1     heading "Data|Dictionary|Gets"         format 999,999,999
column c2     heading "Data|Dictionary|Cache|Misses" format 999,999,999
column c3     heading "Data|Dictionary|Hit|Ratio"    format 999,999

select
   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   sum(new.gets-old.gets)                c1,
   sum(new.getmisses-old.getmisses)      c2,
   trunc((1-(sum(new.getmisses-old.getmisses)/sum(new.gets-old.gets)))*100) c3
from
   stats$rowcache_summary new,
   stats$rowcache_summary old,
   stats$snapshot sn
where
   new.snap_id = sn.snap_id
and
   old.snap_id = new.snap_id-1
having
   trunc((1-(sum(new.getmisses-old.getmisses)/sum(new.gets-old.gets)))*100) < 95
group by
   to_char(snap_time,'yyyy-mm-dd HH24')
;

Here is the listing from this script. We can quickly identify times when the data dictionary experienced a poor hit ratio.

SQL> @rpt_dict_alert.sql

                                      Data       Data
                         Data   Dictionary Dictionary
                   Dictionary        Cache        Hit
Yr.  Mo Dy  Hr.          Gets       Misses      Ratio
---------------- ------------ ------------ ----------
2001-12-22 02         268,149       34,377         87
2001-12-22 08         976,143       52,311         94
2001-12-23 02         219,912       33,789         84
2001-12-24 06         571,179       54,327         90
2001-12-26 02         277,263       33,915         87
2001-12-27 02         275,961       33,957         87
2001-12-29 02         271,761       34,818         87
2001-12-30 02         407,001       34,587         91
2001-12-31 06         818,496       55,104         93
2001-12-31 11         621,138       38,136         93
2001-01-02 02         290,472       35,700         87
2001-01-02 08         661,248       36,918         94
2001-01-02 13         366,282       43,953         88
2001-01-03 02         304,101       36,057         88
2001-01-05 02         302,253       36,519         87

We can easily extend this report to see if there is a pattern in the data dictionary gets and misses. The script here computes the average by the hour of the day:

set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.'              format a16
column c1     heading "Data|Dictionary|Gets"         format 999,999,999
column c2     heading "Data|Dictionary|Cache|Misses" format 999,999,999
column c3     heading "Data|Dictionary|Hit|Ratio"    format 999,999

select
   to_char(snap_time,'HH24')  mydate,
--   sum(new.gets-old.gets)                c1,
--   sum(new.getmisses-old.getmisses)      c2,
   trunc((1-(sum(new.getmisses-old.getmisses)/sum(new.gets-old.gets)))*100) c3
from
   stats$rowcache_summary new,
   stats$rowcache_summary old,
   stats$snapshot sn
where
   new.snap_id = sn.snap_id
and
   old.snap_id = new.snap_id-1
group by
   to_char(snap_time,'HH24')
;

Here is the output from the script. Let's plot the numbers and see if there is a pattern.

                      Data
                 Dictionary
                        Hit
Yr.  Mo Dy  Hr.       Ratio
---------------- ----------
00                       99
01                       99
02                       94
03                      100
04                       96
05                       98
06                       98
07                       98
08                       98
09                       98
10                       99
11                       99
12                       99
13                       99
14                       99
15                       99
16                       99
17                       99
18                       99
19                       99
20                       99
21                       98
22                       99
23                       99

Figure 9-15 shows the graph.

Figure 9-69: Average data dictionary hit ratio by hour of the day

Here we see that the overall data dictionary hit ratio is acceptable, but there is a consistent drop in the data dictionary hit ratio at 2:00 a.m. each morning. Now that we have examined the system-wide dictionary averages, we are ready to drill down and look at the individual types of data dictionary objects.

The STATSPACK script here displays the details for each parameter type within the data dictionary cache:

rpt_dict_detail.sql

                                                                  Data
                                    Data  Dictionary       Data Object
                              Dictionary       Cache Dictionary    Hit
Yr.  Mo Dy  Hr.  PARAMETER          Gets      Misses      Usage  Ratio
---------------- ----------- ----------- ----------- ---------- ------
2001-12-20 11    dc_objects        1,342          38         22     97
                 dc_segments       4,085          10          2    100
                 dc_user_grants      414           1         49    100
                 dc_object_ids     2,268          39         11     98
                 dc_objects        1,335          66         63     95
                 dc_segments       2,275          49         61     98


2001-12-20 12    dc_constraints       72          24          8     67
                 dc_histogram_defs   229          66         60     71
                 dc_object_ids     6,315          94         85     99
                 dc_objects        4,048         156        151     96
                 dc_segments       6,002         114        105     98
                 dc_sequence_grants   97           6        101     94
                 dc_synonyms       1,474          49         22     97


2001-12-20 13    dc_object_ids    12,465          86         84     99
                 dc_objects        7,234         130        130     98
                 dc_segments      13,070         112        113     99
                 dc_synonyms       2,718          38         40     99
                 dc_users          3,043           1          1    100

While this standard report is useful for showing the activity of the objects, what the DBA really needs is a script to send an alert whenever a frequently used dictionary object type is experiencing a poor hit ratio. The STATSPACK script here will identify all data dictionary object types where the data dictionary hit ratio is less than 80 percent for an object type that had more than 300 gets:

rpt_dict_detail_alert.sql

set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.'              format a16
column parameter                                     format a20
column c1     heading "Data|Dictionary|Gets"         format 99,999,999
column c2     heading "Data|Dictionary|Cache|Misses" format 99,999,999
column c3     heading "Data|Dictionary|Usage"        format 999
column c4     heading "Object|Hit|Ratio"             format 999

select
   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   new.parameter                         parameter,
   (new.gets-old.gets)                   c1,
   (new.getmisses-old.getmisses)         c2,
   (new.total_usage-old.total_usage)     c3,
  round((1 - (new.getmisses-old.getmisses) /
  (new.gets-old.gets))*100,1)            c4
from
   stats$rowcache_summary new,
   stats$rowcache_summary old,
   stats$snapshot         sn
where
  round((1 - (new.getmisses-old.getmisses) /
  (new.gets-old.gets))*100,1) < 70
and
   (new.total_usage-old.total_usage) > 300
and
   new.snap_id = sn.snap_id
and
   old.snap_id = new.snap_id-1
and
   old.parameter = new.parameter
and
   new.gets-old.gets > 0
;

Here is the output. We can clearly see the time when our Oracle database experienced a high reload rate and the type of object. In the next report, we see a low data dictionary cache miss ratio with the dc_free_extents object. This may be due to a large load job that was requesting frequent new file extents.

                                               Data
                                    Data  Dictionary       Data Object
                              Dictionary       Cache Dictionary    Hit
Yr.  Mo Dy  Hr.  PARAMETER          Gets      Misses      Usage  Ratio
---------------- ----------- ----------- ----------- ---------- ------
2001-01-02 20    dc_histogram_defs 4,398       3,595      8,362     18
2001-01-11 22    dc_free_extents     934         316        319     66
2001-01-13 02    dc_free_extents     621         310        305     50
2001-01-19 02    dc_free_extents     626         312        310     50
2001-01-19 07    dc_histogram_defs 1,196         371        367     69
2001-01-19 12    dc_used_extents     453         351        321     23
2001-01-20 02    dc_free_extents     317         314        316      1
2001-01-23 02    dc_free_extents     627         313        322     50
2001-01-24 02    dc_free_extents     322         318        314      1

Now that we have completed our survey of Oracle's data dictionary cache, let's expand our scope and take a look at how to identify and tune Oracle sorting with the SGA.


This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter