Oracle Database
Monitor Oracle Database Servers and RAC Clusters
Data Collection Setup
Metrics are collected via JDBC from the Oracle Database Instance monitoring views.
Network Requirements
Port: 1521 (TCP) Non-SSL or 2484 (TCP) SSL
Least Privileged User
If your Oracle Database is running on Amazon RDS, run the following statement as DBA admin user to create the LPU:
CREATE USER BLUEMEDORA IDENTIFIED BY P@ssw0rd1;
GRANT CONNECT TO lpu;
GRANT SELECT on gv$instance to lpu;
GRANT SELECT on dba_data_files TO lpu;
GRANT SELECT on dba_free_space TO lpu;
GRANT SELECT on dba_tablespaces TO lpu;
GRANT SELECT on dba_temp_files TO lpu;
GRANT SELECT on global_name TO lpu;
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$ARCHIVE_DEST','lpu','SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$FILESTAT', 'lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$INSTANCE','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$PARAMETER','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$RESOURCE_LIMIT','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$SESSION','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$SGAINFO','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$SQL','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$SYSMETRIC','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$SYSTEM_EVENT','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$SYSTEM_WAIT_CLASS','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('GV_$TEMPSTAT','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVE_DEST','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$CONTROLFILE','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$DATAFILE','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$FILESTAT','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$INSTANCE','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGFILE','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$RECOVERY_AREA_USAGE','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$RESOURCE_LIMIT','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$RMAN_BACKUP_JOB_DETAILS','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$TEMPFILE','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('V_$VERSION','lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('DBA_UNDO_EXTENTS', 'lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('DBA_UNDO_EXTENTS', 'lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('DBA_TEMP_FREE_SPACE', 'lpu', 'SELECT');
EXEC rdsadmin.rdsadmin_util.grant_sys_object('DBA_OUTSTANDING_ALERTS', 'lpu', 'SELECT');
If your Oracle Database is not running on Amazon RDS, run the following statement as a DBA admin user to create the LPU:
CREATE USER BLUEMEDORA IDENTIFIED BY Passw0rd1;
GRANT CONNECT TO BLUEMEDORA;
GRANT SELECT on DBA_DATA_FILES TO BLUEMEDORA;
GRANT SELECT on DBA_FREE_SPACE TO BLUEMEDORA;
GRANT SELECT on DBA_TABLESPACES TO BLUEMEDORA;
GRANT SELECT on DBA_TEMP_FILES TO BLUEMEDORA;
GRANT SELECT on GLOBAL_NAME TO BLUEMEDORA;
GRANT SELECT on DBA_UNDO_EXTENTS TO BLUEMEDORA;
GRANT SELECT on DBA_TEMP_FREE_SPACE TO BLUEMEDORA;
GRANT SELECT on DBA_OUTSTANDING_ALERTS TO BLUEMEDORA;
GRANT SELECT on GV_$INSTANCE TO BLUEMEDORA;
GRANT SELECT on GV_$ARCHIVE_DEST TO BLUEMEDORA;
GRANT SELECT on GV_$FILESTAT TO BLUEMEDORA;
GRANT SELECT on GV_$INSTANCE TO BLUEMEDORA;
GRANT SELECT on GV_$PARAMETER TO BLUEMEDORA;
GRANT SELECT on GV_$RESOURCE_LIMIT TO BLUEMEDORA;
GRANT SELECT on GV_$SESSION TO BLUEMEDORA;
GRANT SELECT on GV_$SGAINFO TO BLUEMEDORA;
GRANT SELECT on GV_$SQL TO BLUEMEDORA;
GRANT SELECT on GV_$SYSMETRIC TO BLUEMEDORA;
GRANT SELECT on GV_$SYSTEM_EVENT TO BLUEMEDORA;
GRANT SELECT on GV_$SYSTEM_WAIT_CLASS TO BLUEMEDORA;
GRANT SELECT on GV_$TEMPSTAT TO BLUEMEDORA;
GRANT SELECT on V_$ARCHIVE_DEST TO BLUEMEDORA;
GRANT SELECT on V_$CONTROLFILE TO BLUEMEDORA;
GRANT SELECT on V_$DATABASE TO BLUEMEDORA;
GRANT SELECT on V_$DATAFILE TO BLUEMEDORA;
GRANT SELECT on V_$FILESTAT TO BLUEMEDORA;
GRANT SELECT on V_$INSTANCE TO BLUEMEDORA;
GRANT SELECT on V_$LOG TO BLUEMEDORA;
GRANT SELECT on V_$LOGFILE TO BLUEMEDORA;
GRANT SELECT on V_$PARAMETER TO BLUEMEDORA;
GRANT SELECT on V_$RECOVERY_AREA_USAGE TO BLUEMEDORA;
GRANT SELECT on V_$RESOURCE_LIMIT TO BLUEMEDORA;
GRANT SELECT on V_$RMAN_BACKUP_JOB_DETAILS TO BLUEMEDORA;
GRANT SELECT on V_$TEMPFILE TO BLUEMEDORA;
GRANT SELECT on V_$VERSION TO BLUEMEDORA;
Supported Versions
Oracle Database: 11.2.x - 18c+
Connection Parameters
Name | Required? | Description |
---|---|---|
Host | Required | The Oracle host to connect to. |
Port | The port for communication to the Oracle host. | |
Username | Required | |
Password | Required | |
SSL Configuration | The SSL mode to use when connecting to the target. Can be configured to not use SSL (No SSL), and use SSL and verify the target's certificate (Verify). | |
Configuration Type | Required | |
SID | Required | The name of the Oracle Instance. |
Service Name | Required | The service name of the Oracle Instance, generally used for RAC instances. |
Query Count | The number of queries to collect. | |
Query History Interval | ||
Order Queries By | The metric by which to order queries by. | |
Show Monitoring Queries | ||
Query Info Table | The table from which to collect query information. | |
Show Query Text | ||
Excluded Queries | ||
Session Count | The number of sessions to collect. | |
Connection Pool Size | The max number of connections that may be simultaneously used to collect monitoring information. |
Metrics
Application
Name |
---|
Active User Sessions |
Application |
Background Types |
Blocked by Unknown User Sessions |
Blocked User Sessions |
Cached User Sessions |
Database ID |
Global Name |
Inactive User Sessions |
Instance ID |
Killed User Sessions |
Non-Waiting User Sessions |
O/S Client Users |
O/S Machines |
Sessions Count |
Sniped User Sessions |
Unblocked User Sessions |
Unknown Wait Time User Sessions |
User Types |
Waiting User Sessions |
Cache Advice Entry
Name |
---|
Buffer Pool Name |
Cache Size (Megabytes) |
Database ID |
Estimated Physical Reads |
Global Name |
Size Factor |
Control File
Name |
---|
Control File Name |
Control File Size (Bytes) |
Database ID |
Global Name |
Data Guard Destination
Name |
---|
Database ID |
Destination ID |
Destination Name |
Global Name |
Instance ID |
Status |
Database
Name |
---|
Archive Log Mode |
Archive Logging Enabled Score |
Average Configuration And Health Score |
Average Datafile Read Time (Milliseconds) |
Average Datafile Read Time < 1ms Score |
Controlfile Count |
Database ID |
Days Since Last RMAN Backup |
Expired Undo Extents Size (Bytes) |
Fast Recovery Area Configured |
Fast Recovery Area Configured Score |
Fast Recovery Area Usage |
Fast Recovery Area Usage Score |
Flashback Enabled |
Flashback Enabled Score |
Global Name |
Host List |
Multiplexed Archive Log Files Count |
Multiplexed Archive Log Files Score |
Multiplexed Controlfile Score |
Multiplexed Online Log Files Average Count |
Multiplexed Online Log Files Score |
Oracle Database Type |
Oracle Managed Files Configured |
Oracle Managed Files Configured Score |
Processes Limit Score |
Processes Usage (%) |
RMAN Backup In Last 24 Hours Score |
Sessions Limit Score |
Sessions Usage (%) |
Undo Tablespace Size (Bytes) |
Unexpired Undo Extents Size (Bytes) |
Database File
Name |
---|
Autoextensible |
Average I/O Time (Milliseconds) |
Average Read Time (Milliseconds) |
Average Write Time (Milliseconds) |
Database ID |
File Name |
Global Name |
Max Read Time (Milliseconds) |
Max Size (Bytes) |
Max Write Time (Milliseconds) |
Physical Blocks Read |
Physical Blocks Written |
Physical Reads |
Physical Writes |
Read Time (Milliseconds) |
Single Blocks Read |
Size (Bytes) |
Status |
Tablespace Name |
Write Time (Milliseconds) |
Event
Name |
---|
Average Foreground Time Waited (Milliseconds) |
Average Total Time Waited (Milliseconds) |
Database ID |
Event |
Foreground Time Waited (Milliseconds) |
Foreground Timeouts |
Foreground Waits |
Global Name |
Instance ID |
Total Time Waited (Milliseconds) |
Total Timeouts |
Total Waits |
Wait Class |
Event Wait Group
Name |
---|
Database ID |
Foreground Time Waited (Milliseconds) |
Foreground Waits |
Global Name |
Instance ID |
Total Time Waited (Milliseconds) |
Total Waits |
Wait Class |
Instance
Name |
---|
Active Parallel Sessions (Sessions) |
Active Serial Sessions (Sessions) |
Active State |
Active User Sessions |
Applications |
Archiver |
Average Active Sessions (Sessions) |
Average I/O Time (Milliseconds) |
Average Read Time (Milliseconds) |
Average Synchronous Single-Block Read Latency (Milliseconds) |
Average Write Time (Milliseconds) |
Background Checkpoints (per Second) |
Background CPU Usage (Centiseconds per Second) |
Background Time (per Second) |
Background Types |
Blocked |
Blocked by Unknown User Sessions |
Blocked User Sessions |
Branch Node Splits (per Second) |
Branch Node Splits Per Transaction |
Buffer Cache Hit Ratio (%) |
Buffer Cache Size (Bytes) |
Cached User Sessions |
Cell Physical I/O Interconnect (Bytes) |
Consistent Read Blocks Created (per Second) |
Consistent Read Blocks Created Per Transaction |
Consistent Read Changes (per Second) |
Consistent Read Changes Per Transaction |
Consistent Read Gets (per Second) |
Consistent Read Gets Per Transaction |
Consistent Read Undo Records Applied (per Second) |
Consistent Read Undo Records Applied Per Transaction |
CPU Usage (Centiseconds per Second) |
CPU Usage Per Transaction (Centiseconds) |
Current Logons Count |
Current Open Cursors Count |
Current OS Processes Load |
Cursor Cache Hit Ratio (%) |
Data Definition Language Statements Parallelized (per Second) |
Data Manipulation Language Statements Parallelized (per Second) |
Data Transfer Cache Size (Bytes) |
Database Block Changes (per Second) |
Database Block Changes Per Transaction |
Database Block Changes Per User Call |
Database Block Gets (per Second) |
Database Block Gets Per Transaction |
Database Block Gets Per User Call |
Database CPU Time Ratio (%) |
Database ID |
Database Status |
Database Time (Centiseconds per Second) |
Database Wait Time Ratio (%) |
Database Writer Checkpoints (per Second) |
Disk Sort (per Second) |
Disk Sort Per Transaction |
Enqueue Deadlocks (per Second) |
Enqueue Deadlocks Per Transaction |
Enqueue Locks Current Utilization |
Enqueue Locks Limit |
Enqueue Requests (per Second) |
Enqueue Requests Per Transaction |
Enqueue Timeouts (per Second) |
Enqueue Timeouts Per Transaction |
Enqueue Waits (per Second) |
Enqueue Waits Per Transaction |
Execute Without Parse Ratio (%) |
Executions (per Second) |
Executions Per Transaction |
Executions Per User Call |
Fixed System Global Area Size (Bytes) |
Foreground Latch Time Waited (Milliseconds) |
Foreground Time Waited (Milliseconds) |
Free System Global Area Memory Available (Bytes) |
Full Index Scans (per Second) |
Full Index Scans Per Transaction |
Global Cache Average Consistent Read Get Time (Centiseconds) |
Global Cache Average Current Get Time (Centiseconds) |
Global Cache Blocks Corrupted |
Global Cache Blocks Lost |
Global Cache Consistent Read Block Received (per Second) |
Global Cache Consistent Read Block Received Per Transaction |
Global Cache Current Block Received (per Second) |
Global Cache Current Block Received Per Transaction |
Global Name |
Granule Size (Bytes) |
Hard Parse Count (per Second) |
Hard Parse Count Per Transaction |
Host CPU Usage (Centiseconds per Second) |
Host CPU Utilization (%) |
Host Name |
Inactive User Sessions |
Instance ID |
Instance Name |
Instance Role |
I/O Requests (per Second) |
I/O Throughput (Megabytes per Second) |
Java Pool Size (Bytes) |
Killed User Sessions |
Large Pool Size (Bytes) |
Leaf Node Splits (per Second) |
Leaf Node Splits Per Transaction |
Library Cache Hit Ratio (%) |
Library Cache Miss Ratio (%) |
Logical Reads (per Second) |
Logical Reads Per Transaction |
Logical Reads Per User Call |
Login Mode |
Logons (per Second) |
Logons Per Transaction |
Long Table Scans (per Second) |
Long Table Scans Per Transaction |
Max Read Time (Milliseconds) |
Maximum System Global Area Size (Bytes) |
Max Write Time (Milliseconds) |
Memory Sorts Ratio (%) |
Network Traffic Volume (per Second) |
Non-Waiting User Sessions |
Open Cursors (per Second) |
Open Cursors Per Transaction |
O/S Client Users |
O/S Machines |
Parallel |
Parallel Execution downgraded 1 to 25 percent (per Second) |
Parallel Execution downgraded 25 to 50 percent (per Second) |
Parallel Execution downgraded 50 to 75 percent (per Second) |
Parallel Execution downgraded 75 to 99 percent (per Second) |
Parallel Execution downgraded to serial (per Second) |
Parallel Execution Operations Not Downgraded (per Second) |
Parallel Max Servers Current Utilization |
Parallel Max Servers Limit |
Parallel Statement Queuing Query Coordinator Sessions (Sessions) |
Parallel Statement Queuing Slave Sessions (Sessions) |
Parse Failure Count (per Second) |
Parse Failure Count Per Transaction |
Physical Blocks Read |
Physical Blocks Written |
Physical Read I/O Requests (per Second) |
Physical Read Rate (Bytes per Second) |
Physical Read Total I/O Requests (per Second) |
Physical Reads |
Physical Reads Direct (per Second) |
Physical Reads Direct Lobs (per Second) |
Physical Reads Direct Lobs Per Transaction |
Physical Reads Direct Per Transaction |
Physical Reads Per Transaction |
Physical Write I/O Requests (per Second) |
Physical Write Rate (Bytes per Second) |
Physical Write Total I/O Requests (per Second) |
Physical Writes |
Physical Writes Direct (per Second) |
Physical Writes Direct Lobs (per Second) |
Physical Writes Direct Lobs Per Transaction |
Physical Writes Direct Per Transaction |
Physical Writes Per Transaction |
Process Limit (%) |
Processes Current Utilization |
Processes Limit |
Program Global Area Cache Hit (%) |
Queries Parallelized (per Second) |
Read Time (Milliseconds) |
Recursive Calls (per Second) |
Recursive Calls Per Transaction |
Redo Allocation Hit Ratio (%) |
Redo Buffers Size (Bytes) |
Redo Generated (per Second) |
Redo Generated Per Transaction (Bytes) |
Redo Writes (per Second) |
Redo Writes Per Transaction |
Response Time Per Transaction (Centiseconds) |
Row Cache Hit Ratio (%) |
Row Cache Miss Ratio (%) |
Rows Per Sort |
Run Queue Processes (per Second) |
Session Limit (%) |
Sessions Count |
Sessions Current Utilization |
Sessions Limit |
Shared IO Pool Size (Bytes) |
Shared Pool Free (%) |
Shared Pool Size (Bytes) |
Shutdown Pending |
Single Blocks Read |
Sniped User Sessions |
Soft Parse Ratio (%) |
SQL Service Response Time (Centiseconds) |
Startup Overhead in Shared Pool (Bytes) |
Startup Time |
Status |
Streams Pool Size (Bytes) |
Streams Pool Usage (%) |
Temp Space Used (Bytes) |
Total Foreground Latch Timeouts |
Total Foreground Latch Waits |
Total Foreground Timeouts |
Total Foreground Waits |
Total Index Scans (per Second) |
Total Index Scans Per Transaction |
Total Latch Time Waited (Milliseconds) |
Total Latch Timeouts |
Total Latch Waits |
Total Parse Count (per Second) |
Total Parse Count Per Transaction |
Total Physical Read Rate (Bytes per Second) |
Total Physical Write Rate (Bytes per Second) |
Total Program Global Area Allocated (Bytes) |
Total Program Global Area Used by SQL Workareas (Bytes) |
Total Sorts Per User Call |
Total Table Scans (per Second) |
Total Table Scans Per Transaction |
Total Table Scans Per User Call |
Total Time Waited (Milliseconds) |
Total Timeouts |
Total Waits |
Transactions Per Logon |
Unblocked User Sessions |
Unknown Wait Time User Sessions |
User Calls (per Second) |
User Calls Per Transaction |
User Calls Ratio (%) |
User Commits (per Second) |
User Commits Ratio (%) |
User Limit (%) |
User Rollback Undo Records Applied (per Second) |
User Rollback Undo Records Applied Per Transaction |
User Rollbacks (per Second) |
User Rollbacks Ratio (%) |
User Transactions (per Second) |
User Types |
Version |
Waiting User Sessions |
Workload Capture and Replay Status |
Write Time (Milliseconds) |
Memory Advice Entry
Name |
---|
Database ID |
Estimated Database Time |
Global Name |
Memory Size (Megabytes) |
Memory Size Factor |
O/S User
Name |
---|
Active User Sessions |
Applications |
Background Types |
Blocked by Unknown User Sessions |
Blocked User Sessions |
Cached User Sessions |
Database ID |
Global Name |
Inactive User Sessions |
Instance ID |
Killed User Sessions |
Non-Waiting User Sessions |
O/S Client User |
O/S Machines |
Sessions Count |
Sniped User Sessions |
Unblocked User Sessions |
Unknown Wait Time User Sessions |
User Types |
Waiting User Sessions |
Parameter
Name |
---|
Database ID |
Global Name |
Instance ID |
Parameter Description |
Parameter has been Modified after Instance Startup |
Parameter has not been Modified by Configuration File |
Parameter Value |
Parameter Value Type |
Paramter Name |
PGA Advice Entry
Name |
---|
Database ID |
Estimated Extra Read Write Data (Bytes) |
Estimated Program Global Area Cache Hit Rate (%) |
Estimated Program Global Area Memory Over-Allocations |
Global Name |
Program Global Area Aggregate Target (Bytes) |
Program Global Area Target Factor |
Query
Name |
---|
Application Wait Time (Microseconds) |
Average Time (Microseconds) |
Buffer Gets |
Child Count |
Concurrency Wait Time (Microseconds) |
CPU Time (Microseconds) |
Database ID |
Direct Writes |
Disk Reads |
Elapsed Time (Microseconds) |
Executions |
Global Name |
Instance ID |
I/O Interconnect Data (Bytes) |
Last Active Time |
Last Load Time |
Optimized Physical Read Requests |
Parse Calls |
Physical Read Data (Bytes) |
Physical Read Requests |
Physical Write Data (Bytes) |
Physical Write Requests |
Rank |
Rows Processed |
Runtime Memory Used (Bytes) |
Shareable Memory (Bytes) |
SQL ID |
SQL Text |
Total Locks |
Total Pins |
User I/O Wait Time (Microseconds) |
Users Executing |
Redo Log File
Name |
---|
Database ID |
Global Name |
Group Number |
Member Name |
Sequence Number |
Size (Bytes) |
Status |
Status Extended |
Thread Number |
Session
Name |
---|
Application |
Blocking Instance ID |
Blocking Session ID |
Blocking Session Status |
Client Port |
Database ID |
Final Blocking Instance ID |
Final Blocking Session ID |
Final Blocking Session Status |
Global Name |
Instance ID |
Lock Address |
Oracle User Name |
O/S Client User Name |
O/S Machine |
Schema Name |
Serial Number |
Session ID |
SQL Execution Start Time |
SQL Text |
Status |
Time Since Last Wait (Microseconds) |
Type |
Wait Event |
Wait State |
Wait Time (Microseconds) |
Wait Time Remaining (Microseconds) |
SGA Advice Entry
Name |
---|
Database ID |
Estimated Buffer Cache Size (Megabytes) |
Estimated Database Time |
Estimated Physical Reads |
Estimated Shared Pool Size (Megabytes) |
Global Name |
System Global Area Size (Megabytes) |
System Global Area Size Factor |
Shared Pool Advice Entry
Name |
---|
Database ID |
Estimated Elapsed Parse Time Saved (Seconds) |
Global Name |
Shared Pool Cache Size (Megabytes) |
Shared Pool Size Factor |
Tablespace
Name |
---|
Allocated Size (Bytes) |
Contents |
Database ID |
Free Space (Bytes) |
Global Name |
Status |
Tablespace Name |
Updated almost 5 years ago