Oracle Database Performance Metrics
Achieve in-depth visibility into critical performance metrics for your Oracle Database, encompassing pluggable databases (PDB), tablespaces, response times, hit ratios, redo logs, Program Global Area (PGA) details, System Global Area (SGA) details, Automatic Storage Management (ASM) diskgroup details—all accessible through a unified and comprehensive console.
Receive immediate alerts via the Site24x7 Server Monitoring agent whenever predefined thresholds are breached. Proactively automate remedial actions to prevent outages and ensure uninterrupted operations.
Supported metrics
Overview
| Metric | Description | Unit |
| SQL Service Response Time | The average elapsed time per execution of a representative set of SQL statements, relative to a baseline | Milliseconds |
| Number of Sessions | The total number of sessions in the last data collection interval | Count |
| Buffer Cache Hit Ratio | The percentage of blocks found in the buffer cache without having to read from the disk | Percentage |
| PDB Count | The number of user-created PDBs belonging to a given application root or CDB$ROOT | Count |
| Uptime | The time since the instance started | Days, hours, minutes, or seconds |
| Version | The version of the database | Text |
| Database Type | The type of the database | Text |
| Database Role | The current role of the database; possible values include SNAPSHOT STANDBY, LOGICAL STANDBY, PHYSICAL STANDBY, PRIMARY, and FAR SYNC | Text |
| Open Mode | The open mode information | Text |
| Total Size | The total size of the PDB | Bytes |
| Block Size | The current block size for the PDB | Bytes |
| Average Active Sessions | The average number of active sessions | Count |
| Total Sessions | The total number of sessions | Count |
| Buffer Cache Hit Ratio | The percentage of blocks found in the buffer cache without having to read from the disk | Percentage |
| Data Buffer Hit Ratio | The current ratio of buffer cache hits to total requests | Percentage |
| Data Dictionary Hit Ratio | The ratio of logical reads to physical disk reads | Percentage |
| Library Cache Hit Ratio | The ratio when a pin hit occurs when the SQL or Procedural Language for SQL (PL/SQL) code you wish to execute is already in the library cache and is valid to execute. A low library cache hit percentage indicates either the shared pool size is small or an unsharable SQL is being used | Percentage |
Performance
| Metric | Description | Unit |
| Long Table Scans | The number of long table scans placed per second | Count |
| User Rollbacks | The number of rollbacks done by the user per second | Count |
| Disk Sort per Second | The number of sorts going to the disk per second | Count |
| Memory Sorts Ratio | The sort efficiency as measured by the percentage of times sorts were performed in memory as opposed to going to the disk | Percentage |
| Rows per Sort | The average number of rows per sort for all types of sorts performed | Count |
| Total Sorts per User Call | The total number of sorts placed per user call | Count |
| Hard Parse Count per Second | A hard parse occurs when an SQL statement has to be loaded into the shared pool. This indicates the number of hard parses per second | Count |
| Hard Parse Count per Transaction | The number of hard parses that occur per transaction | Count |
| Parse Failure Count per Second | The total number of queries failed to parse per second | Count |
| Parse Failure Count per Transaction | The total number of queries failed to parse per transaction | Count |
| Total Parse Count per Second | The total number of queries parsed per second | Count |
| Total Parse Count per Transaction | The total number of queries parsed per transaction | Count |
| Invalid Objects Count | The number of unused or invalid objects | Count |
| Unused Indexes Count | The number of unused or invalid indexes | Count |
| Disk Sort Ratio | The percentage of sorts that were performed on disk rather than in memory | Percentage |
| Currently Busy Dispatchers Count | The number of dispatcher processes that are currently busy handling user requests | Count |
| Recursive Calls Ratio | The ratio of recursive calls, generated internally by Oracle to manage the data dictionary and execute SQL, to total calls | Percentage |
| Database Corrupt Blocks Count | The number of data blocks in the database that are currently marked as corrupt | Count |
| Control File Status Invalid count | The number of control files whose status is reported as invalid | Count |
| Admin Failed Requests Count | The number of administrative requests that have failed | Count |
| Transaction Errors Count | The number of errors encountered while processing transactions | Count |
| Indoubt Transaction Count | The number of distributed transactions left in an in-doubt state, typically due to an interrupted two-phase commit | Count |
| Unanalyzed Tables Count | The number of tables that have not been analyzed and therefore have no current optimizer statistics | Count |
| Materialized Views Refresh Broken Count | The number of materialized views whose automatic refresh is broken | Count |
| Stored Objects Error Count | The number of stored objects, such as procedures, functions, and packages, that are in an error state | Count |
| Disabled Constraints Count | The number of constraints that are currently disabled | Count |
| Foreign Objects Count | The number of objects owned by a foreign or unexpected schema | Count |
| Free Cursors Percentage | The percentage of cursors in the shared pool that are currently free | Percentage |
| Free Cursors Count | The number of cursors in the shared pool that are currently free | Count |
| Open Cursors Count | The number of cursors currently open across all sessions | Count |
| Active Connection Ratio | The ratio of active connections to the total number of connections | Percentage |
| Enqueue Timeouts | The number of enqueue (lock) requests that timed out before the lock could be obtained | Count |
| Global Cache Blocks Corrupt | The number of blocks that were found to be corrupt in the global cache (Real Application Clusters) | Count |
| Global Cache Blocks Lost | The number of global cache blocks lost over the interconnect (Real Application Clusters) | Count |
PGA
| Metric | Description | Unit |
| Total PGA in Use | This indicates how much PGA memory is currently consumed by the work areas | KB, MB, or GB |
| Total PGA Allocated | The current amount of PGA memory allocated by the instance | KB, MB, or GB |
| Cache Hit Percentage | This metric is computed by Oracle Database to reflect the performance of the PGA memory component, cumulative since instance startup. 100% means optimal PGA memory usage | Percentage |
| PGA Memory Freed Back to OS | The number of bytes of PGA memory freed back to the operating system | KB, MB, or GB |
ASM
| Metric | Description | Unit |
| Name | The name of the disk group | Text |
| Free Size | The unused capacity of the disk group | MB |
| Total Size | The total capacity of the disk group, excluding that of quorum disks (in megabytes) | MB |
| Used Size | The used capacity of the disk group | MB |
| Used Percentage | The used percentage of the disk group | Percentage |
| Free Percentage | The unused percentage of the disk group | Percentage |
| Type | The redundancy type for the disk group | Text |
| State | The state of the disk group relative to the instance | Text |
| Group Number | The cluster-wide number assigned to the disk group | Number |
| Reads | The total number of I/O read requests for the disk group | Count |
| Writes | The total number of I/O write requests for the disk group | Count |
| Avg Read Time | The time taken to perform a read request in the disk group if the TIMED_STATISTICS initialization parameter is set to TRUE (0 if set to FALSE) | Seconds |
| Avg Write Time | The time taken to perform a write request in the disk group if the TIMED_STATISTICS initialization parameter is set to TRUE (0 if set to FALSE) | Seconds |
| Bytes Read | The total number of bytes read from the disk group | Bytes |
| Bytes Written | The total number of bytes written from the disk group | Bytes |
| ASM Disk Count | The number of disks in the disk group | Count |
SGA
| Metric | Description | Unit |
| Redo Buffers | The amount of redo entries that contain the information necessary to reconstruct or redo changes made to the database by insert, update, delete, create, alter, or drop operations | KB, MB, or GB |
| Buffer Cache Size | The area of the SGA used to hold copies of data blocks read from the disk | KB, MB, or GB |
| In Memory Area Size | The amount of memory allocated from the in-memory area | KB, MB, or GB |
| Shared Pool Size | The size of the area devoted to shared SQL and PL/SQL statements | KB, MB, or GB |
| Large Pool Size | The amount of memory allocated from the large pool | KB, MB, or GB |
| Java Pool Size | The amount of memory allocated from the Java pool | KB, MB, or GB |
| Streams Pool Size | The amount of memory allocated from the streams pool | KB, MB, or GB |
| Shared IO Pool Size | The amount of memory allocated from the shared IO pool | KB, MB, or GB |
| Fixed SGA Size | The fixed SGA size is computed when the Oracle is compiled | KB, MB, or GB |
| SQL Area | The size of the SQL area in the shared pool | KB, MB, or GB |
| Library Cache | The size of the library cache in the shared pool | KB, MB, or GB |
| Dictionary Cache | The size of the dictionary cache in the shared pool | KB, MB, or GB |
| Max Shared Pool | The maximum size of the shared pool | KB, MB, or GB |
| Max Large Pool | The maximum size of the large pool | KB, MB, or GB |
| Max Java Pool | The maximum size of the Java pool | KB, MB, or GB |
| Max Streams Pool | The maximum size of the streams pool | KB, MB, or GB |
| Default Buffer Cache Max | The maximum size of the DEFAULT buffer cache | KB, MB, or GB |
| Shared IO Pool Max | The maximum size of the shared IO pool | KB, MB, or GB |
| Physical Reads per Sec | The number of disk reads per second | Count |
| Physical Writes per Sec | The number of disk writes per second | Count |
| Redo Log Files Count | The number of redo log files | Count |
Tablespace
| Metric | Description | Unit |
| Status | The status of the tablespace | Text |
| Name | The name of the tablespace | Text |
| Total | The total size of the tablespace | KB, MB, or GB |
| Used | The used space of the tablespace | KB, MB, or GB |
| Free | The free space in the tablespace | KB, MB, or GB |
| Used Percentage | The percentage of tablespace used | Percentage |
| Free Percentage | The percentage of space available in the tablespace | Percentage |
Processes
| Metric | Description | Unit |
| PName | The name of this process | Text |
| PGA Used Memory | The PGA memory currently used by the process | KB, MB, or GB |
| PGA Allocated Memory | The PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process) | KB, MB, or GB |
| PGA Freeable Memory | The allocated PGA memory that can be freed | Bytes |
| PGA Max Memory | The maximum PGA memory ever allocated by the process | KB, MB, or GB |
| Max Processes Count | Maximum number of processes active at one time since instance startup | Count |
| Redo Log Files Count | The number of redo log files | Count |
Configuration
Instance details
| Metric | Description | Unit |
| Instance Number | The number of the instance | Text |
| Instance Name | The name of the instance | Text |
| Host Name | The name of the host machine | Text |
| Version | The version of the database | Text |
| Startup Time | The time when the instance was started | Text |
| Status | The status of the instance. Possible values are STARTED, MOUNTED, OPEN, and OPEN MIGRATE | Text |
| Parallel | This indicates whether the instance is mounted in the cluster database mode (YES) or not (NO) | Text |
| Redo Thread Count | The number of redo threads opened by the instance | Count |
| Archiving Status | The automatic archiving status. Possible values are STOPPED, STARTED, or FAILED | Text |
| Log Switch Wait | The event that log switching is waiting for | Text |
| Log Ins | This indicates whether the instance is in unrestricted mode, allowing logins by all users, or in restricted mode, allowing logins by database administrators only | Text |
| Shutdown Pending | This indicates whether a shutdown is pending or not | Text |
| Database Status | The status of the database: possible values are ACTIVE, SUSPENDED, or INSTANCE RECOVERY | Text |
| Instance Role | This indicates whether the instance is an active instance, an inactive secondary instance, or unknown, if the instance has been started but not mounted. Possible values are PRIMARY_INSTANCE, SECONDARY_INSTANCE, or UNKNOWN | Text |
| Active State | The quiesce (activity) state of the instance: possible values are NORMAL, QUIESCING, or QUIESCED | Text |
| Blocked Services | This indicates whether all services are blocked or not | Text |
| Container ID | The ID of the container to which the data pertains | Text |
| Instance Mode | The mode of the Oracle Database instance: possible values are REGULAR, READ MOSTLY, or READ ONLY | Text |
| Edition | The edition of the database | Text |
| Max Processes | The maximum number of processes that have been concurrently active since the instance started | Count |
| Max Cursors | The maximum number of cursors that have been concurrently open since the instance started | Count |
CDB details
| Metric | Description | Unit |
| Name | The name of the container | Text |
| Open Mode | The open mode information | Text |
| Open Time | The date and time when the database was last opened | Date/time (timestamp) |
| Total Size | The total size of the PDB | Bytes |
| Block Size | The current block size for the PDB | Bytes |
| Recovery Status | The status shows whether recovery is enabled or disabled for the PDB | Text |
| Snapshot Parent Con ID | This shows the container ID of the master PDB that this PDB is a snapshot clone of | Number |
| Application Root | This indicates whether the PDB is an application root | Text |
| Application PDB | This indicates whether the PDB is an application PDB | Text |
| Application Seed | This indicates whether the PDB is an application seed (an application seed is also an application PDB) | Text |
| Application Root Con ID | If this PDB is an application PDB, this value indicates the container ID of an application root to which this application PDB belongs | Number |
| Application Root Clone | This indicates whether this PDB is an application root clone | Text |
| Proxy PDB | This indicates whether this PDB is a proxy PDB (YES) or not (NO) | Text |
| Undo Mode | This indicates whether the PDB is in local undo mode | Text |
| Creation Time | The date and time at which the PDB was created | Date |
| PDB Count | The number of user-created PDBs belonging to a given application root or CDB$ROOT | Count |
| Member CDB | This indicates whether the row corresponds to the Member CDB part of the CDB Fleet | Text |
| Restricted | This indicates whether only users possessing Restricted session privileges can connect to the PDB | Text |
Physical database details
| Metric | Description | Unit |
| DB ID | The ID of the database | Number |
| Created Date | The creation date of the database | Date |
| Flashback On | The state if the flashback is on, off, or at a restore point | Text |
| Log Mode | The log mode of the database: possible values include NOARCHIVELOG, ARCHIVELOG, and MANUAL | Text |
| Control File Type | The type of control file | Text |
| Switch Over Status | The number assigned to the database switchover | Text |
| Protection Mode | The protection mode currently in effect for the database: possible values include MAXIMUM PROTECTION, MAXIMUM AVAILABILITY, RESYNCHRONIZATION, MAXIMUM PERFORMANCE, and UNPROTECTED | Text |
| Open Reset Logs | This indicates whether the next database open allows or requires the reset logs option | Text |
| Guard Status | This indicates the protection status of the data | Text |
| Force Logging | This indicates the type of logging mode that is currently in force | Text |
| Current SCN | The current System Change Number (SCN) of the database | Number |
Redo log details
| Metric | Description | Unit |
| Redo Buffers | The amount of memory in the SGA used for redo log buffers | KB, MB, or GB |
| Redo Log Files | The number of redo log files configured for the database | Count |
| Redo Log File Status Invalid Count | The number of redo log files whose status is reported as invalid | Count |
FRA details
| Metric | Description | Unit |
| FRA Used Space | The amount of space currently used in the fast recovery area | KB, MB, or GB |
| FRA Free Space | The amount of free space available in the fast recovery area | KB, MB, or GB |
| FRA Used Percentage | The percentage of the fast recovery area that is currently used | Percentage |
| FRA Free Space Percentage | The percentage of the fast recovery area that is currently free | Percentage |
Oracle PDB
| Metric | Description | Unit |
| Database Name | The name of the PDB | Text |
| Container ID | The ID of the container to which the pluggable database belongs | Text |
| Open Mode | The open mode of the pluggable database, such as READ WRITE, READ ONLY, or MOUNTED | Text |
| Restricted | This indicates whether the pluggable database is in restricted mode (YES) or not (NO) | Text |
| Recovery Status | The recovery status of the pluggable database | Text |
| Total Size | The total size of the pluggable database | KB, MB, or GB |
| Status | The status of the pluggable database | Text |
Listener details
| Metric | Description | Unit |
| Listener Name | The name of the Oracle listener | Text |
| Port | The port on which the listener is accepting connections | Number |
| Version | The version of the listener | Text |
| Start Date | The date and time when the listener was started | Text |
| Security Status | The security status of the listener | Text |
| Port Binding Status | This indicates whether the listener is successfully bound to its configured port | Text |
| Oracle Listener Home Path | The home directory path of the Oracle listener | Text |
| Endpoints | The endpoints (protocol addresses) on which the listener is listening | Text |
| Uptime | The duration for which the listener has been running | Text |
Listener performance details
| Metric | Description | Unit |
| Listener Name | The name of the Oracle listener | Text |
| Port | The port on which the listener is accepting connections | Number |
| Memory Usage | The amount of memory consumed by the listener process | KB, MB, or GB |
| CPU Usage | The percentage of CPU consumed by the listener process | Percentage |
| Status | The status of the listener | Text |
| Error Message | The error message reported for the listener, if any | Text |
Listener service details
| Metric | Description | Unit |
| Listener Name | The name of the Oracle listener | Text |
| Instance Name | The name of the instance to which the service belongs | Text |
| Service Name | The name of the service registered with the listener | Text |
| Established Connections Count | The number of connections currently established for the service | Count |
| Refused Connection Count | The number of connection attempts that were refused for the service | Count |
| Active Connections | The number of connections currently active for the service | Count |
| Max Connections Allowed | The maximum number of connections allowed for the service | Count |
| State | The state of the service | Text |
| tnsping Status | The result of the tnsping check used to verify listener reachability | Text |
| tnsping Time | The time taken by the tnsping check to receive a response | milliseconds |
| Status | The status of the service | Text |
| Error Message | The error message reported for the service, if any | Text |
Archive log destination details
| Metric | Description | Unit |
| Dest ID | The identifier of the archive log destination | Number |
| Database Name | The name of the database associated with the destination | Text |
| Destination | The location to which the archived redo log files are written | Text |
| Status | The status of the archive log destination | Text |
| Type | The type of the archive log destination | Text |
| Protection Mode | The data protection mode in force for the destination | Text |
| Transmit Mode | The mode used to transmit redo data to the destination | Text |
| Recovery Mode | The recovery mode of the destination | Text |
| Affirm | This indicates whether the destination uses the AFFIRM (synchronous) or NOAFFIRM redo transport attribute | Text |
Archive log destination stats
| Metric | Description | Unit |
| Dest ID | The identifier of the archive log destination | Number |
| Redo Log Sequence Being Processed | The sequence number of the redo log currently being processed | Number |
| Network Timeout | The network timeout configured for the destination | seconds |
| Number of Failed Redo Log | The number of redo logs that failed to be transmitted or archived | Count |
| Last Archived Redo Sequence | The sequence number of the last redo log that was archived | Number |
| Last Redo Sequence Applied on Standby | The sequence number of the last redo log applied on the standby database | Number |
| Delay in Minutes | The configured delay before archived redo logs are applied on the standby | minutes |
| Error Message | The error message reported for the destination, if any | Text |
Log Apply Gap Details
| Metric | Description | Unit |
| Dest ID | The identifier of the archive log destination | Number |
| Thread ID | The redo thread identifier | Number |
| Gap to Primary | The redo gap between the standby and the primary database | Count |
| Gap Applied | The amount of redo gap that has been applied to the standby | Count |
| Gap Receieved | The amount of redo gap that has been received by the standby | Count |
| Gap Last Received Sequence | The sequence number of the last redo log received by the standby | Number |
| Gap Last Applied Sequence | The sequence number of the last redo log applied on the standby | Number |
Query Monitor
| Metric | Description | Unit |
| Query Monitor | The name of the configured query monitor | Text |
| Template | The query template used by the query monitor | Text |
| Status | The status of the query monitor | Text |
| Execution Time | The time taken to execute the monitored query | seconds |
| Query Result Set Count | The number of rows returned in the query result set | Count |
Data file metrics
These are the detailed metrics for each Data File across all tablespaces in the database. These metrics track file status, capacity, utilization, and I/O performance.
| Metric | Description | Unit |
| Container ID | Identifier of the container (CDB root or specific PDB) to which the Data File belongs. | Number |
| PDB Name | Name of the Pluggable Database (PDB) the Data File belongs to. | Text |
| File ID | Unique identifier of the Data File within the container. | Number |
| File Name | Full physical path of the Data File on disk. | Text |
| Tablespace Name | Name of the tablespace to which the Data File belongs. | Text |
| File Status | Dictionary-level status of the Data File. Reflects the status recorded in the data dictionary. Possible values: AVAILABLE or INVALID. | Text |
| Online Status | Runtime status of the Data File. Reflects the current operational state as seen by the database instance. Possible values: OFFLINE, ONLINE, SYSTEM, RECOVER, and SYSOFF. | Text |
| Enabled | Indicates whether the Data File is enabled for read/write operations. Possible values:
|
Text |
| Bytes | Current allocated size of the Data File in bytes. | Bytes |
| Max Bytes | Maximum size the Data File can grow to if autoextensible is enabled. | Bytes |
| Autoextensible | Indicates whether the Data File can automatically grow to accommodate more data (YES/NO). | Text |
| Used Bytes | Space currently used by segments in the Data File, calculated as (BYTES - FREE_BYTES). | Bytes |
| Free Bytes | Free (unallocated) space available inside the Data File for new segments. | Bytes |
| Used Percent | Percentage of the Data File currently used by allocated segments. High values approaching 100% indicate the Data File is nearly full. | Percentage |
| Free Percent | Available percentage of the Data File space for future usage. Low values may indicate the need to resize the Data File or allocate more space. | Percentage |
| Reads | Number of physical read I/O operations performed on this Data File during the collection interval. | Count |
| Writes | Number of physical write I/O operations performed on this Data File during the collection interval. | Count |
| Average Read Time | Average time per physical read operation on this Data File, typically measured in milliseconds. High values may indicate I/O subsystem latency or contention. | Milliseconds |
| Average Write Time | Average time per physical write operation on this Data File, typically measured in milliseconds. Elevated write times may impact transaction commit latency. | Milliseconds |
Data file status count
These are aggregated counts of Data Files by status within each container (PDB or CDB). These metrics are included in the data for each PDB as well as the CDB root.
| Metric | Description | Unit |
| Total Count | Total number of Data Files present in the container. | Count |
| Online Count | Number of Data Files with ONLINE status in the container. These Data Files are currently accessible and in use. | Count |
| Offline Count | Number of Data Files with OFFLINE status in the container. These Data Files are not currently accessible. | Count |
| System Count | Number of Data Files with SYSTEM status in the container (typically SYSTEM and SYSAUX tablespaces). | Count |
| Recover Count | Number of Data Files with RECOVER status in the container. These Data Files may require recovery. | Count |
| SYSOFF Count | Number of Data Files with SYSOFF status in the container (offline system Data Files). | Count |
Note
Monitor Offline Count and Recover Count closely. Any Data Files in these states may impact database availability and should be investigated and resolved promptly. Set up alerts to notify when these counts exceed zero.
Host metrics
These are system-level resource utilization metrics as observed by the Oracle database instance.
| Metric | Description | Unit |
| CPU Utilization | Current host CPU utilization percentage. | Percentage |
| Memory Percent | Memory used percentage calculated from total and free memory available on the host. | Percentage |
Alert log metrics
These are error and alert messages from the Oracle alert log. Alert log data for Oracle 12c and later is collected using the v$DIAG_ALERT_EXT view. For versions above 11g but below 12c, a custom view v$site24x7_alertlog_ext is required since the underlying x$dbgalertext view is accessible only to SYSDBA users.
Alert log field descriptions
| Field name | Description | Unit |
| Message Level | The severity level of the alert message. Lower level values imply higher severity. Possible values:
|
Text |
| Record ID | Unique record number for the alert message within the alert log. | Number |
| Timestamp | Date and time when the alert message was generated. | Milliseconds |
| Host ID | DNS hostname of the originating host where the alert was generated. | Text |
| Host Address | IP address or other network address of the originating host for the message. | Text |
| Message Text | Fully formed and localized text of the alert message, including detailed error descriptions and context. | Text |
| Container ID | Container ID in which the error or alert was generated (in a CDB, identifies the specific PDB). | Number |
| Container Name | Name of the container (PDB or CDB) in which the error or alert was generated. | Text |
| ORA Error | All Oracle error codes associated with the alert, concatenated with commas (e.g., ORA-00800,ORA-001700). Helps identify the specific errors triggered by this alert. | Text |
Alert log permissions
Alert log collection requires appropriate database permissions depending on your Oracle version:
For Oracle 11g instances:
Create or replace view v_$site24x7_alertlog_ext as select * from x$dbgalertext; Create or replace public synonym v$site24x7_alertlog_ext for sys.v_$site24x7_alertlog_ext; Grant select on v$site24x7_alertlog_ext to site24x7;
For multitenant instances (12c and later):
Grant select on v$diag_alert_ext to C##SITE24X7;
Tip
Filter alert logs by Message Level to focus on critical and severe messages (levels 1 and 2). Set up alerts to notify when CRITICAL-level messages appear in the alert log, enabling rapid response to database issues.
Related links
- Database monitoring: Oracle Database | Oracle PDB | PostgreSQL | MySQL | Microsoft SQL | MySQL NDB Clusters
- Server monitoring: Linux