Setting threshhold value and critical value of a Metric in Oracle

Example scipt to set threshold for Alerts
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
How to set on metric SHARED_POOL_FREE_PCT ?

begin
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.&Metric,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '&Warning',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '&Critical',
observation_period => 10,
consecutive_occurrences => 5,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_&type,
object_name => &object_name);
end;
/

Enter value for metric: SHARED_POOL_FREE_PCT
Enter value for warning: 15
Enter value for critical: 5
Enter value for type: SYSTEM
Enter value for object_name: NULL

Supported Object Types
~~~~~~~~~~~~~~~~~~~~~~
OBJECT_TYPE_SYSTEM CONSTANT BINARY_INTEGER := 1;
OBJECT_TYPE_FILE CONSTANT BINARY_INTEGER := 2;
OBJECT_TYPE_SERVICE CONSTANT BINARY_INTEGER := 3;
OBJECT_TYPE_EVENT_CLASS CONSTANT BINARY_INTEGER := 4;
OBJECT_TYPE_TABLESPACE CONSTANT BINARY_INTEGER := 5;
OBJECT_TYPE_SESSION CONSTANT BINARY_INTEGER := 9;

METRIC_ID METRIC_NAME
---------- ------------------------------------------
0 Number of Sessions Waiting (Event)
1 Total Time Waited
2 Total Wait Counts

1000 Average Users Waiting Counts ( AVG_USERS_WAITING )
1001 Database Time Spent Waiting (%) ( DB_TIME_WAITING )
1002 Total Wait Counts
1003 Total Time Waited
2000 Buffer Cache Hit Ratio ( BUFFER_CACHE_HIT )
2001 Memory Sorts Ratio ( MEMORY_SORTS_PCT )
2002 Redo Allocation Hit Ratio ( REDO_ALLOCATION_HIT )
2003 User Transaction Per Sec ( USER_TRANSACTIONS_SEC )
2004 Physical Reads Per Sec ( PHYSICAL_READS_SEC )
2005 Physical Reads Per Txn ( PHYSICAL_READS_TXN )
2006 Physical Writes Per Sec ( PHYSICAL_WRITES_SEC )
2007 Physical Writes Per Txn ( PHYSICAL_WRITES_TXN )
2008 Physical Reads Direct Per Sec ( PHYSICAL_READS_DIR_SEC )
2009 Physical Reads Direct Per Txn ( PHYSICAL_READS_DIR_TXN )
2010 Physical Writes Direct Per Sec ( PHYSICAL_WRITES_DIR_SEC )
2011 Physical Writes Direct Per Txn ( PHYSICAL_WRITES_DIR_TXN )
2012 Physical Reads Direct Lobs Per Sec ( PHYSICAL_READS_LOB_SEC )
2013 Physical Reads Direct Lobs Per Txn ( PHYSICAL_READS_LOB_TXN )
2014 Physical Writes Direct Lobs Per Sec ( PHYSICAL_WRITES_LOB_SEC )
2015 Physical Writes Direct Lobs Per Txn ( PHYSICAL_WRITES_LOB_TXN )
2016 Redo Generated Per Sec ( REDO_GENERATED_SEC )
2017 Redo Generated Per Txn ( REDO_GENERATED_TXN )
2018 Logons Per Sec ( LOGONS_SEC )
2019 Logons Per Txn ( LOGONS_TXN )
2020 Open Cursors Per Sec ( OPEN_CURSORS_SEC )
2021 Open Cursors Per Txn ( OPEN_CURSORS_TXN )
2022 User Commits Per Sec ( USER_COMMITS_SEC )
2023 User Commits Percentage ( USER_COMMITS_TXN )
2024 User Rollbacks Per Sec ( USER_ROLLBACKS_SEC )
2025 User Rollbacks Percentage ( USER_ROLLBACKS_TXN )
2026 User Calls Per Sec ( USER_CALLS_SEC )
2027 User Calls Per Txn ( USER_CALLS_TXN )
2028 Recursive Calls Per Sec ( RECURSIVE_CALLS_SEC )
2029 Recursive Calls Per Txn ( RECURSIVE_CALLS_TXN )
2030 Logical Reads Per Sec ( SESS_LOGICAL_READS_SEC)
2031 Logical Reads Per Txn ( SESS_LOGICAL_READS_TXN )
2032 DBWR Checkpoints Per Sec ( DBWR_CKPT_SEC )
2033 Background Checkpoints Per Sec ( BACKGROUND_CKPT_SEC )
2034 Redo Writes Per Sec ( REDO_WRITES_SEC )
2035 Redo Writes Per Txn ( REDO_WRITES_TXN )
2036 Long Table Scans Per Sec ( LONG_TABLE_SCANS_SEC )
2037 Long Table Scans Per Txn ( LONG_TABLE_SCANS_TXN )
2038 Total Table Scans Per Sec ( TOTAL_TABLE_SCANS_SEC )
2039 Total Table Scans Per Txn ( TOTAL_TABLE_SCANS_TXN )
2040 Full Index Scans Per Sec ( FULL_INDEX_SCANS_SEC )
2041 Full Index Scans Per Txn ( FULL_INDEX_SCANS_TXN )
2042 Total Index Scans Per Sec ( TOTAL_INDEX_SCANS_SEC )
2043 Total Index Scans Per Txn ( TOTAL_INDEX_SCANS_TXN )
2044 Total Parse Count Per Sec ( TOTAL_PARSES_SEC )
2045 Total Parse Count Per Txn ( TOTAL_PARSES_TXN )
2046 Hard Parse Count Per Sec ( HARD_PARSES_SEC )
2047 Hard Parse Count Per Txn ( HARD_PARSES_TXN )
2048 Parse Failure Count Per Sec ( PARSE_FAILURES_SEC )
2049 Parse Failure Count Per Txn ( PARSE_FAILURES_TXN )
2050 Cursor Cache Hit Ratio ( CURSOR_CACHE_HIT )
2051 Disk Sort Per Sec ( DISK_SORT_SEC )
2052 Disk Sort Per Txn ( DISK_SORT_TXN )

