Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

94 DBMS_SERVER_ALERT

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:


Using DBMS_SERVER_ALERT

This section contains topics which relate to using the DBMS_SERVER_ALERT package. The following topics define constants used in package procedures.


Object Types

You qualify the metric by an individual object for the following object types.

Table 94-1 Object Types Defined as Constants

Constant Description
OBJECT_TYPE_SYSTEM Metrics collected on the system level for each instance.
OBJECT_TYPE_FILE Metrics collected on the file level. These are used for AVERAGE_FILE_READ_TIME and AVERAGE_FILE_WRITE_TIME metrics.
OBJECT_TYPE_SERVICE Metrics collected on the service level. Currently ELAPSED_TIME_PER_CALL and CPU_TIME_PER_CALL are collected.
OBJECT_TYPE_TABLESPACE Metrics collected on the tablespace level.
OBJECT_TYPE_EVENT_CLASS Metrics collected on wait event class level. Currently supported metrics are AVG_USERS_WAITING and DB_TIME_WAITING.
OBJECT_TYPE_SESSION Metrics collected on the session level. Currently only BLOCKED_USERS is collected. The threshold can only be set at the instance level, which means that no object name should be specified when setting the threshold for this type of metric.


Relational Operators

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
OPERATOR_CONTAINS A metric value matching an entry in a list of threshold values is considered a violation.
OPERATOR_DO_NOT_CHECK The metric value is not compared to the threshold value, and no alerts are generated. Use this operator to disable alerts for a metric.
OPERATOR_EQ A metric value equal to the threshold value is considered a violation.
OPERATOR_GE A metric value greater than or equal to the threshold value is considered a violation.
OPERATOR_GT A metric value greater than the threshold value is considered a violation.
OPERATOR_LE A metric value less than or equal to the threshold value is considered a violation.
OPERATOR_LT A metric value less than the threshold value is considered a violation.
OPERATOR_NE A metric value not equal to the threshold value is considered a violation.


