Oracle Insights monitoring
Oracle Insights extends the standard Oracle Database monitor with deeper, query-level and session-level intelligence. While the Oracle Database monitor tracks instance health and performance counters, Oracle Insights surfaces the information you need to diagnose slow queries, investigate session wait events, monitor scheduled job health, track data file and redo log states, and audit backup completeness—all from within Site24x7.
Overview
Oracle Insights is auto-discovered when the parent Oracle Database monitor is set up on the same server. It collects data from Oracle's dynamic performance views (V$ and DBA_ views) and provides the following additional capabilities over the standard Oracle Database monitor:
- Top query analysis: Identifies the slowest and most resource-intensive SQL statements by execution time, CPU time, I/O wait time, buffer gets, and physical reads.
- Query activity breakdown: Tracks the volume of
SELECT,INSERT,UPDATE, andDELETEstatements executed over time. - Session wait event tracking: Counts sessions currently blocked by specific wait events such as buffer busy waits, latch contention, log file sync, and library cache contention.
- Scheduled job health: Monitors Oracle DBMS Scheduler jobs for failures, broken jobs, run counts, and next scheduled execution.
- Data file and redo log visibility: Tracks the total number of data files and flags any that are not online, and monitors the redo log file counts.
- Backup health: Monitors the status, duration, size, age, and throughput of the most recent Oracle RMAN backup for each backup type.
Prerequisites
- The Oracle Database monitor must already be configured for the same instance on the same server.
- Site24x7 Linux agent 22.2.00 or above installed on the Oracle Database server. Remote monitoring is supported from a connected server with a Linux Full-Stack agent installed.
- The database user configured in the Oracle Database monitor must have
SELECTaccess on the relevantV$, DBA_SCHEDULER_JOBS, V$DATAFILE, V$LOG, and V$RMAN_BACKUP_JOB_DETAILSviews.
Oracle Insights is auto-discovered and added for monitoring when the parent Oracle Database monitor is set up. No separate add-monitor workflow is required. If the Insights monitor is not detected, trigger application discovery from the server monitor: go to Server Monitor > [specific server] > click the hamburger icon beside the display name > click Discover Applications.
Navigate to the Oracle Insights monitor
- Log in to Site24x7 and go to Database in the left navigation pane.
- Select Oracle from the database list and click the Oracle Database monitor for the instance you want to inspect.
- Click the Insights tab or select the Oracle Insights child monitor from the monitor list to view the detailed Insights page.
Instance metadata
The following instance-level attributes are collected on change and displayed in the Metadata tab.
| Attribute | Description |
| Instance Name | The Oracle SID (System Identifier) name of the monitored instance. |
| Hostname | The hostname of the server on which the Oracle instance is running. |
| Version | The Oracle Database software version of this instance. |
| Edition | The Oracle Database edition (for example, Enterprise Edition, Standard Edition). |
| Instance Role | The role of the instance, such as PRIMARY or STANDBY (relevant in Data Guard configurations). |
| Database Type | The database type, such as SINGLE, RAC, or RACONENODE. |
Top query metrics
These metrics identify the most resource-intensive SQL statements currently executing or recently executed on the database. They are collected every poll and help pinpoint queries causing performance degradation.
| Metric | Description | Unit |
| Max Average Execution Time | The highest average elapsed execution time observed across all tracked SQL statements. A high value identifies queries with significant overall latency. | Milliseconds |
| Max Average CPU Time | The highest average CPU time consumed per execution across all tracked SQL statements. Useful for identifying CPU-intensive queries that may be straining the server. | Milliseconds |
| Max Average User I/O Wait Time | The highest average time a query spent waiting for user I/O operations (disk reads/writes) per execution. A high value indicates I/O-bound queries that may benefit from indexing or tablespace tuning. | Milliseconds |
| Max Average Buffer Gets Per Execution | The highest average number of logical reads (buffer cache accesses) per execution across all tracked SQL statements. High values indicate queries performing full table scans or accessing large result sets. | Count |
| Max Average Reads Per Execution | The highest average number of physical disk reads per execution across all tracked SQL statements. High values indicate queries that cannot be served from the buffer cache and are hitting disk. | Count |
Query activity
Query activity metrics track the volume of DML and query operations executed on the database during each collection period, providing a workload profile of the instance.
| Metric | Description | Unit |
| Total Queries Executed | The total number of SQL statements executed on the instance during the collection period, across all statement types. | Count |
| SELECT Count | The number of SELECT statements executed during the collection period. | Count |
| INSERT Count | The number of INSERT statements executed during the collection period. | Count |
| UPDATE Count | The number of UPDATE statements executed during the collection period. | Count |
| DELETE Count | The number of DELETE statements executed during the collection period. | Count |
Session overview
Session overview metrics provide a snapshot of all Oracle sessions by their status, collected every poll.
| Metric | Description | Unit |
| Active Session Count | The number of sessions currently in an ACTIVE state—executing a SQL statement or performing database work. | Count |
| Inactive Session Count | The number of sessions in an INACTIVE state—connected to the database but not currently executing any statement. | Count |
| Killed Session Count | The number of sessions that have been marked as KILLED—either by a DBA or by Oracle due to an error—and are in the process of being terminated. | Count |
| Cached Session Count | The number of sessions in a CACHED state—inactive sessions that are being held in the session cache by a connection pool for potential reuse. | Count |
| Sniped Session Count | The number of sessions in a SNIPED state—inactive sessions that have exceeded the idle time limit defined in a profile and are waiting to be cleaned up. | Count |
| Active Users | The number of distinct database users with at least one active session on the instance. | Count |
Session wait events
These metrics count the number of sessions currently waiting on specific Oracle wait events. Non-zero values indicate contention on the corresponding resource and should be investigated when they are sustained or spike unexpectedly.
| Wait Event | Description | Unit |
| Sessions Waiting | The total number of sessions currently in a wait state across all wait events. | Count |
| Sessions Suspended | The number of sessions currently suspended, typically due to a space allocation issue such as a full tablespace or rollback segment. | Count |
| Buffer Busy Waits | Sessions waiting for a buffer in the buffer cache that is being read or modified by another session. Indicates buffer contention. | Count |
| Buffer Latch | Sessions waiting to acquire a latch protecting a buffer cache structure. Indicates high concurrency on buffer cache internals. | Count |
| Cursor: Pin S Wait on X | Sessions waiting for a shared pin on a cursor that another session holds exclusively. Indicates library cache contention, often caused by excessive hard parses or non-sharable SQL. | Count |
| DB File Scattered Read | Sessions waiting for a multi-block physical read to complete, typically associated with full table scans or fast full index scans. High values suggest missing indexes or large sequential scan workloads. | Count |
| Free Buffer Waits | Sessions waiting for a free buffer in the buffer cache. Indicates the buffer cache is full and the database writer (DBWR) cannot keep up with demand—may require buffer cache sizing or DBWR tuning. | Count |
| Latch: Enqueue Hash Chains | Sessions waiting for the latch protecting the enqueue hash chain structures, which manage row-level lock queues. Sustained values indicate high lock contention. | Count |
| Latch Free | Sessions waiting for any miscellaneous latch. A catch-all wait event indicating general latch contention not covered by a specific latch wait event. | Count |
| Latch: Row Cache Objects | Sessions waiting for the latch protecting row cache (data dictionary cache) structures. Indicates dictionary cache contention, often caused by an undersized shared pool. | Count |
| Library Cache Lock | Sessions waiting for a lock on a library cache object (such as a package or procedure). Indicates DDL contention or an object being compiled while sessions are trying to execute it. | Count |
| Library Cache Mutex X | Sessions waiting for exclusive access to a library cache mutex. Indicates heavy concurrent parsing or cursor invalidation activity. | Count |
| Library Cache Pin | Sessions waiting for a pin on a library cache object while another session is modifying it (for example, during a recompile). Usually indicates DDL activity interfering with DML execution. | Count |
| Log File Sync | Sessions waiting for the log writer (LGWR) to flush the redo log buffer to disk, typically at COMMIT time. High values indicate I/O latency on redo log devices or very high commit rates. | Count |
| Row Cache Lock | Sessions waiting for a lock on a row in the data dictionary row cache. Usually associated with high DDL activity or concurrent object creation. | Count |
Scheduled jobs
Oracle Insights monitors Oracle DBMS Scheduler jobs and provides both summary counts and per-job details.
Summary metrics (collected every poll)
| Metric | Description | Unit |
| Scheduler Jobs Count | The total number of DBMS Scheduler jobs defined in the database. | Count |
| Failed Jobs | The number of scheduler jobs that have failed since the last reset. Non-zero values warrant investigation of the job's error log. | Count |
| Broken Jobs | The number of scheduler jobs currently marked as broken—jobs that have failed repeatedly and have been disabled by Oracle to prevent further attempts. | Count |
Per-job details (collected every poll for each discovered job)
| Field | Description | |
| Job Name | The name of the DBMS Scheduler job. | |
| Job Owner | The database schema that owns this scheduled job. | |
| Job Type | The type of work the job performs (for example, PLSQL_BLOCK, STORED_PROCEDURE, or EXECUTABLE). | |
| Job State | The current operational state of the job (for example, SCHEDULED, RUNNING, SUCCEEDED, FAILED, or DISABLED). | |
| Enabled | Indicates whether the job is currently enabled for execution. | |
| Last Run Status | The outcome of the most recent execution of this job (for example, SUCCEEDED or FAILED). | |
| Last Error Code | The Oracle error code from the most recent failed execution, if applicable. | |
| Last Start Date | The date and time the job most recently began execution. | |
| Last Run Duration | The elapsed time (in seconds) of the most recent job execution. | |
| Next Run Date | The scheduled date and time for the next execution of this job. | |
| Repeat Interval | The repeat interval expression that defines how frequently this job is scheduled to run. | |
| Schedule Type | The type of scheduling used for this job (for example, ONCE, CALENDAR, or NAMED). | |
| Run Count | The total number of times this job has been executed since it was created. | |
| Failure Count | The total number of times this job has failed since it was created. | |
| Broken | Indicates whether this job has been marked as broken by Oracle due to repeated failures. |
Data files and redo logs
These metrics track the health of Oracle's physical storage structures, collected every poll.
| Metric | Description | Unit |
| Data Files (Total) | The total number of data files currently belonging to this database. This includes files in all tablespaces. | Count |
| Data Files Not Online | The number of data files whose status is not ONLINE (for example, OFFLINE, RECOVER, or SYSOFF). Any non-zero value indicates a data file requiring attention, as affected tablespaces may be partially or fully unavailable. | Count |
| Redo Log Files (Total) | The total number of redo log file members across all redo log groups. Monitoring this count helps detect unexpected changes to the redo log configuration. | Count |
Backup monitoring
Oracle Insights tracks the most recent RMAN backup for each backup type, providing per-backup status, timing, size, and throughput. The following metrics are collected for each backup record.
| Metric | Description | Unit |
| Backup Status | The outcome of the most recent backup for this backup type (for example, COMPLETED, FAILED, RUNNING, or COMPLETED WITH WARNINGS). | Text |
| Time Taken | The elapsed duration of the most recent backup run. | Milliseconds |
| Backup Size | The total size of the data written during the most recent backup. | KB |
| Backup Age | The time elapsed since the most recent backup was completed. A high value indicates the backup schedule may have missed a run. | Minutes |
| Throughput | The rate at which data was written during the most recent backup. Low throughput may indicate storage I/O contention during the backup window. | MB/sec |
In addition, the following backup alert flags are tracked as part of the main polling table.
| Metric | Description | Unit |
| Database Backup Alert | The number of database backups in an alert state (any backup type). | Count |
| Database Backup Pending | The number of database backups that are pending or overdue (any backup type). | Count |
| Incremental Backup Alert | The number of incremental backups in an alert state. | Count |
| Incremental Backup Pending | The number of incremental backups that are pending or overdue. | Count |
| Full Backup Alert | The number of full backups in an alert state. | Count |
| Full Backup Pending | The number of full backups that are pending or overdue. | Count |
Setting up alerts
All performance metrics in the Oracle Insights monitor support threshold-based alerting and anomaly detection.
Global configuration
- In Site24x7, go to Admin > Configuration Profiles > Threshold and Availability (+).
- Click Add Threshold Profile and select Oracle Insights as the Monitor Type.
- Set threshold values for the metrics listed above.
Monitor-level configuration
- Navigate to the Oracle Insights monitor.
- Click the hamburger icon beside the display name, then select Edit.
- Configure threshold values using the Threshold and Availability option, then click Save.
IT Automation
Site24x7 IT Automation allows you to define automated remediation actions that trigger on alert events for this monitor—for example, invoking a script or webhook when a scheduled job fails, or a backup goes overdue.
Licensing
The Oracle Insights monitor consumes one advanced monitor license per instance. It is a child monitor of the Oracle Database monitor and does not consume an additional basic monitor license.