Oracle Tips by Burleson Consulting
is an excerpt from "Oracle 10g New Features for Administrators" by
Introduction to Metrics
MMON collects database metrics continuously and
automatically saves them in the SGA for one hour.
The OEM Database Control’s All Metrics page offers
an excellent way to view the various metrics.
Oracle Database 10g Metric Groups are (can be
obtained from V$METRICGROUP):
o Event Class Metrics
o Event Metrics
o File Metrics
o Service Metrics
o Session Metrics V$SYSMETRIC,
o System Metrics
o Tablespace Metrics
Viewing Saved Metrics
MMON will automatically flush the metric data from
the SGA to the DBA_HISTORY_* views on disk. Examples of the history
views are DBA_HIST_SUMMARY_HISTORY,
DBA_HIST SYSMETRIC_HISTORY, and DBA_HIST_METRICNAME.
Each of these views contains snapshots of the corresponding V$ view.
There are three situations when a database can send
• A monitored metric crosses a critical threshold
• A monitored metric crosses a warning threshold
• A service or target suddenly becomes unavailable
Default Server-Generated Alerts
Your database comes with a set of the following
default alerts already configured. In addition, you can choose to
have other alerts.
• Any snapshot too old errors
• Tablespace space usage (warning alert at 85
percent usage; critical alert at 97 percent usage)
• Resumable session suspended
• Recovery session running out of free space
Server-Generated Alert Mechanism
MMON process checks all the configured metrics and
if any metric crosses a preset threshold, an alert will be
Using the Database Control to Manage Server Alerts
You can use Database Control to:
• set a warning and critical threshold
• A response action: a SQL script or a OS command
line to execute
• set Notification Rules: when notify a DBA
Using the DBMS_SERVER_ALERT Package to Manage Alerts
SET_THRESHOLD - This procedure will set warning and
critical thresholds for given metrics.
DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2,
In this example, a warning alert is issued when CPU
time exceeds 8000 microseconds for each user call and a critical
alert is issued when CPU time exceeds 10,000 microseconds for each
user call. The arguments include:
alert is generated.
o CPU_TIME_PER_CALL specifies the metric
identifier. For a list of support metrics, see PL/SQL Packages
and Types Reference.
o The observation period is set to 1 minute. This
period specifies the number of minutes that the condition must
deviate from the threshold value before the alert is issued.
o The number of consecutive occurrences is set to
2. This number specifies how many times the metric value must
violate the threshold values before
o The name of the instance is set to inst1.
o The constant DBMS_ALERT.OBJECT_TYPE_SERVICE
specifies the object type on which the threshold is set. In
this example, the service name is dev.oracle.com.
Note: If you don’t want Oracle to send any
metric based alerts, simply set the warning value and the critical
value to NULL.
GET_THRESHOLD - Use this procedure to retrieve
metrics_id IN NUMBER,
warning_operator OUT NUMBER,
warning_value OUT VARCHAR2,
critical_operator OUT NUMBER,
critical_value OUT VARCHAR2,
observation_period OUT NUMBER,
consecutive_occurrences OUT NUMBER,
instance_name IN VARCHAR2,
object_type IN NUMBER,
object_name IN VARCHAR2)
See the section "Proactive
Tablespace Management" for more
examples of using DBMS_SERVER_ALERT package.
Using the Alert Queue
You can use the DBMS_AQ and DBMS_AQADM packages for
directly accessing and reading alert messages in the alert queue.
Steps you should follow are:
1. Create an agent and subscribe the agent to the
ALERT_QUE using the CREATE_AQ_AGENT and ADD_SUBSCRIBER
procedures of the DBMS_AQADM package.
2. Associate a database user with the subscribing
agent and assign the enqueue privilege to the user using the
ENABLE_DB_ACCESS and GRANT_QUEUE_PRIVILEGE procedures of the
3. Optionally, you can register with the
DBMS_AQ.REGISTER procedure to receive an asynchronous
notification when an alert is enqueued to ALERT_QUE.
4. To read an alert message, you can use the
DBMS_AQ.DEQUEUE procedure or OCIAQDeq call. After the message
has been dequeued, use the
procedure to expand the text of the message.
Data Dictionary Views of Metrics and Alerts
DBA_THRESHOLDS - lists the threshold settings
defined for the instance.
DBA_OUTSTANDING_ALERTS - describes the outstanding
alerts in the database.
DBA_ALERT_HISTORY - lists a history of alerts that
have been cleared.
V$ALERT_TYPES - provides information such as group
and type for each alert.
V$METRICNAME - contains the names, identifiers, and
other information about the system metrics.
V$METRIC and V$METRIC_HISTORY - views contain
system-level metric values in memory.
STATE - Holds two possible values: stateful or
stateless. The database considers all the non-threshold alerts as
stateless alerts. A stateful alert first appears in
theDBA_OUTSTANDING_ALERTS view and goes to the DBA_ALERT_HISTORY
view when it is cleared. A stateless alert goes straight to
SCOPE - Classifies alerts into database wide and
instance wide. The only database-level alert is the one based on the
Tablespace Space Usage metric. All the other alerts are at
the instance level.
GROUP_NAME - Oracle aggregates the various database
alerts into some common groups: Space, Performance,
Configuration-related database alerts.
If you like Oracle tuning, see the
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.