2053 Rows Per Sort ( ROWS_PER_SORT )
2054 Execute Without Parse Ratio ( EXECUTE_WITHOUT_PARSE )
2055 Soft Parse Ratio ( SOFT_PARSE_PCT )
2056 User Calls Ratio ( USER_CALLS_PCT )
2057 Host CPU Utilization (%)
2058 Network Traffic Volume Per Sec ( NETWORK_BYTES_SEC )
2059 Enqueue Timeouts Per Sec ( ENQUEUE_TIMEOUTS_SEC )
2060 Enqueue Timeouts Per Txn ( ENQUEUE_TIMEOUTS_TXN )
2061 Enqueue Waits Per Sec ( ENQUEUE_WAITS_SEC )
2062 Enqueue Waits Per Txn ( ENQUEUE_WAITS_TXN )
2063 Enqueue Deadlocks Per Sec ( ENQUEUE_DEADLOCKS_SEC )
2064 Enqueue Deadlocks Per Txn ( ENQUEUE_DEADLOCKS_TXN )
2065 Enqueue Requests Per Sec ( ENQUEUE_REQUESTS_SEC )
2066 Enqueue Requests Per Txn ( ENQUEUE_REQUESTS_TXN )
2067 DB Block Gets Per Sec ( DB_BLKGETS_SEC )
2068 DB Block Gets Per Txn ( DB_BLKGETS_TXN )
2069 Consistent Read Gets Per Sec ( CONSISTENT_GETS_SEC )
2070 Consistent Read Gets Per Txn ( CONSISTENT_GETS_TXN )
2071 DB Block Changes Per Sec ( DB_BLKCHANGES_SEC )
2072 DB Block Changes Per Txn ( DB_BLKCHANGES_TXN )
2073 Consistent Read Changes Per Sec ( CONSISTENT_CHANGES_SEC )
2074 Consistent Read Changes Per Txn ( CONSISTENT_CHANGES_TXN )
2075 CPU Usage Per Sec ( SESSION_CPU_SEC )
2076 CPU Usage Per Txn ( SESSION_CPU_TXN )
2077 CR Blocks Created Per Sec ( CR_BLOCKS_CREATED_SEC )
2078 CR Blocks Created Per Txn ( CR_BLOCKS_CREATED_TXN )
2079 CR Undo Records Applied Per Sec ( CR_RECORDS_APPLIED_SEC )
2080 CR Undo Records Applied Per Txn ( CR_RECORDS_APPLIED_TXN )
2081 User Rollback UndoRec Applied Per Sec ( RB_RECORDS_APPLIED_SEC )
2082 User Rollback Undo Records Applied Per Txn ( RB_RECORDS_APPLIED_TXN )
2083 Leaf Node Splits Per Sec ( LEAF_NODE_SPLITS_SEC )
2084 Leaf Node Splits Per Txn ( LEAF_NODE_SPLITS_TXN )
2085 Branch Node Splits Per Sec ( BRANCH_NODE_SPLITS_SEC )
2086 Branch Node Splits Per Txn ( BRANCH_NODE_SPLITS_TXN )
2087 PX downgraded 25% or more Per Sec ( PX_DOWNGRADED_25_SEC )
2088 PX downgraded 50% or more Per Sec ( PX_DOWNGRADED_50_SEC )
2089 PX downgraded 75% or more Per Sec ( PX_DOWNGRADED_75_SEC )
2090 PX downgraded Parallel Operation Per Sec ( PX_DOWNGRADED_SEC )
2091 PX downgraded to serial Per Sec ( PX_DOWNGRADED_SER_SEC )
2092 Physical Read Total IO Requests Per Sec
2093 Physical Read Total Bytes Per Sec ( PX_DOWNGRADED_SEC )
2094 GC CR Block Received Per Second
2095 GC CR Block Received Per Txn ( PX_DOWNGRADED_SER_SEC )
2096 GC Current Block Received Per Second
2097 GC Current Block Received Per Txn
2098 Global Cache Average CR Get Time ( GC_AVG_CR_GET_TIME )
2099 Global Cache Average Current Get Time ( GC_AVG_CUR_GET_TIME )
2100 Physical Write Total IO Requests Per Sec
2101 Global Cache Blocks Corrupted ( GC_BLOCKS_CORRUPT )
2102 Global Cache Blocks Lost ( GC_BLOCKS_LOST )
2103 Current Logons Count ( LOGONS_CURRENT )
2104 Current Open Cursors Count ( OPEN_CURSORS_CURRENT )
2105 User Limit % ( USER_LIMIT_PCT )
2106 SQL Service Response Time ( SQL_SRV_RESPONSE_TIME )
2107 Database Wait Time Ratio ( DATABASE_WAIT_TIME )
2108 Database CPU Time Ratio ( DATABASE_CPU_TIME )
2109 Response Time Per Txn ( RESPONSE_TXN )
2110 Row Cache Hit Ratio ( ROW_CACHE_HIT )
2111 Row Cache Miss Ratio ( ROW_CACHE_MISS )
2112 Library Cache Hit Ratio ( LIBARY_CACHE_HIT )
2113 Library Cache Miss Ratio ( LIBARY_CACHE_MISS )
2114 Shared Pool Free % ( SHARED_POOL_FREE_PCT )
2115 PGA Cache Hit % ( PGA_CACHE_HIT )
2118 Process Limit % ( PROCESS_LIMIT_PCT )
2119 Session Limit % ( SESSION_LIMIT_PCT )
2120 Executions Per Txn
2121 Executions Per Sec ( EXECUTIONS_PER_SEC )
2122 Txns Per Logon
2123 Database Time Per Sec ( DB_TIME_PER_SEC )
2124 Physical Write Total Bytes Per Sec
2125 Physical Read IO Requests Per Sec
2126 Physical Read Bytes Per Sec
2127 Physical Write IO Requests Per Sec
2128 Physical Write Bytes Per Sec
2129 DB Block Changes Per User Call
2130 DB Block Gets Per User Call
2131 Executions Per User Call
2132 Logical Reads Per User Call
2133 Total Sorts Per User Call
2134 Total Table Scans Per User Call
2135 Current OS Load
2136 ??????????????????? (STREAMS_POOL_USED_PCT)
4000 Blocked User Session Count ( BLOCKED_USERS )
5000 User Transaction Count (Session)
5001 CPU Time (Session)
5002 Physical Reads (Session)
5003 PGA Memory (Session)
5004 Hard Parse Count (Session)
5005 Total Parse Count (Session)
5006 Physical Reads Ratio (Sess/Sys) %
5007 Logical Reads Ratio (Sess/Sys) %
5008 Soft Parse Count
5009 Logical Read Count
6000 Elapsed Time Per User Call ( ELAPSED_TIME_PER_CALL )
6001 CPU Time Per User Call ( CPU_TIME_PER_CALL )
6002 User Calls Per Second
6003 DB Time Per Second
6004 DB Time Per User Call
7000 Average File Read Time (Files-Long) ( AVG_FILE_READ_TIME )
7001 Average File Write Time (Files-Long) ( AVG_FILE_WRITE_TIME )
7002 Physical Reads (Files-Long)
7003 Physical Writes (Files-Long)
7004 Physical Block Reads (Files-Long)
7005 Physical Block Writes (Files-Long)
9000 Tablespace Space Usage ( TABLESPACE_PCT_FULL )
9001 Tablespace Bytes Space Usage ( TABLESPACE_BYT_FREE )