 |
|
Trend Reports of the Data
Buffer Hit Ratio with STATSPACK
Oracle Tips by Burleson
|
Now that we see how to assign tables and
indexes into the proper data buffer, we are ready to examine STATSPACK
reports that will show trends in the behavior of the data buffer
pools. We can generate average DBHR values along two dimensions:
Each of these reports can provide invaluable
information in spotting usage trends within the Oracle database.
Remember, activity within the data buffers happens very fast, and
sometime a long-tern analysis will provide valuable clues into the
processing characteristics of your database. On almost every Oracle
database, patterns emerge as the result of regular processing
schedules, and these patterns are commonly known as “signatures.” In
the following examples we will plot the average DBHR for an Oracle
database running Oracle MRP applications.
Plotting the Data Buffer Hit Ratio by Hour of the Day
Using STATSPACK, you can easily compute the
average data buffer hit ratio by the hour of the day. The following
script will average the DBHR and present the hourly averages. Let's
take a close look at the next script so we can understand how it
functions. Note that the script references the
stats$buffer_pool_statistics table, which is where we find the values
used to compute the DBHR. The problem is that each STATSPACK snapshot
gives the values at a specific point in time, while the DBHR is an
elapsed-time measure. To convert the snapshots to elapsed-time
measures, we join the stats$buffer_pool_statistics table against
itself, comparing each snapshot with the snapshot immediately
preceding it. Since my STATSPACK collection interval is hourly, the
following script computes each hourly DBHR. From the individual DBHR
readings, we average the value by selecting the snap_time column with
a mask of HH24. This results in an hourly average value for DBHR.
rpt_bhr8i_hr.sql
set pages
999;
column bhr format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(snap_time,'HH24') mydate,
avg(
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))
) bhr
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where
new.name in ('DEFAULT','FAKE VIEW')
and
new.name = old.name
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.consistent_gets > 0
and
old.consistent_gets > 0
having
avg(
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))
) < 1
group by
to_char(snap_time,'HH24')
;
NOTE: The only problem with this script is that the
v$ accumulators will be reset when the instance is stopped and
restarted. When starting a database and taking a STATSPACK value with
a prior value before the database was stopped, STATSPACK will
invariable return an arbitrary large number. To get around this
problem, we added the HAVING clause to the script to omit any values
that are greater than 1.
Here is the output from the script. Note that
we get the average DBHR for each day. While this report is somewhat
interesting, the signature of the database becomes more evident after
we plot the data in an Excel spreadsheet.
yr BHR
-- -----
00 .94
01 .96
02 .91
03 .82
04 .80
05 .90
06 .94
07 .93
08 .96
09 .95
10 .84
12 .91
13 .96
14 .95
17 .97
18 .97
19 .95
20 .95
21 .99
22 .93
23 .94
Figure 9-11
shows a plot of the data. Over time, the signature will become more
evident, but this database displays some interesting trends.
Figure 9-65: Average data buffer hit ration by
hour of day
From this chart we can clearly see that the
DBHR dropped below the recommended value of 90 percent at 3:00
a.m., 4:00
a.m., and 10:00
a.m. each day. In this
database, it turns out that the end users were submitting huge batch
reports between 3:00 a.m.
until 5:00 a.m. The
problem here is that the DBHR is dropping low at 10:00
a.m., a prime-time
online period. The next step would be to review the SQL statements
collected in stats$sql_summary for the 9:00
a.m. and 10:10
a.m. periods and see if
we can find any rows with a large value for rows_processed.
This task should then be rescheduled to execute during off-peak
processing periods.
Plotting the Data Buffer Hit Ratio by Day of the Week
We can perform a similar analysis for the
average DBHR by day of the week. By simply changing the snap_time
format mask from “HH24” to “day,” the averages for each day of the
week can be displayed. Here is the script that collected the averages:
rpt_bhr8i_dy.sql
set pages
999;
column bhr format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(snap_time,'day') mydate,
avg(
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))
) bhr
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where
new.name in ('DEFAULT','FAKE VIEW')
and
new.name = old.name
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.consistent_gets > 0
and
old.consistent_gets > 0
having
avg(
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))
) < 1
group by
to_char(snap_time,'day')
;
Here is the output from the script. Note that
the days are presented in alphabetical order, so you must manually
resequence the output after pasting it into the spreadsheet for
graphing.
yr. mo d
BHR
--------- -----
friday .89
monday .98
saturday .92
sunday .91
thursday .96
tuesday .93
wednesday .91
Once the rows are
resequenced into day order, it is easy to plot the graph, as shown in
Figure 9-12.
Figure 9-66: The average data buffer hit ration
by day of the week
This report is especially useful for developing
a daily signature. In the case of this database, we see that the DBHR
drops on Wednesdays and Fridays. To fully understand this, we would
need to use STATSPACK to review the differences between these days and
the other days of the week.
Now that we are experts in the data buffer hit
ratio and how to plot and interpret the values, we are ready to move
on to other areas of instance tuning. The next section will explore
the Oracle database writer and show how STATSPACK can be used to
ensure that it is working at optimal levels.
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. |
|
