The DBMS_SERVER_ALERT
package enables you to configure the Oracle Database server to issue an alert when a threshold for a specified server metric has been violated. You can configure both warning and critical thresholds for a large number of predefined metrics.
If a warning threshold is reached, the server generates a severity level 5 alert. If a critical threshold is reached, the server generates a severity level 1 alert.
The chapter contains the following topics:
This section contains topics which relate to using the DBMS_SERVER_ALERT
package. The following topics define constants used in package procedures.
You qualify the metric by an individual object for the following object types.
Table 94-1 Object Types Defined as Constants
Constant | Description |
---|---|
|
Metrics collected on the system level for each instance. |
|
Metrics collected on the file level. These are used for |
|
Metrics collected on the service level. Currently |
|
Metrics collected on the tablespace level. |
|
Metrics collected on wait event class level. Currently supported metrics are |
|
Metrics collected on the session level. Currently only |
You can specify a relational comparison operator to determine whether or not a given metric's value violates the threshold setting. The server supports the following operators.
Table 94-2 Relational Operators Defined as Constants
Constant | Description |
---|---|
|
A metric value matching an entry in a list of threshold values is considered a violation. |
|
The metric value is not compared to the threshold value, and no alerts are generated. Use this operator to disable alerts for a metric. |
|
A metric value equal to the threshold value is considered a violation. |
|
A metric value greater than or equal to the threshold value is considered a violation. |
|
A metric value greater than the threshold value is considered a violation. |
|
A metric value less than or equal to the threshold value is considered a violation. |
|
A metric value less than the threshold value is considered a violation. |
|
A metric value not equal to the threshold value is considered a violation. |
The following metrics are supported. All internal metric names are supplied as package contants.
Table 94-3 List of Supported Metrics
Metric Name (Internal) | Metric Name (External) | Units |
---|---|---|
|
Service Response (for each execution) |
Seconds |
|
Buffer Cache Hit (%) |
% of cache accesses |
|
Library Cache Hit (%) |
% of cache accesses |
|
Library Cache Miss (%) |
% of cache accesses |
|
Sorts in Memory (%) |
% of sorts |
|
Redo Log Allocation Hit |
% of redo allocations |
|
Number of Transactions (for each second) |
Transactions for each Second |
|
Physical Reads (for each second) |
Reads for each Second |
|
Physical Reads (for each transaction) |
Reads for each Transaction |
|
Physical Writes (for each second) |
Writes for each Second |
|
Physical Writes (for each transaction) |
Writes for each Transaction |
|
Direct Physical Reads (for each second) |
Reads for each Second |
|
Direct Physical Reads (for each transaction) |
Reads for each Transaction |
|
Direct Physical Writes (for each second) |
Writes for each Second |
|
Direct Physical Writes (for each transaction) |
Writes for each Transaction |
|
Direct LOB Physical Reads (for each second) |
Reads for each Second |
|
Direct LOB Physical Reads (for each transaction) |
Reads for each Transaction |
|
Direct LOB Physical Writes (for each second) |
Writes for each Second |
|
Direct LOB Physical Writes (for each transaction) |
Writes for each Transaction |
|
Redo Generated (for each second) |
Redo Bytes for each Second |
|
Redo Generated (for each transaction) |
Redo Bytes for each Transaction |
|
Database Wait Time (%) |
% of all database time |
|
Database CPU Time (%) |
% of all database time |
|
Cumulative Logons (for each second) |
Logons for each Second |
|
Cumulative Logons (for each transaction) |
Logons for each Transaction |
|
Current Number of Logons |
Number of Logons |
|
Cumulative Open Cursors (for each second) |
Cursors for each Second |
|
Cumulative Open Cursors (for each transaction) |
Cursors for each Transaction |
|
Current Number of Cursors |
Number of Cursors |
|
User Commits (for each second) |
Commits for each Second |
|
User Commits (for each transaction) |
Commits for each Transaction |
|
User Rollbacks (for each second) |
Rollbacks for each Second |
|
User Rollbacks (for each transaction) |
Rollbacks for each Transaction |
|
User Calls (for each second) |
Calls for each Second |
|
User Calls (for each transaction) |
Calls for each Transaction |
|
Recursive Calls (for each second) |
Calls for each Second |
|
Recursive Calls (for each transaction) |
Calls for each Transaction |
|
Session Logical Reads (for each second) |
Reads for each Second |
|
Session Logical Reads (for each transaction) |
Reads for each Transaction |
|
DBWR Checkpoints (for each second) |
Checkpoints for each Second |
|
Background Checkpoints (for each second) |
Checkpoints for each Second |
|
Redo Writes (for each second) |
Writes for each Second |
|
Redo Writes (for each transaction) |
Writes for each Transaction |
|
Scans on Long Tables (for each second) |
Scans for each Second |
|
Scans on Long Tables (for each transaction) |
Scans for each Transaction |
|
Total Table Scans (for each second) |
Scans for each Second |
|
Total Table Scans (for each transaction) |
Scans for each Transaction |
|
Fast Full Index Scans (for each second) |
Scans for each Second |
|
Fast Full Index Scans (for each transaction) |
Scans for each Transaction |
|
Total Index Scans (for each second) |
Scans for each Second |
|
Total Index Scans (for each transaction) |
Scans for each Transaction |
|
Total Parses (for each second) |
Parses for each Second |
|
Total Parses(for each transaction) |
Parses for each Transaction |
|
Hard Parses(for each second) |
Parses for each Second |
|
Hard Parses(for each transaction) |
Parses for each Transaction |
|
Parse Failures (for each second) |
Parses for each Second |
|
Parse Failures (for each transaction) |
Parses for each Transaction |
|
Sorts to Disk (for each second) |
Sorts for each Second |
|
Sorts to Disk (for each transaction) |
Sorts for each Transaction |
|
Rows Processed for each Sort |
Rows for each Sort |
|
Executes Performed Without Parsing |
% of all executes |
|
Soft Parse (%) |
% of all parses |
|
Cursor Cache Hit (%) |
% of soft parses |
|
User Calls (%) |
% of all calls |
|
Transactions Committed (%) |
% of all transactions |
|
Network Bytes, for each second |
Bytes for each Second |
|
Response (for each transaction) |
Seconds for each Transaction |
|
Data Dictionary Hit (%) |
% of dictionary accesses |
|
Data Dictionary Miss (%) |
% of dictionary accesses |
|
Shared Pool Free(%) |
% of shared pool |
|
Average File Read Time |
Microseconds |
|
Average File Write Time |
Microseconds |
|
Disk I/O |
Milliseconds |
|
Process Limit Usage (%) |
% of maximum value |
|
Session Limit Usage (%) |
% of maximum value |
|
User Limit Usage (%) |
% of maximum value |
|
Average Number of Users Waiting on a Class of Wait Events |
Count of sessions |
|
Percent of Database Time Spent Waiting on a Class of Wait Events |
% of Database Time |
|
Application Design Wait (by session count) |
Count of sessions |
|
Application Design Wait (by time) |
Microseconds |
|
Physical Design Wait (by session count) |
Count of sessions |
|
Physical Design Wait (by time) |
Microseconds |
|
Internal Contention Wait (by session count) |
Count of sessions |
|
Internal Contention Wait (by time) |
Microseconds |
|
Process Service Wait (by session count) |
Count of sessions |
|
Process Service Wait (by time) |
Microseconds |
|
Network Message Wait (by session count) |
Count of sessions |
|
Network Message Wait (by time) |
Microseconds |
|
Disk I/O Wait (by session count) |
Count of sessions |
|
Operating System Service Wait (by session count) |
Count of sessions |
|
Operating System Service Wait (by time) |
Microseconds |
|
Resource Mgr I/O Limit Wait (by session count) |
Count of sessions |
|
Resource Mgr I/O Limit Wait (by time) |
Microseconds |
|
Resource Mgr CPU Limit Wait (by session count) |
Count of sessions |
|
Resource Mgr CPU Limit Wait (by time) |
Microseconds |
|
Resource Mgr User Limit Wait (by session count) |
Count of sessions |
|
Resource Mgr User Limit Wait (by time) |
Microseconds |
|
Operating System Scheduler CPU Wait (by session count) |
Count of sessions |
|
Operating System Scheduler CPU Wait (by time) |
Microseconds |
|
Cluster Messaging Wait (by session count) |
Count of sessions |
|
Cluster Messaging Wait (by time) |
Microseconds |
|
Other Waits (by session count) |
Count of sessions |
|
Other Waits (by time) |
Microseconds |
|
Enqueue Timeouts (for each second) |
Timeouts for each Second |
|
Enqueue Timeouts (for each transaction) |
Timeouts for each Transaction |
|
Enqueue Waits (for each second) |
Waits for each Second |
|
Enqueue Waits (for each transaction) |
Waits for each Transaction |
|
Enqueue Deadlocks (for each second) |
Deadlocks for each Second |
|
Enqueue Deadlocks (for each transaction) |
Deadlocks for each Transaction |
|
Enqueue Requests (for each second) |
Requests for each Second |
|
Enqueue Requests (for each transaction) |
Requests for each Transaction |
|
DB Block Gets (for each second) |
Gets for each Second |
|
DB Block Gets (for each transaction) |
Gets for each Transaction |
|
Consistent Gets (for each second) |
Gets for each Second |
|
Consistent Gets (for each transaction) |
Gets for each Transaction |
|
DB Block Changes (for each second) |
Changes for each Second |
|
DB Block Changes (for each transaction) |
Changes for each Transaction |
|
Consistent Changes (for each second) |
Changes for each Second |
|
Consistent Changes (for each transaction) |
Changes for each Transaction |
|
Database CPU (for each second) |
Microseconds for each Second |
|
Database CPU (for each transaction) |
Microseconds for each Transaction |
|
CR Blocks Created (for each second) |
Blocks for each Second |
|
CR Blocks Created (for each transaction) |
Blocks for each Transaction |
|
CR Undo Records Applied (for each second) |
Records for each Second |
|
CR Undo Records Applied (for each transaction) |
Records for each Transaction |
|
Rollback Undo Records Applied (for each second) |
Records for each Second |
|
Rollback Undo Records Applied(for each transaction) |
Records for each Transaction |
|
Leaf Node Splits (for each second) |
Splits for each Second |
|
Leaf Node Splits (for each transaction) |
Splits for each Transaction |
|
Branch Node Splits (for each second) |
Splits for each Second |
|
Branch Node Splits (for each transaction) |
Splits for each Transaction |
|
Global Cache Blocks Corrupt |
Blocks |
|
Global Cache Blocks Lost |
Blocks |
|
Global Cache CR Request |
Milliseconds |
|
Global Cache Current Request |
Milliseconds |
|
Downgraded Parallel Operations (for each second) |
Operations for each Second |
|
Downgraded to 25% and more (for each second) |
Operations for each Second |
|
Downgraded to 50% and more (for each second) |
Operations for each Second |
|
Downgraded to 75% and more (for each second) |
Operations for each Second |
|
Downgraded to serial (for each second) |
Operations for each Second |
|
Number of Users blocked by some Session |
Number of Users |
|
PGA Cache Hit (%) |
% bytes processed in PGA |
|
Elapsed time for each user call for each service |
Microseconds for each call |
|
CPU time for each user call for each service |
Microseconds for each call |
|
Tablespace space usage |
% full |
|
Tablespace bytes space usage |
Kilobytes free |
Table 94-4 DBMS_SERVER_ALERT Package Subprograms
Subprogram | Description |
---|---|
Expands alert messages |
|
Gets the current threshold settings for a specified metric |
|
Sets the warning and critical thresholds for a specified metric |
This function expands alert messages.
DBMS_SERVER_ALERT.EXPAND_MESSAGE( user_language IN VARCHAR2, message_id IN NUMBER, argument_1 IN VARCHAR2, argument_2 IN VARCHAR2, argument_3 IN VARCHAR2, argument_4 IN VARCHAR2, argument_5 IN VARCHAR2) RETURN VARCHAR2;
Table 94-5 EXPAND_MESSAGE Procedure Parameters
Parameter | Description |
---|---|
|
The language of the current session. |
|
Id of the alert message |
|
The first argument in the alert message. |
|
The second argument in the alert message. |
|
The third argument in the alert message. |
|
The fourth argument in the alert message. |
|
The fifth argument in the alert message. |
This procedure gets the current threshold settings for the specified metric.
DBMS_SERVER_ALERT.GET_THRESHOLD( metrics_id IN BINARY_INTEGER, warning_operator OUT BINARY_INTEGER, warning_value OUT VARCHAR2, critical_operator OUT BINARY_INTEGER, critical_value OUT VARCHAR2, observation_period OUT BINARY_INTEGER, consecutive_occurrences OUT BINARY_INTEGER, instance_name IN VARCHAR2, object_type IN BINARY_INTEGER, object_name IN VARCHAR2);
Table 94-6 GET_THRESHOLD Procedure Parameters
Parameter | Description |
---|---|
|
The internal name of the metric. See "Supported Metrics". |
|
The operator for the comparing the actual value with the warning threshold. |
|
The warning threshold value. |
|
The operator for the comparing the actual value with the critical threshold. |
|
The critical threshold value. |
|
The period at which the metric values are computed and verified against the threshold setting. |
|
The number of observation periods the metric value should violate the threshold value before the alert is issued. |
|
The name of the instance for which the threshold is set. This is |
|
Either |
|
The name of the object. |
This procedure sets the warning and critical thresholds for a specified metric.
DBMS_SERVER_ALERT.SET_THRESHOLD( metrics_id IN BINARY_INTEGER, warning_operator IN BINARY_INTEGER, warning_value IN VARCHAR2, critical_operator IN BINARY_INTEGER, critical_value IN VARCHAR2, observation_period IN BINARY_INTEGER, consecutive_occurrences IN BINARY_INTEGER, instance_name IN VARCHAR2, object_type IN BINARY_INTEGER, object_name IN VARCHAR2);
Table 94-7 SET_THRESHOLD Procedure Parameters
Parameter | Description |
---|---|
|
The internal name of the metric. See "Supported Metrics". |
|
The operator for the comparing the actual value with the warning threshold (such as |
|
The warning threshold value. This is |
|
The operator for the comparing the actual value with the critical threshold. See "Relational Operators". |
|
The critical threshold value. This is |
|
The period at which the metric values are computed and verified against the threshold setting. The valid range is 1 to 60 minutes. |
|
The number of observation periods the metric value should violate the threshold value before the alert is issued. |
|
The name of the instance for which the threshold is set. This is |
|
See "Object Types". |
|
The name of the object. This is |