Oracle 10gR2 Adaptive Thresholds, Part 1: Overview

Synopsis. Oracle 10g Release 2 (10gR2) has
improved significantly the methodology for tracking performance metrics within
the database. This article – the first in this series – discusses how adaptive
thresholds are designed to improve the detection of threshold violations,
including the ability to discern a false positive from a true warning.

I first started taking advantage of Oracle Enterprise
Manager (OEM) events in Oracle 9iR2. I had a good reason, too: One of my
client’s applications was constantly malfunctioning and would regularly begin
to use up almost all of the database’s available Program Global Area (PGA)
memory. As a result, other user sessions would have virtually no additional
memory to perform sorting operations or, in a few extreme cases, block any
further connections to the database.

Our team activated event tracking for per-session PGA memory
usage by creating a custom user-defined event that Oracle 9i OEM
monitored once every five minutes. We created a special package that contained
various functions for monitoring which user sessions were using more than 30MB
of PGA memory for longer than two monitoring periods (i.e. 10 minutes).
Whenever OEM detected that a single application session was using excessive
amounts of memory, it automatically sent an e-mail to our DBA team so that we
could determine what the problem was.

This “distant early warning” system helped us immeasurably
because we could respond to excessive PGA utilization in a reasonably timely
fashion. However, after a few months of OEM monitoring, I noticed two
interesting patterns:

The upper thresholds for the warning and critical
conditions had to be constantly adjusted. As the load on our database
server gradually increased over time, our initial warning target of 30MB for
per-session PGA utilization tended to be violated more and more often, and we
would constantly receive warning messages. As a result, we had to readjust the
warning threshold limit gradually so that we’d only get warned about
significant events.

“False positives” tended to be ignored over time.
When we turned on notification for the thresholds, we had a natural tendency to
pay strict attention to every warning we received. After a while, however, I
noticed that human nature kicked in: we grew used to receiving warning
messages, and we began to treat them a bit too lackadaisically. You can guess
the result: Once in a while, we’d simply put off following up on a warning or
critical alert, and by the time we investigated, it was too late to find out
the cause of the alert.

Oracle 10g Metrics Collection and Interpretation: A New Performance Model

This story is a prime example of how Oracle releases prior
to Oracle 10g handled the detection of potential problems in the database.
Since Oracle 10g has greatly expanded the number and types of wait events
from about 290 in prior releases to over 800, it makes it that much simpler to
isolate the exact causes of poor database performance. In fact, the increased
granularity of these wait events is crucial to the success of the automatic
advisor features found in Automatic Database Diagnostic Monitor (ADDM) and its
related Advisor framework.

Oracle 10g also improved the concept of database
thresholds because a majority of the issues that cause Oracle DBAs the most
grief are being monitored automatically right “out of the box.” For example,
Oracle 10g automatically monitors tablespace space utilization and will issue a
warning or critical message when the tablespace’s size has reached a threshold
of 85% or 97%, respectively. Other conditions monitored include the ratio of
the time the database is spending in a wait state vs. actually performing work,
the average number of seconds it takes to complete a transaction (i.e. response
time), and how much redo each DML operation is generating.

Another nice feature of Oracle 10g Release 1 (10gR1) is the
ability to create a performance baseline from metrics gathered over the
past days, weeks and months. Once I’ve established and saved such a baseline, I
can easily instruct Oracle 10gR1 to build thresholds based upon the highest
value for a particular metric (for example, the number of user commits per
second). I can also tell Oracle 10gR1 to build thresholds based on an
additional percentage beyond a baseline value – say, 125% of the number
of user commits per second for the baseline time period.

Even with these improved detection methods, however, Oracle
10gR1 still has two slight drawbacks. First, since it relies upon strict arithmetic
methods for detecting a threshold violation, Oracle 10gR1 can only notify me
when a threshold has been breached. The current value of user commits per
second may be hovering very near but just below the warning threshold value for
several hours, and I would never know that a potential problem is brewing.
Also, when a warning threshold is constantly being breached, I really have only
two choices: ignore the warning as a likely “false positive,” or
continually raise the threshold until I no longer receive the warning.

This conundrum illustrates that I really need to know two
different things whenever a threshold is breached:

• If a current metric were compared to a threshold as of a
  corresponding point in time, would it even register as a problem? For example,
  a value of 300 user commits / second may be entirely normal during my
  database’s nightly batch processing time frame between 7 PM and 10 PM, but
  highly out of the ordinary between the hours 10 AM and 2 PM, when my online
  transaction processing users are performing order entry tasks. To make this
  even more complex, I may need to know this variance on an hour-by-hour
  comparison for some metrics, while for others I’d like to know the variance
  based on a day-by-day (e.g. Monday vs. Tuesday) or weekday vs.
  weekend comparison.

