| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/catalrt.sql
{ORACLE_HOME}/rdbms/admin/dbmsslrt.sql |
| First Available |
10.1 |
| Constants: Message Levels |
| Name |
Data Type |
Value |
| SUBTYPE SEVERITY_LEVEL_T |
PLS_INTEGER |
NULL |
| LEVEL_CRITICAL |
PLS_INTEGER |
1 |
| LEVEL_WARNING |
PLS_INTEGER |
5 |
| LEVEL_CLEAR |
PLS_INTEGER |
32 |
|
Constants: Alert Reasons |
| Name |
Data Type |
Value |
Description |
| RSN_SLTE |
REASON_ID_T |
0 |
stateless test alert |
| RSN_SFTE |
REASON_ID_T |
1 |
stateful test alert |
| RSN_SYS_BFCHP |
REASON_ID_T |
2 |
buffer cache hit ratio |
| RSN_FIL_AFRT |
REASON_ID_T |
3 |
avg file read time |
| RSN_SVC_ELAPC |
REASON_ID_T |
4 |
service elapsed time |
| RSN_EVC_AUWC |
REASON_ID_T |
5 |
wait session count |
| RSN_SES_BLUSC |
REASON_ID_T |
6 |
blocked users |
| RSN_SYS_GBKCR |
REASON_ID_T |
7 |
global cache blocks corrupt |
| RSN_SYS_GBKLS |
REASON_ID_T |
8 |
global cache blocks lost |
| RSN_SFTS |
REASON_ID_T |
9 |
tablespace alert |
| RSN_LQWT |
REASON_ID_T |
10 |
long query warning on undo tbs |
| RSN_LQWR |
REASON_ID_T |
11 |
long query warn on rollback seg |
| RSN_OSAT |
REASON_ID_T |
12 |
operation suspended on tablespace |
| RSN_OSAR |
REASON_ID_T |
13 |
oper suspended on rollback seg |
| RSN_OSAD |
REASON_ID_T |
14 |
operation suspended on data |
| RSN_OSAQ |
REASON_ID_T |
15 |
operation suspended on quota |
| RSN_SYS_MSRTP |
REASON_ID_T |
16 |
memory sorts ratio |
| RSN_SYS_RDAHP |
REASON_ID_T |
17 |
redo allocation hit ratio |
| RSN_SYS_UTXNR |
REASON_ID_T |
18 |
user transaction per sec |
| RSN_SYS_PHRDR |
REASON_ID_T |
19 |
physical reads per sec |
| RSN_SYS_PHRDX |
REASON_ID_T |
20 |
physical reads per txn |
| RSN_SYS_PHWRR |
REASON_ID_T |
21 |
physical writes per sec |
| RSN_SYS_PHWRX |
REASON_ID_T |
22 |
physical writes per txn |
| RSN_SYS_PRDDR |
REASON_ID_T |
23 |
physical reads direct per sec |
| RSN_SYS_PRDDX |
REASON_ID_T |
24 |
physical reads direct per txn |
| RSN_SYS_PWRDR |
REASON_ID_T |
25 |
physical writes direct per sec |
| RSN_SYS_PWRDX |
REASON_ID_T |
26 |
physical writes direct per txn |
| RSN_SYS_PRDLR |
REASON_ID_T |
27 |
phys reads direct lobs per sec |
| RSN_SYS_PRDLX |
REASON_ID_T |
28 |
phys reads direct lobs per txn |
| RSN_SYS_PWDLR |
REASON_ID_T |
29 |
phys writes direct lobs per sec |
| RSN_SYS_PWDLX |
REASON_ID_T |
30 |
phys writes direct lobs per txn |
| RSN_SYS_RDGNR |
REASON_ID_T |
31 |
redo generated per sec |
| RSN_SYS_LGNTR |
REASON_ID_T |
32 |
logons per sec |
| RSN_SYS_LGNTX |
REASON_ID_T |
33 |
logons per txn |
| RSN_SYS_OCSTR |
REASON_ID_T |
34 |
open cursors per sec |
| RSN_SYS_OCSTX |
REASON_ID_T |
35 |
open cursors per txn |
| RSN_SYS_UCMTR |
REASON_ID_T |
36 |
user commits per sec |
| RSN_SYS_UCMTP |
REASON_ID_T |
37 |
user commits percentage |
| RSN_SYS_URBKR |
REASON_ID_T |
38 |
user rollbacks per sec |
| RSN_SYS_URBKP |
REASON_ID_T |
39 |
user rollbacks percentage |
| RSN_SYS_UCALR |
REASON_ID_T |
40 |
user calls per sec |
| RSN_SYS_UCALX |
REASON_ID_T |
41 |
user calls per txn |
| RSN_SYS_RCALR |
REASON_ID_T |
42 |
recursive calls per sec |
| RSN_SYS_RCALX |
REASON_ID_T |
43 |
recursive calls per txn |
| RSN_SYS_SLRDR |
REASON_ID_T |
44 |
logical reads per sec |
| RSN_SYS_SLRDX |
REASON_ID_T |
45 |
logical reads per txn |
| RSN_SYS_DWCPR |
REASON_ID_T |
46 |
DBWR checkpoints per sec |
| RSN_SYS_BGCPR |
REASON_ID_T |
47 |
background checkpoints per sec |
| RSN_SYS_RDWRR |
REASON_ID_T |
48 |
redo writes per sec |
| RSN_SYS_RDWRX |
REASON_ID_T |
49 |
redo writes per txn |
| RSN_SYS_LTSCR |
REASON_ID_T |
50 |
long table scans per sec |
| RSN_SYS_LTSCX |
REASON_ID_T |
51 |
long table scans per txn |
| RSN_SYS_TTSCR |
REASON_ID_T |
52 |
total table scans per sec |
| RSN_SYS_TTSCX |
REASON_ID_T |
53 |
total table scans per txn |
| RSN_SYS_FISCR |
REASON_ID_T |
54 |
full index scans per sec |
| RSN_SYS_FISCX |
REASON_ID_T |
55 |
full index scans per txn |
| RSN_SYS_TISCR |
REASON_ID_T |
56 |
total index scans per sec |
| RSN_SYS_TISCX |
REASON_ID_T |
57 |
total index scans per txn |
| RSN_SYS_TPRSR |
REASON_ID_T |
58 |
total parse count per sec |
| RSN_SYS_TPRSX |
REASON_ID_T |
59 |
total parse count per txn |
| RSN_SYS_HPRSR |
REASON_ID_T |
60 |
hard parse count per sec |
| RSN_SYS_HPRSX |
REASON_ID_T |
61 |
hard parse count per txn |
| RSN_SYS_FPRSR |
REASON_ID_T |
62 |
parse failure count per sec |
| RSN_SYS_FPRSX |
REASON_ID_T |
63 |
parse failure count per txn |
| RSN_SYS_CCHTR |
REASON_ID_T |
64 |
cursor cache hit ratio |
| RSN_SYS_DSRTR |
REASON_ID_T |
65 |
disk sort per sec |
| RSN_SYS_DSRTX |
REASON_ID_T |
66 |
disk sort per txn |
| RSN_SYS_RWPST |
REASON_ID_T |
67 |
rows per sort |
| RSN_SYS_XNPRS |
REASON_ID_T |
68 |
execute without parse ratio |
| RSN_SYS_SFPRP |
REASON_ID_T |
69 |
soft parse ratio |
| RSN_SYS_UCALP |
REASON_ID_T |
70 |
user calls ratio |
| RSN_SYS_NTWBR |
REASON_ID_T |
71 |
network traffic volume per sec |
| RSN_SYS_EQTOR |
REASON_ID_T |
72 |
enqueue timeouts per sec |
| RSN_SYS_EQTOX |
REASON_ID_T |
73 |
enqueue timeouts per txn |
| RSN_SYS_EQWTR |
REASON_ID_T |
74 |
enqueue waits per sec |
| RSN_SYS_EQWTX |
REASON_ID_T |
75 |
enqueue waits per txn |
| RSN_SYS_EQDLR |
REASON_ID_T |
76 |
enqueue deadlocks per sec |
| RSN_SYS_EQDLX |
REASON_ID_T |
77 |
enqueue deadlocks per txn |
| RSN_SYS_EQRQR |
REASON_ID_T |
78 |
enqueue requests per sec |
| RSN_SYS_EQRQX |
REASON_ID_T |
79 |
enqueue requests per txn |
| RSN_SYS_DBBGR |
REASON_ID_T |
80 |
db block gets per sec |
| RSN_SYS_DBBGX |
REASON_ID_T |
81 |
db block gets per txn |
| RSN_SYS_CRGTR |
REASON_ID_T |
82 |
consistent read gets per sec |
| RSN_SYS_CRGTX |
REASON_ID_T |
83 |
consistent read gets per txn |
| RSN_SYS_DBBCR |
REASON_ID_T |
84 |
db block changes per sec |
| RSN_SYS_DBBCX |
REASON_ID_T |
85 |
db block changes per txn |
| RSN_SYS_CRCHR |
REASON_ID_T |
86 |
consistent read changes per sec |
| RSN_SYS_CRCHX |
REASON_ID_T |
87 |
consistent read changes per txn |
| RSN_SYS_CPUUR |
REASON_ID_T |
88 |
cpu usage per sec |
| RSN_SYS_CPUUX |
REASON_ID_T |
89 |
cpu usage per txn |
| RSN_SYS_CRBCR |
REASON_ID_T |
90 |
cr blocks created per sec |
| RSN_SYS_CRBCX |
REASON_ID_T |
91 |
cr blocks created per txn |
| RSN_SYS_CRRAX |
REASON_ID_T |
92 |
cr undo records applied per txn |
| RSN_SYS_RBRAR |
REASON_ID_T |
93 |
user rollbk undorec appl per sec |
| RSN_SYS_RBRAX |
REASON_ID_T |
94 |
user rollbk undorec appl per txn |
| RSN_SYS_LNSPR |
REASON_ID_T |
95 |
leaf node splits per sec |
| RSN_SYS_LNSPX |
REASON_ID_T |
96 |
leaf node splits per txn |
| RSN_SYS_BNSPR |
REASON_ID_T |
97 |
branch node splits per sec |
| RSN_SYS_BNSPX |
REASON_ID_T |
98 |
branch node splits per txn |
| RSN_SYS_PX25R |
REASON_ID_T |
99 |
px downgraded 25% or more per sec |
| RSN_SYS_PX50R |
REASON_ID_T |
100 |
px downgraded 50% or more per sec |
| RSN_SYS_PX75R |
REASON_ID_T |
101 |
px downgraded 75% or more per sec |
| RSN_SYS_PXDGR |
REASON_ID_T |
102 |
px downgraded per sec |
| RSN_SYS_PXSRR |
REASON_ID_T |
103 |
px downgraded to serial per sec |
| RSN_SYS_GACRT |
REASON_ID_T |
104 |
global cache average CR get time |
| RSN_SYS_GACUT |
REASON_ID_T |
105 |
global cache ave current get time |
| RSN_SYS_LGONC |
REASON_ID_T |
106 |
current logons count |
| RSN_SYS_OPCSC |
REASON_ID_T |
107 |
current open cursors count |
| RSN_SYS_USLMP |
REASON_ID_T |
108 |
user limit % |
| RSN_SYS_SQSRT |
REASON_ID_T |
109 |
sql service response time |
| RSN_SYS_DBWTT |
REASON_ID_T |
110 |
database wait time ratio |
| RSN_SYS_DBCPT |
REASON_ID_T |
111 |
database cpu time ratio |
| RSN_SYS_RSPTX |
REASON_ID_T |
112 |
response time per txn |
| RSN_SYS_RCHTR |
REASON_ID_T |
113 |
row cache hit ratio |
| RSN_SYS_LCHTR |
REASON_ID_T |
114 |
library cache hit ratio |
| RSN_SYS_LCMSR |
REASON_ID_T |
115 |
library cache miss ratio |
| RSN_SYS_SPFRP |
REASON_ID_T |
116 |
shared pool free % |
| RSN_SYS_PGCHR |
REASON_ID_T |
117 |
pga cache hit % |
| RSN_SYS_PRCLP |
REASON_ID_T |
118 |
process limit % |
| RSN_SYS_SESLP |
REASON_ID_T |
119 |
session limit % |
| RSN_FIL_AFWT |
REASON_ID_T |
120 |
avg file write time |
| RSN_EVC_DTSW |
REASON_ID_T |
121 |
total time waited |
| RSN_SYS_RCMSR |
REASON_ID_T |
122 |
row cache miss ratio |
| RSN_RADL |
REASON_ID_T |
123 |
recovery area disk limit alerts |
| RSN_SYS_RDGNX |
REASON_ID_T |
124 |
redo generated per txn |
| RSN_SYS_CRRAR |
REASON_ID_T |
125 |
cr undo records applied per sec |
| RSN_SYS_THNTF |
REASON_ID_T |
126 |
threshold notice on system type |
| RSN_FIL_THNTF |
REASON_ID_T |
127 |
threshold notice on file type |
| RSN_EVC_THNTF |
REASON_ID_T |
128 |
threshold notice on event class |
| RSN_SVC_THNTF |
REASON_ID_T |
129 |
threshold notice on service |
| RSN_TBS_THNTF |
REASON_ID_T |
130 |
threshold notice on tablespace |
| RSN_SVC_CPUPC |
REASON_ID_T |
131 |
cpu time per user call |
| RSN_SES_THNTF |
REASON_ID_T |
132 |
threshold notice on sessions |
| RSN_SFBTS |
REASON_ID_T |
133 |
tablespace bytes based thresholds |
| RSN_SYS_INQPR |
REASON_ID_T |
134 |
instance should be quiesced |
| RSN_FAN_INSTANCE_UP |
REASON_ID_T |
135 |
instance up |
| RSN_FAN_INSTANCE_DOWN |
REASON_ID_T |
136 |
instance down |
| RSN_FAN_SERVICE_UP |
REASON_ID_T |
137 |
service up |
| RSN_FAN_SERVICE_DOWN |
REASON_ID_T |
138 |
service down |
| RSN_FAN_SERVICE_MEMBER_UP |
REASON_ID_T |
139 |
svc member up |
| RSN_FAN_SERVICE_MEMBER_DOWN |
REASON_ID_T |
140 |
svc member down |
| RSN_FAN_SVC_PRECONNECT_UP |
REASON_ID_T |
141 |
preconnect up |
| RSN_FAN_SVC_PRECONNECT_DOWN |
REASON_ID_T |
142 |
preconnect down |
| RSN_FAN_NODE_DOWN |
REASON_ID_T |
143 |
node down |
| RSN_FAN_ASM_INSTANCE_UP |
REASON_ID_T |
144 |
asm instance up |
| RSN_FAN_ASM_INSTANCE_DOWN |
REASON_ID_T |
145 |
asm instance down |
| RSN_FAN_DATABASE_UP |
REASON_ID_T |
146 |
database up |
| RSN_FAN_DATABASE_DOWN |
REASON_ID_T |
147 |
database down |
| RSN_SYS_DBTMR |
REASON_ID_T |
148 |
DB Time per Sec |
| RSN_SYS_XCNTR |
REASON_ID_T |
149 |
Executions Per Sec |
| RSN_STR_CAPTURE_ABORTED |
REASON_ID_T |
150 |
capture aborted |
| RSN_STR_APPLY_ABORTED |
REASON_ID_T |
151 |
apply aborted |
| RSN_STR_PROPAGATION_ABORTED |
REASON_ID_T |
152 |
propagation aborted |
| RSN_STR_STREAMSPOOL_FREE_PCT |
REASON_ID_T |
153 |
streams pool free |
| RSN_STR_ERROR_QUEUE |
REASON_ID_T |
154 |
new entry in error queue |
| RSN_LOG_ARCHIVE_LOG_GAP |
REASON_ID_T |
155 |
archived log gap for logminer |
| RSN_SYS_ACTVS |
REASON_ID_T |
156 |
average active sessions |
| RSN_SYS_SRLAT |
REASON_ID_T |
157 |
Avg synchronous single-blk read latency |
| RSN_SYS_IOMBS |
REASON_ID_T |
158 |
i/o megabytes |
| RSN_SYS_IOREQ |
REASON_ID_T |
159 |
i/o requests |
|
| |
| Constants: Metric Names |
| sec =second |
tx = transaction |
Data Type = BINARY_INTEGER |
|
| Internal Name |
External Name |
Unit |
Value |
| AVERAGE_FILE_READ_TIME |
Average File Read Time |
Microseconds |
7000 |
| AVERAGE_FILE_WRITE_TIME |
Average File Write Time |
Microseconds |
7001 |
| AVG_USERS_WAITING |
Average Number of Users Waiting on a Class of Wait Events |
Count of sessions |
1000 |
| BACKGROUND_CKPT_SEC |
? |
Number of Checkpoints |
2033 |
| BLOCKED_USERS |
Number of Users blocked by some Session |
Number of Users |
4000 |
| BRANCH_NODE_SPLITS_SEC |
Branch Node Splits |
Splits / sec |
2085 |
| BRANCH_NODE_SPLITS_TXN |
Branch Node Splits |
Splits / tx |
2086 |
| BUFFER_CACHE_HIT |
Buffer Cache Hit |
% of cache accesses |
2000 |
| CONSISTENT_CHANGES_SEC |
Consistent Changes |
Changes / sec |
2073 |
| CONSISTENT_CHANGES_TXN |
Consistent Changes |
Changes / tx |
2074 |
| CONSISTENT_GETS_SEC |
Consistent Gets |
Gets / sec |
2069 |
| CONSISTENT_GETS_TXN |
Consistent Gets |
Gets / tx |
2070 |
| CPU_TIME_PER_CALL |
CPU time per user
call per service |
Microseconds per call |
6001 |
| CR_BLOCKS_CREATED_SEC |
CR Blocks Created |
Blocks / sec |
2077 |
| CR_BLOCKS_CREATED_TXN |
CR Blocks Created |
Blocks / tx |
2078 |
| CR_RECORDS_APPLIED_SEC |
CR Undo Records Applied |
Records / sec |
2079 |
| CR_RECORDS_APPLIED_TXN |
CR Undo Records Applied |
Records / tx |
2080 |
| CURSOR_CACHE_HIT |
Cursor Cache Hit |
% of soft parses |
2050 |
| DATABASE_CPU_TIME |
Database CPU Time |
% of all database time |
2108 |
| DATABASE_WAIT_TIME |
Database Wait Time |
% of all database time |
2107 |
| DB_BLKCHANGES_SEC |
DB Block Changes |
Changes / sec |
2071 |
| DB_BLKCHANGES_TXN |
DB Block Changes |
Changes / tx |
2072 |
| DB_BLKGETS_SEC |
DB Block Gets |
Gets / sec |
2067 |
| DB_BLKGETS_TXN |
DB Block Gets |
Gets / tx |
2068 |
| DB_TIME_PER_SEC |
? |
? |
2123 |
| DB_TIME_WAITING |
Percent of Database Time Spent Waiting on a Class of Wait Events |
% of Database time |
1001 |
| DBWR_CKPT_SEC |
DBWR Checkpoints |
Checkpoints per sec |
2032 |
| DISK_SORT_SEC |
Sorts to Disk |
Sorts / sec |
2051 |
| DISK_SORT_TXN |
Sorts to Disk |
Sorts / tx |
2052 |
| ELAPSED_TIME_PER_CALL |
User call elapsed time for each service |
Microseconds / call |
6000 |
| ENQUEUE_DEADLOCKS_SEC |
Enqueue Deadlocks |
Deadlocks / sec |
2063 |
| ENQUEUE_DEADLOCKS_TXN |
Enqueue Deadlocks |
Deadlocks / tx |
2064 |
| ENQUEUE_REQUESTS_SEC |
Enqueue Requests |
Requests / sec |
2065 |
| ENQUEUE_REQUESTS_TXN |
Enqueue Requests |
Requests / tx |
2066 |
| ENQUEUE_TIMEOUTS_SEC |
Enqueue Timeouts |
Timeouts / sec |
2059 |
| ENQUEUE_TIMEOUTS_TXN |
Enqueue Timeouts |
Timeouts / tx |
2060 |
| ENQUEUE_WAITS_SEC |
Enqueue Waits |
Waits / sec |
2061 |
| ENQUEUE_WAITS_TXN |
Enqueue Waits |
Waits / tx |
2062 |
| EXECUTE_WITHOUT_PARSE |
Executes Performed Without Parsing |
% of all executes |
2054 |
| EXECUTIONS_PER_SEC |
Executions |
Executions / sec |
2121 |
| FULL_INDEX_SCANS_SEC |
Fast Full Index Scans per second |
Scans / sec |
2040 |
| FULL_INDEX_SCANS_TXN |
Fast Full Index Scans per transaction |
Scans / tx |
2041 |
| GC_AVG_CR_GET_TIME |
Global Cache CR Request |
Milliseconds |
2098 |
| GC_AVG_CUR_GET_TIME |
Global Cache Current Request |
Milliseconds |
2099 |
| GC_BLOCKS_CORRUPT |
Global Cache Blocks Corrupt |
Blocks |
2101 |
| GC_BLOCKS_LOST |
Global Cache Blocks Lost |
Blocks |
2102 |
| HARD_PARSES_SEC |
Hard Parses |
Parses / sec |
2046 |
| HARD_PARSES_TXN |
Hard Parses |
Parse / tx |
2047 |
| LEAF_NODE_SPLITS_SEC |
Leaf Node Splits |
Splits / sec |
2083 |
| LEAF_NODE_SPLITS_TXN |
Leaf Node Splits |
Splits / tx |
2084 |
| LIBRARY_CACHE_HIT |
Library Cache Hit |
% of cache accesses |
2112 |
| LIBRARY_CACHE_MISS |
Library Cache Miss |
% of cache accesses |
2113 |
| LOGONS_CURRENT |
Current Number of Logons |
Number of Logons |
2103 |
| LOGONS_SEC |
Cumulative Logons |
Logons / sec |
2018 |
| LOGONS_TXN |
Cumulative Logons |
Logons / tx |
2019 |
| LONG_TABLE_SCANS_SEC |
Scans on Long Tables |
Scans / sec |
2036 |
| LONG_TABLE_SCANS_TXN |
Scans on Long Tables |
Scans / tx |
2037 |
| MEMORY_SORTS_PCT |
Sorts in Memory |
% of sorts |
2001 |
| NETWORK_BYTES_SEC |
Network Bytes |
Bytes / sec |
2058 |
| OPEN_CURSORS_CURRENT |
Current Number of Cursors |
Number of Cursors |
2104 |
| OPEN_CURSORS_SEC |
Cumulative Open Cursors |
Cursors sec |
2020 |
| OPEN_CURSORS_TXN |
Cumulative Open Cursors |
Cursors / tx |
2021 |
| PARSE_FAILURES_SEC |
Parse Failures |
Parses / sec |
2048 |
| PARSE_FAILURES_TXN |
Parse Failures |
Parses / tx |
2049 |
| PGA_CACHE_HIT |
PGA Cache Hit Bytes |
% processed (PGA) |
2115 |
| PHYSICAL_READS_DIR_SEC |
Direct Physical Reads |
Reads / sec |
2008 |
| PHYSICAL_READS_DIR_TXN |
Direct Physical Reads |
Reads / tx |
2009 |
| PHYSICAL_READS_LOB_SEC |
Direct LOB Physical Reads |
Reads / sec |
2012 |
| PHYSICAL_READS_LOB_TXN |
Direct LOB Physical Reads |
Reads / tx |
2013 |
| PHYSICAL_READS_SEC |
Physical Reads |
Reads / sec |
2004 |
| PHYSICAL_READS_TXN |
Physical Reads |
Reads / tx |
2005 |
| PHYSICAL_WRITES_DIR_SEC |
Direct Physical Writes |
Writes / sec |
2010 |
| PHYSICAL_WRITES_DIR_TXN |
Direct Physical Writes |
Writes / tx |
2011 |
| PHYSICAL_WRITES_LOB_SEC |
Direct LOB Physical Writes |
Writes / sec |
2014 |
| PHYSICAL_WRITES_LOB_TXN |
Direct LOB Physical Writes |
Writes / tx |
2015 |
| PHYSICAL_WRITES_SEC |
Physical Writes |
Writes / sec |
2006 |
| PHYSICAL_WRITES_TXN |
Physical Writes |
Writes / tx |
2007 |
| PROCESS_LIMIT_PCT |
Process Limit Usage |
% of maximum value |
2118 |
| PX_DOWNGRADED_SEC |
Downgraded Parallel Operations
(also 2093?) |
Operations / sec |
2090 |
| PX_DOWNGRADED_25_SEC |
Downgraded to 25% and more |
Operations / sec |
2087 |
| PX_DOWNGRADED_50_SEC |
Downgraded to 50% and more |
Operations / sec |
2088 |
| PX_DOWNGRADED_75_SEC |
Downgraded to 75% and more |
Operations / sec |
2089 |
| PX_DOWNGRADED_SER_SEC |
Downgraded to serial
(also 2095?) |
Operations / sec |
2091 |
| RB_RECORDS_APPLIED_SEC |
Rollback Undo Records Applied |
Records / sec |
2081 |
| RB_RECORDS_APPLIED_TXN |
Rollback Undo Records Applied |
Records / tx |
2082 |
| RECURSIVE_CALLS_SEC |
Recursive Calls |
Calls / sec |
2028 |
| RECURSIVE_CALLS_TXN |
Recursive Calls |
Calls / tx |
2029 |
| REDO_ALLOCATION_HIT |
Redo Log Allocation
Hit |
% of redo allocations |
2002 |
| REDO_GENERATED_SEC |
Redo Generated |
Bytes / sec |
2016 |
| REDO_GENERATED_TXN |
Redo Generated |
Bytes / tx |
2017 |
| REDO_WRITES_SEC |
Redo Writes |
Writes/sec |
2034 |
| REDO_WRITES_TXN |
Redo Writes |
Writes / tx |
2035 |
| RESPONSE_TXN |
Response |
Secs / tx |
2109 |
| ROWS_CACHE_HIT |
? |
? |
2110 |
| ROWS_CACHE_MISS |
? |
? |
2111 |
| ROWS_PER_SORT |
Rows Processed for
each Sort |
Rows / Sort |
2053 |
| SESS_LOGICAL_READS_SEC |
Session Logical Reads |
Reads / sec |
2030 |
| SESS_LOGICAL_READS_TXN |
Session Logical Reads |
Reads / tx |
2031 |
| SESSION_CPU_SEC |
Database CPU |
Microseconds / sec |
2075 |
| SESSION_CPU_TXN |
Database CPU |
Microseconds / tx |
2076 |
| SESSION_LIMIT_PCT |
Session Limit Usage |
% of maximum value |
2119 |
| SHARED_POOL_FREE_PCT |
Shared Pool Free |
% of shared pool |
2114 |
| SOFT_PARSE_PCT |
Soft Parse |
% of all parses |
2055 |
| SQL_SRV_RESPONSE_TIME |
Service Response per
execution |
Seconds |
2106 |
| STREAMS_POOL_USED_PCT |
Percent of the streams
pool used |
|
2136 |
| TABLESPACE_BYT_FREE |
Tablespace bytes free |
KB Free |
9001 |
| TABLESPACE_PCT_FULL |
Tablespace space
usage |
% Full |
9000 |
| TOTAL_INDEX_SCANS_SEC |
Total Index Scans |
Scans / sec |
2042 |
| TOTAL_INDEX_SCANS_TXN |
Total Index Scans |
Scans / tx |
2043 |
| TOTAL_PARSES_SEC |
Total Parses |
Parses / sec |
2044 |
| TOTAL_PARSES_TXN |
Total Parses |
Parses / tx |
2045 |
| TOTAL_TABLE_SCANS_SEC |
Total Table Scans |
Scans / sec |
2038 |
| TOTAL_TABLE_SCANS_TXN |
Total Table Scans |
Scans / tx |
2039 |
| USER_CALLS_PCT |
User Calls |
% of all calls |
2056 |
| USER_CALLS_SEC |
User Calls |
Calls / sec |
2026 |
| USER_CALLS_TXN |
User Calls |
Calls / tx |
2027 |
| USER_COMMITS_SEC |
User Commits |
Commits / sec |
2022 |
| USER_COMMITS_TXN |
User Commits |
Transactions / sec |
2023 |
| USER_LIMIT_PCT |
User Limit Usage |
% of maximum value |
2105 |
| USER_ROLLBACKS_SEC |
User Rollbacks |
Rollbacks / sec |
2024 |
| USER_ROLLBACKS_TXN |
User Rollbacks |
Rollbacks / tx |
2025 |
| USER_TRANSACTIONS_SEC |
User transactions per second |
? |
2003 |
|
| |
Constants: Object Types |
| Name |
Value |
Description |
| OBJECT_TYPE_EVENT_CLASS |
4 |
Metrics collected on wait event class level. Currently supported metrics are AVG_USERS_WAITING and DB_TIME_WAITING
|
| OBJECT_TYPE_FILE |
2 |
Metrics collected on the file level. These are used for AVERAGE_FILE_READ_TIME and AVERAGE_FILE_WRITE_TIME metrics |
| OBJECT_TYPE_SERVICE |
3 |
Metrics collected on the service level. Currently ELAPSED_TIME_PER_CALL and CPU_TIME_PER_CALL are collected |
| OBJECT_TYPE_SESSION |
9 |
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 |
| OBJECT_TYPE_SYSTEM |
1 |
Metrics collected on the system level for each instance |
| OBJECT_TYPE_TABLESPACE |
5 |
Metrics collected on the tablespace level. Currently only TABLESPACE_PCT_FULL is collected |
|
Constants: Relational Operator Types |
| Name |
Value |
Description |
| OPERATOR_CONTAINS |
5 |
Value contained in a list of threshold values is considered a violation
|
| OPERATOR_DO_NOT_CHECK |
7 |
Don't apply default threshold to OBJECT_TYPE_TABLESPACE |
| OPERATOR_EQ |
1 |
Value equal to the threshold one is a violation |
| OPERATOR_GE |
4 |
Value greater or equal to the threshold is a violation |
| OPERATOR_GT |
0 |
Value greater than the threshold is a violation |
| OPERATOR_LE |
3 |
Value less or equal than the threshold is a violation |
| OPERATOR_LT |
2 |
Value less than the threshold is a violation |
| OPERATOR_NE |
6 |
Value not equal to the threshold one is a violation |
|
| Data Types |
CREATE TYPE threshold_type AS OBJECT(
object_type
NUMBER,
object_name
VARCHAR2(513),
metrics_id
NUMBER,
instance_name VARCHAR2(16),
flags
NUMBER,
warning_operator NUMBER,
warning_value VARCHAR2(256),
critical_operator NUMBER,
critical_value VARCHAR2(256),
observation_period NUMBER,
consecutive_occurrences NUMBER,
object_id
NUMBER);
/
-- Create threshold set type for threshold table function
CREATE TYPE threshold_type_set AS TABLE OF threshold_type;
/
-- Create alert type used for in AQ messages
CREATE TYPE sys.alert_type AS OBJECT (
timestamp_originating TIMESTAMP WITH TIME ZONE,
organization_id VARCHAR2(10),
component_id
VARCHAR2(3),
message_id
NUMBER,
hosting_client_id VARCHAR2(64),
message_type
VARCHAR2(12),
message_group
VARCHAR2(30),
message_level
NUMBER,
host_id
VARCHAR2(256),
host_nw_addr
VARCHAR2(256),
module_id
VARCHAR2(50),
process_id
VARCHAR2(128),
user_id
VARCHAR2(30),
upstream_component_id VARCHAR2(30),
downstream_component_id VARCHAR2(4),
execution_context_id VARCHAR2(128),
error_instance_id VARCHAR2(142),
reason_argument_count NUMBER,
reason_argument_1 VARCHAR2(513),
reason_argument_2 VARCHAR2(513),
reason_argument_3 VARCHAR2(513),
reason_argument_4 VARCHAR2(513),
reason_argument_5 VARCHAR2(513),
sequence_id
NUMBER,
reason_id
NUMBER,
object_owner
VARCHAR2(30),
object_name
VARCHAR2(513),
subobject_name VARCHAR2(30),
object_type
VARCHAR2(30),
instance_name
VARCHAR2(16),
instance_number
NUMBER,
scope
VARCHAR2(10),
advisor_name
VARCHAR2(30),
metric_value
NUMBER,
suggested_action_msg_id NUMBER,
action_argument_count NUMBER,
action_argument_1 VARCHAR2(30),
action_argument_2 VARCHAR2(30),
action_argument_3 VARCHAR2(30),
action_argument_4 VARCHAR2(30),
action_argument_5 VARCHAR2(30));
/
|
| Default Database Threshold |
-- The following code is run by the catalrt.sql script during database installation
BEGIN
dbms_server_alert.set_threshold(9000, NULL, NULL, NULL, NULL,
1, 1, '', 5, '');
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = -00001 THEN
NULL; -- unique constraint error
ELSE
RAISE;
END IF;
END;
/
-- to set the default database thresholds
|
Dependencies |
| BSLN_INTERNAL |
DBMS_SVRALRT_LIB |
| DBA_ALERT_HISTORY |
DBMS_SYS_ERROR |
| DBA_OUTSTANDING_ALERTS |
GV$ALERT_TYPES |
| DBA_THRESHOLDS |
GV$DBFILE |
| DBMS_BACKUP_RESTORE |
THRESHOLD_TYPE |
| DBMS_HA_ALERTS_PRVT |
THRESHOLD_TYPE_SET |
| DBMS_PRVTAQIP |
UTL_LMS |
| DBMS_SERVER_ALERT_PRVT |
WRI$_ALERT_THRESHOLD |
|
| Value explanation |
For the parameters warning_value and critical_value integers between
0 and 100 are treated as percentages. Values larger than 100 are treated as bytes |
| Warnings Enabled by Default |
Recovery area low on free resources
Resumable session suspended
Snapshot too old
Tablespace Usage: warning = 85%, critical = 97% |
| Security Model |
execute is granted to the dba and
imp_full_database roles |
| |
| EXPAND_MESSAGE |
Expand Alert Message |
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; |
-- create an queue agent
exec dbms_aqadm.create_aq_agent(agent_name => 'ALERT_AGT');
-- subscribe to alert_que
exec dbms_aqadm.add_subscriber(queue_name => 'ALERT_QUE', subscriber => AQ$_AGENT('ALERT_AGT','',0));
-- associate user with the secure queue
exec dbms_aqadm.enable_db_access(agent_name => 'ALERT_AGT', db_username=>'SYSTEM');
exec dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'ALERT_QUE',grantee => 'SYSTEM', grant_option => FALSE);
-- dequeue an alert
DECLARE
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message ALERT_TYPE;
message_handle RAW(16);
BEGIN
dequeue_options.consumer_name := 'ALERTAGENT';
dequeue_options.wait := DBMS_AQ.NO_WAIT;
dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;
dbms_aq.dequee(queue_name => 'ALERT_QUE', dequeue_options =>
dequeue_options, message_properties => message_properties,
payload => message, msgid => message_handle);
dbms_output.put_line('Alert message dequeued:');
dbms_output.put_line(' Timestamp: ' ||
message.timestamp_originating);
dbms_output.put_line('Organization Id: ' ||
message.organization_id);
dbms_output.put_line('Component Id: ' || message.component_id);
dbms_output.put_line('Message Type: ' || message.message_type);
dbms_output.put_line('Message Group: ' ||
message.message_group);
dbms_output.put_line('Message Level: ' ||
message.message_level);
dbms_output.put_line('Host Id: ' || message.host_id);
dbms_output.put_line('Host Network Addr: ' ||
message.host_nw_addr);
dbms_output.put_line(' Reason: ' ||
dbms_server_alert.expand_message(userenv('LANGUAGE'),
message.message_id, message.reason_argument_1,
message.reason_argument_2, message.reason_argument_3,
message.reason_argument_4, message.reason_argument_5));
dbms_output.put_line('Sequence Id: ' || message.sequence_id);
dbms_output.put_line('Reason Id: ' || message.reason_id);
dbms_output.put_line('Object Name: ' || message.object_name);
dbms_output.put_line('Object Type: ' || message.object_type);
dbms_output.put_line('Instance Name: '||
message.instance_name);
dbms_output.put_line('Suggested action: ' ||
dbms_server_alert.expand_message(userenv('LANGUAGE'),
message.suggested_action_msg_id,
message.action_argument_1, message.action_argument_2,
message.action_argument_3, message.action_argument_4,
message.action_argument_5));
dbms_output.put_line('Advisor Name: ' || message.advisor_name);
dbms_output.put_line(' Scope: ' || message.scope);
END;
/ |
| |
| GET_THRESHOLD |
Gets the threshold setting for a given
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, -- default 10 min.
consecutive_occurrences OUT BINARY_INTEGER,
instance_name IN VARCHAR2,
-- NULL for db-wide alerts
object_type IN BINARY_INTEGER,
object_name IN VARCHAR2); |
set serveroutput on
DECLARE
vWarnOp NUMBER(10);
vWarnVal VARCHAR2(100);
vCritOp NUMBER(10);
vCritVal VARCHAR2(100);
vObsvPer NUMBER(5);
vConOcur NUMBER(5);
BEGIN
dbms_server_alert.get_threshold(
dbms_server_alert.tablespace_pct_full,
vWarnOp, vWarnVal, vCritOp, vCritVal, vObsvPer, vConOcur,
NULL,
dbms_server_alert.object_type_tablespace,
'UWDATA');
dbms_output.put_line('WarnOp: ' || TO_CHAR(vWarnOp));
dbms_output.put_line('WarnVal: ' || vWarnVal);
dbms_output.put_line('CritOp: ' || TO_CHAR(vCritOp));
dbms_output.put_line('CritVal: ' || vCritVal);
END;
/
|
| |
| SET_THRESHOLD |
Sets the threshold setting for a given 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, -- default 10 min.
consecutive_occurrences IN BINARY_INTEGER,
instance_name IN VARCHAR2, -- NULL for db-wide alerts
object_type IN BINARY_INTEGER,
object_name IN VARCHAR2); |
-- warning_operator = tablespace_pct_full
-- warning = 80
-- critical = 95
-- observation_period = 1
-- consecutive occurrences = 1
-- instance_name is NULL
-- object_type = tablespace
-- object_name = UWDATA
-- start space usage checking
BEGIN
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 80,
dbms_server_alert.operator_ge, 95, 1, 1, NULL,
dbms_server_alert.object_type_tablespace,
'UWDATA');
END;
/
col warning_value format a20
col critical_value format a20
SELECT warning_value, critical_value, status
FROM dba_thresholds
WHERE metrics_name = 'Tablespace Space Usage'
AND object_name = 'UWDATA';
-- check status
SELECT reason, resolution
FROM dba_alert_history
WHERE object_name = 'UWDATA';
-- check for alerts
SELECT reason, message_level,
DECODE(message_level, 5, 'WARNING', 1, 'CRITICAL') ALERT_LEVEL
FROM dba_outstanding_alerts
WHERE object_name = 'UWDATA';
-- stop space usage checking
BEGIN
dbms_server_alert.set_threshold (
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_do_not_check, '0',
dbms_server_alert.operator_do_not_check, '0', 1, 1, NULL,
dbms_server_alert.object_type_tablespace,
'UWDATA');
END;
/
SELECT warning_value, critical_value, status
FROM dba_thresholds
WHERE metrics_name = 'Tablespace Space Usage'
AND object_name = 'UWDATA';
-- reset the usage threshold
BEGIN
dbms_server_alert.set_threshold (
dbms_server_alert.tablespace_pct_full,
NULL, NULL, NULL, NULL, 1, 1, NULL,
dbms_server_alert.object_type_tablespace,
'UWDATA');
END;
/
SELECT warning_value, critical_value, status
FROM dba_thresholds
WHERE metrics_name = 'Tablespace Space Usage'
AND object_name = 'UWDATA'; |
| |
| VIEW_THRESHOLDS |
Pipelined table function used to create the dictionary view DBA_THRESHOLDS |
dbms_server_alert.view_thresholds RETURN threshold_type_set PIPELINED; |
set linesize 131
col metrics_id format 9999
col warning_value format a15
col critical_value format a15
SELECT object_type,metrics_id, instance_name, flags,
warning_operator, warning_value, critical_operator,
critical_value, observation_period, consecutive_occurrences,
object_id
FROM TABLE(dbms_server_alert.view_thresholds); |
| |
| Related Queries |
Alert Types |
set linesize 121
col object_type format a16
col group_name format a20
col internal_metric_category format a23
col internal_metric_name format a23
SELECT reason_id, object_type, type, group_name, scope, internal_metric_category, internal_metric_name
FROM gv$alert_types
ORDER BY 2,3; |
|
