| |
 |
|
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. |
|
