dbms_server_alert tips

Using Oracle dbms_server_alert

Details on deploying Oracle alerts is fully discussed in my book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

In the olden days of Oracle management the DBA has to write their own DBA alerts, and many Oracle professionals have extremely sophisticated alert scripts:

• server-side alerts - You can write UNIX/Linux shell scripts to check for alert log messages, dump and trace files, etc.  For working scripts, see the Oracle script collection, where working scripts are available for instant download.
   
• internal Oracle alerts - I've created dozens of customized alerts for every possible exception condition within Oracle, a valuable approach to alert you before your database crashes.  For examples of such scripts, see Jon Emmon's book "Oracle Shell Scripting".  For a description of Oracle Linux commands, see the Oracle Linux command reference poster.

Starting in Oracle 10g we have the ability to set alerts within OEM, specifying the alert threshold and notifications method (e-mail, pager, etc.).

Oracle also created a procedural interface to the alert mechanism using the new dbms_server_alert package.

The dbms_server_alert package contains procedures to set and get threshold values. When the space usage exceeds either of the two thresholds, an appropriate alert is issued. If the thresholds are not specified, the defaults are 85% for warning and 97% for critical thresholds.

dbms_server_alert procedures

The dbms_server_alert package has these procedures, set threshold, get_threshold, and expand_threshold:

dbms_server_alert.get_threshold

dbms_server_alert.get_threshold(
   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);
 

dbms_server_alert.set_threshold

dbms_server_alert.set_threshold(
   metrics_id               IN  NUMBER,
   warning_operator         IN  NUMBER,
   warning_value            IN  VARCHAR2,
   critical_operator        IN  NUMBER,
   critical_value           IN  VARCHAR2,
   observation_period       IN  NUMBER,
   consecutive_occurrences  IN  NUMBER,
   instance_name            IN  VARCHAR2,
   object_type              IN  NUMBER,
   object_name              IN  VARCHAR2);
 

BEGIN
    DBMS_SERVER_ALERT.SET_THRESHOLD(
        metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
        warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
        warning_value => '10',
        critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
        critical_value => '20',
        observation_period => 1,
        consecutive_occurrences => 3,
        instance_name => 'ZMYDB',
        object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
        object_name => 'CUST'
     );
END;
/
 

The amount that thresholds are to be higher or lower than the baseline values can be specified by percentage. Here is some useful information for the Oracle dbms_server_alert package. DB Control then sets the thresholds using the derived values for each metric using the Oracle dbms_server_alert.set_threshold procedure for the dbms_server_alert package. All thresholds except for space related alerts should be explicitly defined. To enable the dbms_server_alert package functionality, set the statistical_level initialization parameter to TYPICAL or ALL.

This from the Oracle documentation lists all values for dbms_server_alert:
 

Metrics Name (Internal)	Metrics 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_INDEXE_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

Here are the operator values for dbms_server_alert (from the documentation):
 

dbms_server_alert dictionary views

The dbms_server_alert package has the corresponding DBA views to see the status of all scheduled alerts:

• dba_outstanding_alerts - This view will show all existing alerts.
   

• dba_alert_history - This shows a history of all preexisting alerts that were set by dbms_server_alert.
   

select
   object_type,
   object_name,
   reason,
   suggested_action,
   time_suggested,
   resolution,
   advisor_name,
   metric_value,
   message_type,
   message_group,
   message_level
from
   dba_alert_history
where
   -- creation_time <= sysdate-1 and
   resolution = 'cleared'
order by
   creation_time desc

• dba_alert_arguments - This view shows the arguments to dbms_server_alert.
   

• dba_thresholds - This new view shows the threshold values for all alerts.

select
   object_type,
   object_name,
   metrics_name,
   warning_operator,
   warning_value,
   critical_operator,
   critical_value,
   observation_period,
   consecutive_occurrences
from
   sys.dba_thresholds
where
   object_type = 'TABLE';

expand_message  is a function that expands alert messages and returns that expanded message as a VARCHAR2.

get_threshold  is a procedure that fetches the current threshold settings for the specified metric.

set_threshold is a procedure that defines the active threshold settings for the specified metric, both for warning and critical states.

Get the Complete Oracle Tuning Details  The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,150 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.