Supported Metrics

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
SQL_SRV_RESPONSE_TIME Service Response (for each execution) Seconds
BUFFER_CACHE_HIT Buffer Cache Hit (%) % of cache accesses
LIBRARY_CACHE_HIT Library Cache Hit (%) % of cache accesses
LIBRARY_CACHE_MISS Library Cache Miss (%) % of cache accesses
MEMORY_SORTS_PCT Sorts in Memory (%) % of sorts
REDO_ALLOCATION_HIT Redo Log Allocation Hit % of redo allocations
TRANSACTION_RATE Number of Transactions (for each second) Transactions for each Second
PHYSICAL_READS_SEC Physical Reads (for each second) Reads for each Second
PHYSICAL_READS_TXN Physical Reads (for each transaction) Reads for each Transaction
PHYSICAL_WRITES_SEC Physical Writes (for each second) Writes for each Second
PHYSICAL_WRITES_TXN Physical Writes (for each transaction) Writes for each Transaction
PHYSICAL_READS_DIR_SEC Direct Physical Reads (for each second) Reads for each Second
PHYSICAL_READS_DIR_TXN Direct Physical Reads (for each transaction) Reads for each Transaction
PHYSICAL_WRITES_DIR_SEC Direct Physical Writes (for each second) Writes for each Second
PHYSICAL_WRITES_DIR_TXN Direct Physical Writes (for each transaction) Writes for each Transaction
PHYSICAL_READS_LOB_SEC Direct LOB Physical Reads (for each second) Reads for each Second
PHYSICAL_READS_LOB_TXN Direct LOB Physical Reads (for each transaction) Reads for each Transaction
PHYSICAL_WRITES_LOB_SEC Direct LOB Physical Writes (for each second) Writes for each Second
PHYSICAL_WRITES_LOB_TXN Direct LOB Physical Writes (for each transaction) Writes for each Transaction
REDO_GENERATED_SEC Redo Generated (for each second) Redo Bytes for each Second
REDO_GENERATED_TXN Redo Generated (for each transaction) Redo Bytes for each Transaction
DATABASE_WAIT_TIME Database Wait Time (%) % of all database time
DATABASE_CPU_TIME Database CPU Time (%) % of all database time
LOGONS_SEC Cumulative Logons (for each second) Logons for each Second
LOGONS_TXN Cumulative Logons (for each transaction) Logons for each Transaction
LOGONS_CURRENT Current Number of Logons Number of Logons
OPEN_CURSORS_SEC Cumulative Open Cursors (for each second) Cursors for each Second
OPEN_CURSORS_TXN Cumulative Open Cursors (for each transaction) Cursors for each Transaction
OPEN_CURSORS_CURRENT Current Number of Cursors Number of Cursors
USER_COMMITS_SEC User Commits (for each second) Commits for each Second
USER_COMMITS_TXN User Commits (for each transaction) Commits for each Transaction
USER_ROLLBACKS_SEC User Rollbacks (for each second) Rollbacks for each Second
USER_ROLLBACKS_TXN User Rollbacks (for each transaction) Rollbacks for each Transaction
USER_CALLS_SEC User Calls (for each second) Calls for each Second
USER_CALLS_TXN User Calls (for each transaction) Calls for each Transaction
RECURSIVE_CALLS_SEC Recursive Calls (for each second) Calls for each Second
RECURSIVE_CALLS_TXN Recursive Calls (for each transaction) Calls for each Transaction
SESS_LOGICAL_READS_SEC Session Logical Reads (for each second) Reads for each Second
SESS_LOGICAL_READS_TXN Session Logical Reads (for each transaction) Reads for each Transaction
DBWR_CKPT_SEC DBWR Checkpoints (for each second) Checkpoints for each Second
LOG_SWITCH_SEC Background Checkpoints (for each second) Checkpoints for each Second
REDO_WRITES_SEC Redo Writes (for each second) Writes for each Second
REDO_WRITES_TXN Redo Writes (for each transaction) Writes for each Transaction
LONG_TABLE_SCANS_SEC Scans on Long Tables (for each second) Scans for each Second
LONG_TABLE_SCANS_TXN Scans on Long Tables (for each transaction) Scans for each Transaction
TOTAL_TABLE_SCANS_SEC Total Table Scans (for each second) Scans for each Second
TOTAL_TABLE_SCANS_TXN Total Table Scans (for each transaction) Scans for each Transaction
FULL_INDEX_SCANS_SEC Fast Full Index Scans (for each second) Scans for each Second
FULL_INDEX_SCANS_TXN Fast Full Index Scans (for each transaction) Scans for each Transaction
TOTAL_INDEX_SCANS_SEC Total Index Scans (for each second) Scans for each Second
TOTAL_INDEX_SCANS_TXN Total Index Scans (for each transaction) Scans for each Transaction
TOTAL_PARSES_SEC Total Parses (for each second) Parses for each Second
TOTAL_PARSES_TXN Total Parses(for each transaction) Parses for each Transaction
HARD_PARSES_SEC Hard Parses(for each second) Parses for each Second
HARD_PARSES_TXN Hard Parses(for each transaction) Parses for each Transaction
PARSE_FAILURES_SEC Parse Failures (for each second) Parses for each Second
PARSE_FAILURES_TXN Parse Failures (for each transaction) Parses for each Transaction
DISK_SORT_SEC Sorts to Disk (for each second) Sorts for each Second
DISK_SORT_TXN Sorts to Disk (for each transaction) Sorts for each Transaction
ROWS_PER_SORT Rows Processed for each Sort Rows for each Sort
EXECUTE_WITHOUT_PARSE Executes Performed Without Parsing % of all executes
SOFT_PARSE_PCT Soft Parse (%) % of all parses
CURSOR_CACHE_HIT Cursor Cache Hit (%) % of soft parses
USER_CALLS_PCT User Calls (%) % of all calls
TXN_COMMITTED_PCT Transactions Committed (%) % of all transactions
NETWORK_BYTES_SEC Network Bytes, for each second Bytes for each Second
RESPONSE_TXN Response (for each transaction) Seconds for each Transaction
DATA_DICT_HIT Data Dictionary Hit (%) % of dictionary accesses
DATA_DICT_MISS Data Dictionary Miss (%) % of dictionary accesses
SHARED_POOL_FREE_PCT Shared Pool Free(%) % of shared pool
AVERAGE_FILE_READ_TIME Average File Read Time Microseconds
AVERAGE_FILE_WRITE_TIME Average File Write Time Microseconds
DISK_IO Disk I/O Milliseconds
PROCESS_LIMIT_PCT Process Limit Usage (%) % of maximum value
SESSION_LIMIT_PCT Session Limit Usage (%) % of maximum value
USER_LIMIT_PCT User Limit Usage (%) % of maximum value
AVG_USERS_WAITING Average Number of Users Waiting on a Class of Wait Events Count of sessions
DB_TIME_WAITING Percent of Database Time Spent Waiting on a Class of Wait Events % of Database Time
APPL_DESGN_WAIT_SCT Application Design Wait (by session count) Count of sessions
APPL_DESGN_WAIT_TIME Application Design Wait (by time) Microseconds
PHYS_DESGN_WAIT_SCT Physical Design Wait (by session count) Count of sessions
PHYS_DESGN_WAIT_TIME Physical Design Wait (by time) Microseconds
CONTENTION_WAIT_SCT Internal Contention Wait (by session count) Count of sessions
CONTENTION_WAIT_TIME Internal Contention Wait (by time) Microseconds
PSERVICE_WAIT_SCT Process Service Wait (by session count) Count of sessions
PSERVICE_WAIT_TIME Process Service Wait (by time) Microseconds
NETWORK_MSG_WAIT_SCT Network Message Wait (by session count) Count of sessions
NETWORK_MSG_WAIT_TIME Network Message Wait (by time) Microseconds
DISK_IO_WAIT_SCT Disk I/O Wait (by session count) Count of sessions
OS_SERVICE_WAIT_SCT Operating System Service Wait (by session count) Count of sessions
OS_SERVICE_WAIT_TIME Operating System Service Wait (by time) Microseconds
DBR_IO_LIMIT_WAIT_SCT Resource Mgr I/O Limit Wait (by session count) Count of sessions
DBR_IO_LIMIT_WAIT_TIME Resource Mgr I/O Limit Wait (by time) Microseconds
DBR_CPU_LIMIT_WAIT_SCT Resource Mgr CPU Limit Wait (by session count) Count of sessions
DBR_CPU_LIMIT_WAIT_TIME Resource Mgr CPU Limit Wait (by time) Microseconds
DBR_USR_LIMIT_WAIT_SCT Resource Mgr User Limit Wait (by session count) Count of sessions
DBR_USR_LIMIT_WAIT_TIME Resource Mgr User Limit Wait (by time) Microseconds
OS_SCHED_CPU_WAIT_SCT Operating System Scheduler CPU Wait (by session count) Count of sessions
OS_SCHED_CPU__WAIT_TIME Operating System Scheduler CPU Wait (by time) Microseconds
CLUSTER_MSG_WAIT_SCT Cluster Messaging Wait (by session count) Count of sessions
CLUSTER_MSG_WAIT_TIME Cluster Messaging Wait (by time) Microseconds
OTHER_WAIT_SCT Other Waits (by session count) Count of sessions
OTHER_WAIT_TIME Other Waits (by time) Microseconds
ENQUEUE_TIMEOUTS_SEC Enqueue Timeouts (for each second) Timeouts for each Second
ENQUEUE_TIMEOUTS_TXN Enqueue Timeouts (for each transaction) Timeouts for each Transaction
ENQUEUE_WAITS_SEC Enqueue Waits (for each second) Waits for each Second
ENQUEUE_WAITS_TXN Enqueue Waits (for each transaction) Waits for each Transaction
ENQUEUE_DEADLOCKS_SEC Enqueue Deadlocks (for each second) Deadlocks for each Second
ENQUEUE_DEADLOCKS_TXN Enqueue Deadlocks (for each transaction) Deadlocks for each Transaction
ENQUEUE_REQUESTS_SEC Enqueue Requests (for each second) Requests for each Second
ENQUEUE_REQUESTS_TXN Enqueue Requests (for each transaction) Requests for each Transaction
DB_BLKGETS_SEC DB Block Gets (for each second) Gets for each Second
DB_BLKGETS_TXN DB Block Gets (for each transaction) Gets for each Transaction
CONSISTENT_GETS_SEC Consistent Gets (for each second) Gets for each Second
CONSISTENT_GETS_TXN Consistent Gets (for each transaction) Gets for each Transaction
DB_BLKCHANGES_SEC DB Block Changes (for each second) Changes for each Second
DB_BLKCHANGES_TXN DB Block Changes (for each transaction) Changes for each Transaction
CONSISTENT_CHANGES_SEC Consistent Changes (for each second) Changes for each Second
CONSISTENT_CHANGES_TXN Consistent Changes (for each transaction) Changes for each Transaction
SESSION_CPU_SEC Database CPU (for each second) Microseconds for each Second
SESSION_CPU_TXN Database CPU (for each transaction) Microseconds for each Transaction
CR_BLOCKS_CREATED_SEC CR Blocks Created (for each second) Blocks for each Second
CR_BLOCKS_CREATED_TXN CR Blocks Created (for each transaction) Blocks for each Transaction
CR_RECORDS_APPLIED_SEC CR Undo Records Applied (for each second) Records for each Second
CR_RECORDS_APPLIED_TXN CR Undo Records Applied (for each transaction) Records for each Transaction
RB_RECORDS_APPLIED_SEC Rollback Undo Records Applied (for each second) Records for each Second
RB_RECORDS_APPLIED_TXN Rollback Undo Records Applied(for each transaction) Records for each Transaction
LEAF_NODE_SPLITS_SEC Leaf Node Splits (for each second) Splits for each Second
LEAF_NODE_SPLITS_TXN Leaf Node Splits (for each transaction) Splits for each Transaction
BRANCH_NODE_SPLITS_SEC Branch Node Splits (for each second) Splits for each Second
BRANCH_NODE_SPLITS_TXN Branch Node Splits (for each transaction) Splits for each Transaction
GC_BLOCKS_CORRUPT Global Cache Blocks Corrupt Blocks
GC_BLOCKS_LOST Global Cache Blocks Lost Blocks
GC_AVG_CR_GET_TIME Global Cache CR Request Milliseconds
GC_AVG_CUR_GET_TIME Global Cache Current Request Milliseconds
PX_DOWNGRADED_SEC Downgraded Parallel Operations (for each second) Operations for each Second
PX_DOWNGRADED_25_SEC Downgraded to 25% and more (for each second) Operations for each Second
PX_DOWNGRADED_50_SEC Downgraded to 50% and more (for each second) Operations for each Second
PX_DOWNGRADED_75_SEC Downgraded to 75% and more (for each second) Operations for each Second
PX_DOWNGRADED_SER_SEC Downgraded to serial (for each second) Operations for each Second
BLOCKED_USERS Number of Users blocked by some Session Number of Users
PGA_CACHE_HIT PGA Cache Hit (%) % bytes processed in PGA
ELAPSED_TIME_PER_CALL Elapsed time for each user call for each service Microseconds for each call
CPU_TIME_PER_CALL CPU time for each user call for each service Microseconds for each call
TABLESPACE_PCT_FULL Tablespace space usage % full
TABLESPACE_BYT_FREE Tablespace bytes space usage Kilobytes free


