Databases Reference
In-Depth Information
The baselines can be fixed, moving window, or templates. The baseline we have defined in the
previous example is fixed, because it corresponds to a specific time period in the past. The
moving windows baseline corresponds to the AWR data within the entire retention period, and
it's useful when used with adaptive thresholds. The baseline templates, instead, are created
for a future time period, and can be single or repeating.
In the first statement of step 2, we have set the interval between snapshots to 30 minutes;
in the second statement the retention period of the snapshots collected is set to 21600
minutes, which corresponds to 15 days.
The adaptive thresholds just mentioned consent to adapt the thresholds of a performance
metric according to the workload of the system, eliminating false alerts. From Oracle 11
g
,
adaptive thresholds are adjusted based on different workload patterns (for example, a
system used for OLTP in daytime and for batch jobs at night) automatically recognized
by the database.
We have created a report in the previous example by using the
awrrpt.sql
script. There
are other reports available, generated by a corresponding script in the same folder; for
example,
awrrpti.sql
is the same as
awrrpt.sql
, but for a specific database instance.
awrsqrpti.sql
generates a report for a particular SQL statement, like the script
sprepsql.
sql
for Statspack. The corresponding script
awrsqrpti.sql
prepares the same report for
a specific database instance.
There are also compare period reports, which allow us to compare not two snapshots but
two AWR reports. If we have a database which performs well in a certain period, and we
experiment a lack of performance in another period, we can elaborate two reports for the
first and the latter period, and then compare the reports among them, to point out the
differences and try to identify the issue.
For example, in step 4, we have created a baseline based on the snapshots with IDs from 1
to 11, and we name it "Friday off-peak".
The timespan of the two reports we are comparing isn't important, because AWR normalizes
the data according to the different timeframe.
Compare period reports can be launched from Oracle Enterprise Manager or using the script
awrddrpt.sql
(the script
awrddrpti.sql
to concentrate the result on a single instance).
There's more...
We can specify the adaptive thresholds as a percentage of the maximum value observed in
the moving window baseline, or as a statistical percentile, ranging from 0.95 to 0.9999—from
five observations expected to exceed the value in 100 to 1 observation in 10,000.