• Is the current level of this metric truly significant?
  Continuing the same scenario, do I really need (or desire!) to be notified
  whenever the number of user commits per second barely exceeds some
  relatively arbitrary threshold? And just as important, wouldn’t I want to know
  when a metric value is just below that same threshold for a significant
  period of time?

Oracle 10gR2 solves these issues with a new concept called adaptive
thresholds. In a nutshell, an adaptive threshold measures the difference
between the current value for a metric and its corresponding
threshold as of a particular, yet relative, point in time. Once
Oracle determines that a threshold breach has occurred, it also measures the significance
of the breach in relation to the threshold as a deflection from a nominal
value.

Establishing Metric Baselines

Adaptive thresholds rely upon metric baselines created from
representative samples of metrics taken over a time frame. Oracle will only
allow the creation of metric baselines if the data collected has enough
significance, but this can be resolved by altering the time frames or
granularity of the metrics collected. Metric baselines are further divided into
two types: moving window baseline periods and static baseline periods.

Moving Window Baseline Periods. As its name implies,
a moving window baseline period is based upon a rolling window of time that
extends a minimum of seven days into the past. Oracle also allows the creation
of moving window baseline periods that extend 7, 21, 35, and even 90 days into
the past. These baselines are most appropriate when the system or application
whose performance I am measuring have cyclical workloads that are relatively
predictable.

For example, a hybrid database typically has well-defined
activity cycles. Online transaction processing (OLTP) activity may occur during
the morning through the early evening, followed by batch processing during the
evening, and then followed by exports and backups in the early morning hours.
In this case, the database’s performance will follow a regular “heartbeat”
pattern that repeats every business day, while the weekday vs. weekend
performance pattern may be dramatically different.

Static Baseline Periods. Static baseline periods, on
the other hand, consist of a well-defined time period for which I want to
gather and preserve statistics for later comparison. I can create a static
baseline period based on any two points in time – as long as sufficient data
for the metrics exist. For example, I may want to capture the performance of my
database during the last seven to ten days of the previous calendar quarter as
a static baseline so that I can compare that performance during the upcoming
quarter-ending period, thus monitoring the database for any significant
deflections from the norm.

Time Groups. Regardless of the type of baseline
period selected, Oracle 10gR2 permits me to group data within different time
periods known as time groups. This insures that thresholds have
sufficient significant data for metric baseline creation. Time groups are
broken down into several different patterns as illustrated in Table 1.
Once metric baselines data have been gathered, they can be distributed within a
combination of these daily and/or weekly grouping schemes to insure that there
is enough significance for applying adaptive threshold monitoring.

Adaptive Thresholds: It’s All About the Deflection

Once I have set up the appropriate baseline periods and
defined the appropriate time group to insure sufficient significance, I can set
adaptive thresholds based on one of two measurement methodologies: percent
of maximum thresholds or significance level thresholds.

Significance Level Thresholds. These are percentile-based
and measure the deflection from the nominal value based upon one of four
possible levels of deviation from the norm. A significance level threshold
therefore helps to eliminate false positives while highlighting those truly
significant variances from an expected value. Table 2 lists the
available significance levels:

Percent of Maximum Thresholds. This type of threshold
setting uses a simple arithmetic calculation — the percentage of deflection
based on the value at the 99^th percentile for the selected metric
baseline – to determine if a threshold has been violated significantly.

Tracked Thresholds. Of the several dozen metrics that
Oracle 10g provides across the entire database, it is important to note that
Oracle 10gR2 only provides the ability to track adaptive thresholds for 15 of
the most meaningful, broken down across three areas of focus:

• Performance metrics track three indicators of the amount
  of effort the database is expending to provide the current level of
  performance.

• Workload Volume metrics focus on eight of the most
  meaningful indicators of how much work the database is performing.

• Workload Type metrics depend on four indicators that
  describe how the workload is distributed within the database’s overall
  performance.

Table 3 lists the 15 metrics that support adaptive
thresholds, the rates and ratios being tracked, and what each metric indicates:

Next Steps

In Part 2 of this series, I’ll provide actual examples of
how to create metric baselines using both static metrics and rolling time
periods and then demonstrate how to configure adaptive thresholds using these
metric baselines so that you can elevate your Oracle 10gR2 database’s warning
and critical thresholds to new heights of sensitivity and meaningfulness.

References and Additional Reading

Even though I have hopefully provided enough technical
information in this article to encourage you to explore with these features, I
also strongly suggest that you first review the corresponding detailed Oracle
documentation before proceeding with any experiments. Actual implementation of
these features should commence only after a crystal-clear understanding exists.
Please note that I have drawn upon the following Oracle 10gR2 documentation for
the deeper technical details of this article:

B14214-01 Oracle 10gR2
Database New Features Guide

B14231-01 Oracle 10gR2
Database Administrator’s Guide

B14258-01 Oracle 10gR2 PL/SQL
Packages and Types Reference

»


See All Articles by Columnist Jim Czuprynski