 |
|
Oracle Tips by Burleson |
Plotting the Oracle Data Buffer Hit Ratio by
Hour of the Day
STATSPACK can easily compute the average DBHR
by the hour of the day. Let’s look closely at the script that performs
this function, and notice that it references the
stats$buffer_pool_statistics table. This table contains the values
used for computing the DBHR. These values are time-specific, only
indicative of conditions at the time of the STATSPACK snapshot.
However, we need a technique that will yield an elapsed-time measure
of the hit ratio.
To convert the values into elapsed-time data,
we can join the stats$buffer_pool_statistics table against
itself, and compare the original snapshot with each successive one.
Since the desired collection interval is hourly, the script presented
below will compute each hourly buffer hit ratio. We can further derive
the hourly DBHR for each day by selecting the snap_time column
with a mask of HH24.
See code depot for full scripts
--
****************************************************************
-- Display hourly BHR averages with STATSPACK
-- ****************************************************************
select
. . .
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where
. . .
old.consistent_gets > 0
having
avg(
. . .
group by
to_char(snap_time,'HH24')
;
CAUTION - A problem will arise with this
script if the instance is stopped and restarted because the v$ view
values will be reset. STATSPACK will take a value from the previous
instance when the database is restarted, invariably causing the
utility to return an arbitrarily large number. We can circumvent this
problem by adding the HAVING clause to the script, which omits any
values greater than 1.
The output from the DBHR hourly average script
is shown below. The report displays the average hit ratio for each
day. The report provides insight, but the signature of the database
becomes much more obvious if it is plotted in a 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
Oracle professionals use STATSPACK to extract
the signatures for all of the important metrics and then plot the
metrics to reveal the trend-based patterns. The signatures are
typically gathered by hour of the day and day of the week.
A plot of the data is shown in Figure 4.1.
Signatures become more evident over longer periods of time.
Nevertheless, the plot of this database already evidences some
interesting trends.
It is immediately clear from the chart that
the DBHR dropped below 90 percent at 3:00 a.m., 4:00 a.m. and 10:00
a.m. each day. In this case, end users of the database were submitting
huge batch reports between 3:00 and 5:00 a.m. The difficulty for the
DBA is that the 10:00 a.m. drop is a prime-time online period. To
solve this problem, the DBA might review the SQL statements collected
in the stats$sql_summary for the 9:00 to 10:00 a.m. periods to
see if any rows have large rows_processed values. If so, the
task could be rescheduled during off-peak processing hours.
The above
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
Mike Ault,
one of the world's top Oracle experts, has finally consented to release his
complete collection of more than 450 Oracle scripts, covering every possible
area of Oracle administration and management.
This is
the definitive collection of Oracle monitoring and tuning scripts, and it
would take thousands of hours to re-create this vast arsenal of scripts from
scratch.
Mike has
priced his collection of 465 scripts at $39.95, less than a dime per script.
You can download them immediately at this link:
http://www.rampant-books.com/download_adv_mon_tuning.htm
|