Summary of DBMS_SERVER_ALERT Subprograms

Table 94-4 DBMS_SERVER_ALERT Package Subprograms

Subprogram Description
EXPAND_MESSAGE Function
Expands alert messages
GET_THRESHOLD Procedure
Gets the current threshold settings for a specified metric
SET_THRESHOLD Procedure
Sets the warning and critical thresholds for a specified metric


EXPAND_MESSAGE Function

This function expands alert messages.

Syntax

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;

Parameters

Table 94-5 EXPAND_MESSAGE Procedure Parameters

Parameter Description
user_language The language of the current session.
message_id Id of the alert message
argument_1 The first argument in the alert message.
argument_2 The second argument in the alert message.
argument_3 The third argument in the alert message.
argument_4 The fourth argument in the alert message.
argument_5 The fifth argument in the alert message.


GET_THRESHOLD Procedure

This procedure gets the current threshold settings for the specified metric.

Syntax

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);

Parameters

Table 94-6 GET_THRESHOLD Procedure Parameters

Parameter Description
metrics_id The internal name of the metric. See "Supported Metrics".
warning_operator The operator for the comparing the actual value with the warning threshold.
warning_value The warning threshold value.
critical_operator The operator for the comparing the actual value with the critical threshold.
critical_value The critical threshold value.
observation_period The period at which the metric values are computed and verified against the threshold setting.
consecutive_occurrences The number of observation periods the metric value should violate the threshold value before the alert is issued.
instance_name The name of the instance for which the threshold is set. This is NULL for database-wide alerts.
object_type Either OBJECT_TYPE_SYSTEM or OBJECT_TYPE_SERVICE.
object_name The name of the object.


SET_THRESHOLD Procedure

This procedure sets the warning and critical thresholds for a specified metric.

Syntax

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);

Parameters

Table 94-7 SET_THRESHOLD Procedure Parameters

Parameter Description
metrics_id The internal name of the metric. See "Supported Metrics".
warning_operator The operator for the comparing the actual value with the warning threshold (such as OPERATOR_GE). See "Relational Operators".
warning_value The warning threshold value. This is NULL if no warning threshold is set. A list of values may be specified for OPERATOR_CONTAINS.
critical_operator The operator for the comparing the actual value with the critical threshold. See "Relational Operators".
critical_value The critical threshold value. This is NULL if not set. A list of values may be specified for OPERATOR_CONTAINS.
observation_period The period at which the metric values are computed and verified against the threshold setting. The valid range is 1 to 60 minutes.
consecutive_occurrences The number of observation periods the metric value should violate the threshold value before the alert is issued.
instance_name The name of the instance for which the threshold is set. This is NULL for database-wide alerts.
object_type See "Object Types".
object_name The name of the object. This is NULL for SYSTEM.