DBMS_SERVER_ALERT 简单介绍

一、 DBMS_SERVER_ALERT 简绍
   DBMS_SERVER_ALERT 简介：是oracle里用来设置数据库告警服务的。当一个被监控的测量值超过阀值就会启动事件，并记录到数据库中。
    DBMS_SERVER_ALERT告警级别分有两个1级别是关键警告，5级为警告。这两个条件只有测量值达到请求的时候才高出来。

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.


二、如何使用DBMS_SERVER_ALERT程序包
下面的指标是设置告警的区域
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.

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
下面是关系操作符的介绍
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

五、DBMS_SERVER_ALERT子程序用于的使用方法和传入的参数。

下面是DBMS_SERVER_ALERT子程序中

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.


例子：
以DBMS_SERVER_ALERT.set_threshold 为例，

通过:
BEGIN
    dbms_server_alert.set_threshold        (dbms_server_alert.tablespace_pct_full,dbms_server_alert.operator_ge,     70,dbms_server_alert.operator_ge, 85, 1, 1,  NULL,dbms_server_alert.object_type_tablespace,'TBS_01');

END;

其中，大于等于70%为warning,大于等于85%为critical.

如有表空间达到阀值，可以用下面的语句查询，报警信息，

SELECT reason, message_level,DECODE(message_level, 5, 'WARNING', 1, 'CRITICAL') ALERT_LEVEL FROM dba_outstanding_alerts WHERE object_type = 'TABLESPACE';

可以用下面的语句查询当前的监控阀值设置，

SELECT warning_value, critical_value, status,object_name FROM dba_thresholds WHERE metrics_name = 'Tablespace Space Usage';

表空间的空间和利用率查询，可用下面的语句，

Select * from sys.DBA_TABLESPACE_USAGE_METRICS;