The Oracle RAC database metrics provide the following information for each metric:
Description
Metric summary. The metric summary can include some or all of the following: target version, evaluation frequency, collection frequency, upload frequency, operator, default warning threshold, default critical threshold, consecutive number of occurrences preceding notification, and alert text.
Multiple Thresholds (where applicable)
Data source
User action
The Data Guard metrics check the status, data not received, and data not applied for the databases in the Data Guard configuration.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Use the Data Guard Status metric to check the status of each database in the Data Guard configuration.
By default, a critical and warning threshold value was set for this metric column. Alerts will be generated when threshold values are reached. You can edit the value for a threshold as required.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-1 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
9.2.0.x; 10.1.0.x |
Every 5 Minutes |
After Every Sample |
CONTAINS |
Warning |
Error |
1 |
The Data Guard status of %dg_name% is %value%. |
Check the Edit Properties General page for the primary and standby databases for detailed information.
Examine the database alert logs and the Data Guard broker logs for additional information.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The broker computes the highest applied SCN and uses its value to find the last continuous log that was successfully archived to the standby database. Redo data in all subsequent log files are counted as logs not applied. If the primary database goes down at this point, the redo data from these log files can be applied on the standby database. If there is a gap in the log files received on the standby database, any log files received after the gap cannot be applied.
For example, if log files 1, 2, 3, 6, 7, and 9 are received on the standby database and log apply services is currently applying log 1, log apply services can continue to apply up to log 3. Log apply services cannot apply any more log files because log 4 is missing. Even though log files 6, 7, and 9 are received, they cannot be applied and they will not be counted as data not applied.
If all the archived log files on the standby database are continuous, and standby redo logs are used, the standby redo logs are also counted as data not applied, unless real-time apply is turned on and log apply services is already working on the standby redo log files.
If the standby redo logs are multithreaded, the broker computes the highest applied SCN for every thread and totals the numbers. If there are multiple incarnations and the standby database is in a different incarnation from the primary database, each incarnation is computed separately and the results are then totaled.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The broker computes the highest applied SCN and uses its value to find the last continuous log that was archived to the standby database. The size of redo data in all subsequent log files are counted as data not applied. If the primary database goes down at this point, redo from these log files can be applied on the standby database. If there is a gap in the log files received on the standby database, any log files received after the gap cannot be applied.
For example, if log files 1, 2, 3, 6, 7, and 9 are received on the standby database and log apply services is currently applying log 1, log apply services can continue to apply up to log 3. Log apply services cannot apply any more log files because log 4 is missing. Even though log files 6, 7, and 9 are received, they cannot be applied and they will not be counted as data not applied. In this case, the total size of log files 1, 2, and 3 is the size of Data Not Applied.
If all the archived log files on the standby database are continuous, and standby redo log files are used, the standby redo log files are also counted as data not applied, unless real-time apply is turned on and log apply services is already working on the standby redo log files. The size of an archived log file is its file size. However, the size of a standby redo log is the size of the actual redo in the log and not the file size.
If the standby redo log files are multithreaded, the broker computes the highest applied SCN for every thread and totals the numbers. If there are multiple incarnations and the standby database is in a different incarnation from the primary database, each incarnation is computed separately and the results are then totaled.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The broker computes the highest applied SCN and uses its value to find the last continuous log file that was successfully archived to the standby database. Redo data in all subsequent log files, including the current online redo log file, are counted as log files for potential data loss and will be unrecoverable if the primary database goes down at this point.
For example, if log files 1, 2, 3, 6, 7, and 9 are received on the standby database, and if log 10 is the current online log file, and if log apply services are currently applying log 1, the last continuous log after the highest applied SCN is log 3. All log files after log 3, that is log files 4 through 10, are counted as data not received. If the primary database goes down at this point, all redo data in log files 4 through 10 are lost on the standby database.
If the primary database is multithreaded (in a RAC database), the broker computes the highest applied SCN for every thread and totals the numbers. If the primary database has multiple incarnations (for example, due to a flashback operation) and the standby database is in a different incarnation from the primary database, the computation is done on each incarnation and the results are then totaled.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The broker computes the highest applied SCN and uses its value to find the last continuous log file that was successfully archived to the standby database. The size of redo data in all subsequent log files, including the current online redo log file, are counted as data for potential data loss and will be unrecoverable if the primary database goes down at this point. The size of an archived log file is its file size, and the size of the online redo log file is the size of the actual redo in the online log file, not the file size of the online redo log file.
For example, if log files 1, 2, 3, 6, 7, and 9 are received on the standby database, and if log 10 is the current online log file, and if log apply services is currently applying log 1, the last continuous log after the highest applied SCN is log 3. All log files after log 3, that is log files 4 through 10, are counted as data not received and the total size of redo data in these log files is the size of Data Not Received.
If the primary database is multithreaded (in a RAC database), the broker computes the highest applied SCN for every thread and totals the numbers. If the primary database has multiple incarnations (for example, due to a flashback operation) and the standby database is in a different incarnation from the primary database, the computation is done on each incarnation and the results are then totaled.
The metrics in this category are database-level metrics. For cluster databases, these metrics are monitored at the cluster database target level and not by member instances. The metrics are:
Data Guard Performance metrics
Displays (in seconds) how far the standby is behind the primary.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
v$dataguard_stats('apply lag')
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric shows the approximate number of seconds required to failover to this standby database. This accounts for the startup time, if necessary, plus the remaining time required to apply all the available redo on the standby. If a bounce is not required, it is only the remaining apply time.
v$dataguard_stats ('estimated startup time','apply finish time','standby has been open')
Displays the Redo Apply Rate in KB/second on this standby.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The approximate number of seconds of redo not yet available on this standby database. This may be because the redo has not yet been shipped or there may be a gap.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
v$dataguard_stats('transport lag')
The Data Guard metrics check the status, data not received, and data not applied for the databases in the Data Guard configuration.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Use the Data Guard Status metric to check the status of each database in the Data Guard configuration.
By default, a critical and warning threshold value was set for this metric column. Alerts will be generated when threshold values are reached. You can edit the value for a threshold as required.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-3 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
9.2.0.x; 10.1.0.x |
Every 5 Minutes |
After Every Sample |
CONTAINS |
Warning |
Error |
1 |
The Data Guard status of %dg_name% is %value%. |
Check the Edit Properties General page for the primary and standby databases for detailed information.
Examine the database alert logs and the Data Guard broker logs for additional information.
This metric category contains the metrics that monitor the number of active instances of a cluster database.
This metric monitors how many instances are in an open state.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric monitors how many instances this cluster database has. This metric is collected at 5-minute intervals and applies for all versions of cluster databases.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric category contains the metrics that represent the health of database jobs registered through the DBMS_JOB interface.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The Oracle Server job queue is a database table that stores information about local jobs such as the PL/SQL call to execute for a job such as when to run a job. Database replication is also managed by using the Oracle job queue mechanism using jobs to push deferred transactions to remote master sites, to purge applied transactions from the deferred transaction queue, or to refresh snapshot refresh groups.
A job can be broken in two ways:
Oracle has failed to successfully execute the job after sixteen attempts. The job has been explicitly marked as broken by using the procedure DBMS_ JOB.BROKEN.
This metric checks for broken DBMS jobs. A critical alert is generated if the number of broken jobs exceeds the value specified by the threshold argument.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-4 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every 5 Minutes |
Not Uploaded |
> |
0 |
Not Defined |
1 |
%value% job(s) are broken. |
SELECT COUNT(*) FROM dba_jobs WHERE broken < > 'N'
Check the ALERT log and trace files for error information. Correct the problem that is preventing the job from running. Force immediate re-execution of the job by calling DBMS_JOB.RUN.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The Oracle Server job queue is a database table that stores information about local jobs such as the PL/SQL call to execute for a job such as when to run a job. Database replication is also managed by using the Oracle job queue mechanism using jobs to push deferred transactions to remote master sites, to purge applied transactions from the deferred transaction queue or to refresh snapshot refresh groups.
If a job returns an error while Oracle is attempting to execute it, the job fails. Oracle repeatedly tries to execute the job doubling the interval of each attempt. If the job fails sixteen times, Oracle automatically marks the job as broken and no longer tries to execute it.
This metric checks for failed DBMS jobs. An alert is generated if the number of failed job exceeds the value specified by the threshold argument.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-5 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every 5 Minutes |
Not Uploaded |
> |
0 |
Not Defined |
1 |
%value% job(s) have failed. |
SELECT COUNT(*) FROM dba_jobs WHERE NVL(failures, 0) < > 0"
Check the ALERT log and trace files for error information. Correct the problem that is preventing the job from running.
This metric category contains the metrics that approximate the percentage of time spent waiting by user sessions across instances for the cluster database. This approximation takes system-wide totals and discounts the effects of sessions belonging to background processes.
This metric represents the active sessions using CPU.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 15 Minutes |
| 8.1.7.4; 9.0.1.x; 9.2.0.x | Every Minute |
This database-level metric represents the active sessions waiting for I/O. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 15 Minutes |
| 8.1.7.4; 9.0.1.x; 9.2.0.x | Every Minute |
This database-level metric represents all the waits that are neither idle nor user I/O. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 15 Minutes |
| 8.1.7.4; 9.0.1.x; 9.2.0.x | Every Minute |
This metric represents the average database CPU across instances as a percentage.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric represents the percentage of CPU being used across hosts.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 15 Minutes |
This metric is the sum of the current CPU load for all cluster database hosts.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric represents the total CPU count across all the cluster database hosts.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 15 Minutes |
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric represents the percentage of time spent waiting, database-wide, for resources or objects during this sample period.
This test checks the percentage time spent waiting, database-wide, for resources or objects during this sample period. If the % Wait Time is greater than or equal to the threshold values specified by the threshold arguments, and the number of occurrences exceeds the value specified in the "Number of Occurrences" parameter, then a warning or critical alert is generated.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-6 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
8.1.7.4; 9.0.1.x; 9.2.0.x |
Every Minute |
After Every Sample |
> |
Not Defined |
Not Defined |
3 |
%value%%% of database service time is spent waiting. |
Table 3-7 Metric Summary Table
| Target Version | Server Evaluation Frequency | Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|---|
|
10.1.0.x |
Every Minute |
Every 15 Minutes |
After Every Sample |
> |
Not Defined |
Not Defined |
3 |
Generated By Database Server |
DeltaTotalWait / (DeltaTotalWait + DeltaCpuTime) where:
DeltaTotalWait: Difference of 'sum of time waited for all wait events in v$system_event' between sample end and start.
DeltaCpuTime: Difference of 'select value from v$sysstat where name='CPU used by this session' between sample end and start.
Investigate further into which specific wait events are responsible for the bulk of the wait time. Individual wait events may identify unique problems within the database. Diagnosis will be tailored where appropriate through drilldowns specific to individual wait events.
This metric category contains the metrics associated with this distributed database's deferred transactions.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Oracle uses deferred transactions to propagate data-level changes asynchronously among master sites in an advanced replication system as well as from an updatable snapshot to its master table.
This metric checks for the number of deferred transactions. An alert is generated if the number of deferred transactions exceeds the value specified by the threshold argument.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-8 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every 5 Minutes |
Not Uploaded |
> |
100 |
Not Defined |
3 |
Number of deferred transactions is %value%. |
SELECT count(*) FROM sys.deftran
When the advanced replication facility pushes a deferred transaction to a remote site, it uses a distributed transaction to ensure that the transaction has been properly committed at the remote site before the transaction is removed for the queue at the local site. If transactions are not being pushed to a given remote site, verify that the destination for the transaction was correctly specified. If you specify a destination database when calling DBMS_DEFER_SYS.SCHEDULE_EXECUTION using the DBLINK parameter, or DBMS_DEFER_SYS.EXECUTE using the DESTINATION parameter, make sure the full database link is provided.
Wrong view destinations can lead to erroneous deferred transaction behavior. Verify that the DEFCALLEST and DEFTRANDEST views are the definitions from the CATREPC.SQL and not those from CATDEFER.SQL.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Oracle uses deferred transactions to propagate data-level changes asynchronously among master sites in an advanced replication system as well as from an updatable snapshot to its master table. If a transaction is not successfully propagated to the remote site, Oracle rolls back the transaction, logs the transaction in the SYS.DEFERROR view in the remote destination database.
This metric checks for the number of transactions in SYS.DEFERROR view and raises an alert if it exceeds the value specified by the threshold argument.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-9 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every 5 Minutes |
Not Uploaded |
> |
0 |
Not Defined |
3 |
Number of deferred transactions with errors is %value%. |
SELECT count(*) FROM sys.deferror
An error in applying a deferred transaction may result from a database problem, such as a lack of available space in the table to be updated, or may be the result of an unresolved insert, update, or delete conflict. The SYS.DEFERROR view provides the ID of the transaction that could not be applied. Use this ID to locate the queued calls associated with the transaction. These calls are stored in the SYS.DEFCALL view. You can use the procedures in the DBMS_DEFER_QUERY package to determine the arguments to the procedures listed in the SYS.DEFCALL view.
The metric in this metric category checks for the number of failed logins on the target database. This check is performed every ten minutes and returns the number of failed logins for that ten-minute interval. This metric will only work for databases where the audit_trail initialization parameter is set to DB or XML and the session is being audited.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric checks for the number of failed logins on the target database. This check is performed every ten minutes and returns the number of failed logins for that ten-minute interval. This metric will only work for databases where the audit_trail initialization parameter is set to DB or XML and the session is being audited.
If the failed login count crosses the values specified in the threshold arguments, then a warning or critical alert is generated. Since it is important to know every time a significant number of failed logins occurs on a system, this metric will generate a new alert for any ten-minute interval where the thresholds are crossed. You can manually clear these alerts; they will not automatically clear after the next collection.
The database stores login information in different views based on the audit_trail setting. The database views used are:
DB or DB_EXTENDED: DBA_AUDIT_SESSION
XML (10g Release 2 only): DBA_COMMON_AUDIT_TRAIL
This metric category contains the metrics representing flash recovery.
This metric returns the Flash Recovery Area Location.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Metric Summary 10gR1 or higher Collection every 5 minutes Not evaluated (not alertable)
SELECT value FROM v$parameter WHERE name='db_recovery_file_dest';
This metric returns whether or not flashback logging is enabled - YES or NO.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric returns whether or not flashback logging is enabled - YES or NO.
Metric Summary 10gR1 or higher Collection every 5 minutes Not evaluated (not alertable)
SELECT flashback_on FROM v$database;
This metric returns the log mode of the database - ARCHIVELOG or NOARCHIVELOG.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Metric Summary 10gR1 or higher Collection every 5 minutes Not evaluated (not alertable)
SELECT log_mode FROM v$database;
This metric represents the oldest point-in-time to which you can flashback your database.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Metric Summary 10gR1 or higher Collection every 5 minutes Not evaluated (not alertable)
SELECT to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') FROM v$flashback_database_log;
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric represents the percentage of space usable in the flash recovery area. The space usable is composed of the space that is free in addition to the space that is reclaimable.
Metric Summary 10gR2 or higher Collection every 5 minutes Not evaluated (not alertable)
SELECT (100 - sum(percent_space_used)) + sum(percent_space_reclaimable) FROM v$flash_recovery_area_usage;
This metric category contains the metrics associated with invalid objects.
This metric represents the total invalid object count.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-10 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every 24 Hours |
Not Uploaded |
> |
Not Defined |
Not Defined |
1 |
%value% object(s) are invalid in the database. |
This metric category contains the metrics that represent the number of invalid objects in each schema.
This metric represents the invalid object count by owner.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-11 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every 24 Hours |
Not Uploaded |
> |
2 |
Not Defined |
1 |
%value% object(s) are invalid in the %owner% schema. |
For each metric index:
SELECT count(1)
View the status of the database objects in the schema identified by the Invalid Object Owner metric. Recompile objects as necessary.
This metric category contains the metrics representing database recovery.
This metric represents the count of corrupt data blocks.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Metric Summary 9iR2 or higher Evaluated and Collected every 15 minutes Operator > Warning Threshold - 0 Critical Threshold - Not Defined Number of corrupt data blocks is %value%.
SELECT count(unique(file#)) FROM v$database_block_corruption;
Perform a database recovery.
This metric represents the count of missing media files.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
Metric Summary 8i or higher Evaluated and Collected every 15 minutes Operator > Warning Threshold - 0 Critical Threshold - Not Defined Number of missing media files is %value%.
SELECT count(file#) FROM v$datafile_header WHERE recover ='YES' OR error is not null;
You should perform a database recovery.
This metric category contains the recovery area metrics.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric is evaluated by the server periodically every 15 minutes or during a file creation, whichever occurs first. It is also printed in the alert log. The Critical Threshold is set for < 3% and the Warning Threshold is set for < 15%. It is not user-customizable. The user is alerted the first time the alert occurs, and the alert is not cleared until the available space rises above 15%.
To free up space from the Flash Recovery Area, follow these steps:
Consider changing your RMAN retention policy. If you are using dataguard, then consider changing your RMAN archivelog deletion policy.
Back up files to a tertiary device, such as tape using the RMAN command BACKUP RECOVERY AREA.
Add disk space and increase the db_recovery_file_dest_size parameter to reflect the new space.
Delete unnecessary files using the RMAN DELETE command. If an OS command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.
This metric category contains the metrics that represent the overall responsiveness of the cluster database with respect to a client.
This metric checks whether a new connection can be established to any cluster database instance. If the database is down, the maximum number of users is exceeded, or the listener is down, the database instance is down. If a new connection cannot be made to any cluster database instance, the cluster database is down. As long as one cluster database instance is up, the cluster database is up.
The following table shows how often the metric's value is collected and compared against the default thresholds. This metric is evaluated every minute on the OMS side to check if all the members are down.
Table 3-12 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every Minute |
After Every Sample |
= |
Not Defined |
0 |
1 |
Target is down -- all members are down. |
The calculation is based on the status of each cluster database instance. As long as one database instance is up, the cluster database is up.
Check the status of each cluster database instance to determine if it is up. Also, check the listener to make sure it is running on all the nodes. If the listener is running, check to see if the number of users is at the session limit. Make sure at least one of the cluster database instances is up. For details, refer to the database instance Status metric.
Oracle uses the Automatic Segment Advisor job to detect segment issues regularly within maintenance windows. It determines whether the segments have unused space that can be released. The Number of recommendations is the number of segments that have Reclaimable Space. The recommendations come from all runs of the automatic segment advisor job and any user-scheduled segment advisor jobs.
Oracle uses the Automatic Segment Advisor job to detect segment issues regularly within maintenance windows. It determines whether the segments have unused space that can be released. The Number of recommendations is the number of segments that have Reclaimable Space. The recommendations come from all runs of the automatic segment advisor job and any user-scheduled segment advisor jobs.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric category contains the metrics that represent the number of resumable sessions that are suspended due to a correctable error.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric represents the session suspended by a data object limitation.
View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric represents the session suspended by a quota limitation.
View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric represents the session suspended by a rollback segment limitation.
View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric represents the session suspended by a tablespace limitation.
View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.
This metric category contains the snapshot too old metrics.
This database-level metric represents snapshots too old because of the rollback segment limit. This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.
This database-level metric represents snapshots too old because of the tablespace limit. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.
This metric shows the total number of Streams capture processes, propagations, and apply processes at the local database. This metric also shows the number of capture processes, propagations, and apply processes that have encountered errors.
This metric shows the number of apply processes that have encountered errors at the local database.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 10 Minutes |
The information in this metric is in the DBA_APPLY data dictionary view.
If an apply process has encountered errors, then correct the conditions that caused the errors.
This metric shows the number of capture processes that have encountered errors at the local database.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 10 Minutes |
The information in this metric is in the DBA_CAPTURE data dictionary view.
If a capture process has encountered errors, then correct the conditions that caused the errors.
This metric shows the number of apply processes at the local database.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 10 Minutes |
The information in this metric is in the DBA_APPLY data dictionary view.
Use this metric to determine the total number of apply processes at the local database.
This metric shows the number of capture processes at the local database.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 10 Minutes |
The information in this metric is in the DBA_CAPTURE data dictionary view.
Use this metric to determine the total number of capture processes at the local database.
This metric shows the number of propagations at the local database.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 10 Minutes |
The information in this metric is in the DBA_PROPAGATION data dictionary view.
Use this metric to determine the total number of propagations at the local database.
This metric shows the number of propagations that have encountered errors at the local database.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| 10.1.0.x | Every 10 Minutes |
The information in this metric is in the DBA_PROPAGATION data dictionary view.
If a propagation has encountered errors, then correct the conditions that caused the errors.
This metric category contains the metrics that represent the number of resumable sessions that are suspended due to a correctable error.
This metric represents the number of resumable sessions currently suspended in the database.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-13 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
9.0.1.x; 9.2.0.x |
Every 5 Minutes |
Not Uploaded |
> |
0 |
Not Defined |
1 |
%value% session(s) are suspended. |
SELECT count(*) FROM v$resumable WHERE status = 'SUSPENDED' and enabled = 'YES'
Query the v$resumable view to see what the correctable errors are that are causing the suspension. The method to correct each error depends on the nature of the error.
The metrics in this metric category check the amount of space used and the amount of space allocated to each tablespace. The used space can then be compared to the allocated space to determine how much space is unused in the tablespace. This metric is intended for reporting, rather than alerts. Historical views of unused allocated free space can help DBAs to correctly size their tablespaces, eliminating wasted space.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The allocated space of a tablespace is the sum of the current size of its data files. A portion of this allocated space is used to store data while some may be free space. If segments are added to a tablespace, or if existing segments grow, they will use the allocated free space. The allocated free space is only available to segments within the tablespace. If, over time, the segments within a tablespace are not using this free space, the allocated free space is not being used.
This metric calculates the space allocated for each tablespace. It is not intended to generate alerts. Rather it should be used in conjunction with the Allocated Space Used (MB) metric to produce a historical view of the amount of space being used and unused by each tablespace.
Tablespace Allocated Space (MB) is calculated by looping though the tablespace�s data files and totalling the size of the data files.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The allocated space of a tablespace is the sum of the current size of its data files. Some of this allocated space is used to store data, and some of it may be free space. If segments are added to a tablespace, or if existing segments grow, they will use the allocated free space. The allocated free space is only available to segments within the tablespace. If, over time, the segments within a tablespace are not using this free space, then the allocated free space is being wasted.
This metric calculates the space used for each tablespace. It is not intended to generate alerts. Rather it should be used in conjunction with the Tablespace Allocated Space (MB) metric to produce a historical view of the amount of space being used and unused by each tablespace.
Tablespace Used Space (MB) is Tablespace Allocated Space (MB)� Tablespace Allocated Free Space (MB) where:
Tablespace Allocated Space (MB) is calculated by looping through the tablespace�s data files and totaling the size of the data files.
Tablespace Allocated Free Space (MB) is calculated by looping through the tablespace�s data files and totaling the size of the free space in each data file.
The metrics in this metric category check for the amount of space used by each tablespace. The used space is then compared to the available free space to determine tablespace fullness. The available free space accounts for the maximum data file size as well as available disk space. This means that a tablespace will not be flagged as full if data files can extend and there is enough disk space available for them to extend.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
As segments within a tablespace grow, the available free space decreases. If there is no longer any available free space, meaning data files reached their maximum size or there is no more disk space, then the creation of new segments or the extension of existing segments will fail.
This metric checks for the total available free space in each tablespace. This metric is intended for larger tablespaces, where the Available Space Used (%) metric is less meaningful. If the available free space falls below the size specified in the threshold arguments, then a warning or critical alert is generated.
If the version of the monitored database target is Oracle Database 10g Release 1 or later and the tablespace uses Local Extent Management, then the Oracle Database Server evaluates this metric internally every 10 minutes. Alternatively, if the version of the monitored Database target is Oracle 9i or earlier, or the tablespace uses Dictionary Extent Management, then the Oracle Management Agent tests the value of this metric every 30 minutes.
MaximumSize� Total Used Space where:
TotalUsedSpace: Total used space in MB of tablespace
MaximumSize: Maximum size (in MB) of the tablespace. The maximum size is determined by looping through the tablespace�s data files, as well as additional free space on the disk that would be available for the tablespace should a data file autoextend.
Perform one of the following:
Increase the size of the tablespace by: Enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.
If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.
Relocate segments to another tablespace, thereby increasing the free space in this tablespace.
Run the Segment Advisor on the tablespace.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
As segments within a tablespace grow, the available free space decreases. If there is no longer any available free space, meaning data files have reached their maximum size or there is no more disk space, then the creation of new segments or the extension of existing segments will fail.
This metric checks the Available Space Used (%) for each tablespace. If the percentage of used space is greater than the values specified in the threshold arguments, then a warning or critical alert is generated.
If the version of the monitored database target is Oracle Database 10g Release 1 or later and the tablespace uses Local Extent Management, then the Oracle Database Server evaluates this metric internally every 10 minutes. Alternatively, if the version of the monitored Database target is Oracle 9i or earlier, or the tablespace uses Dictionary Extent Management, then the Oracle Management Agent tests the value of this metric every 30 minutes.
The following tables show how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-14 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
8.1.7.4; 9.0.1.x; 9.2.0.x |
Every 30 Minutes |
After Every Sample |
> |
85 |
97 |
1 |
Tablespace [%name%] is [%value% percent] full |
|
10.2.0.x |
Every 30 Minutes |
After Every Sample |
> |
85 |
97 |
1 |
Not Defined |
Table 3-15 Metric Summary Table
| Target Version | Server Evaluation Frequency | Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|---|
|
10.1.0.x |
Every 10 Minutes |
Every 30 Minutes |
After Every Sample |
> |
85 |
97 |
1 |
Generated By Database Server |
(TotalUsedSpace / MaximumSize) * 100 where:
TotalUsedSpace: total used space in MB of tablespace
MaximumSize: Maximum size (in MB) of the tablespace. The maximum size is determined by looping through the tablespace�s data files, as well as additional free space on the disk that would be available for the tablespace should a data file autoextend.
For additional information about the data source, refer to the fullTbsp.pl Perl script located in the sysman/admin/scripts directory.
Perform one of the following:
Increase the size of the tablespace by: Enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.
If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.
Relocate segments to another tablespace, thus increasing the free space in this tablespace.
Run the Segment Advisor on the tablespace.
The metrics in this metric category check for the amount of space used by each tablespace. The used space is then compared to the available free space to determine tablespace fullness. The available free space accounts for the maximum data file size as well as available disk space. This means that a tablespace will not be flagged as full if data files can extend, and there is enough disk space available for them to extend.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
As segments within a tablespace grow, the available free space decreases. If there is no longer any available free space, meaning data files have reached their maximum size or there is no more disk space, then the creation of new segments or the extension of existing segments will fail.
This metric checks for the total available free space in each tablespace. This metric is intended for larger tablespaces, where the Available Space Used (%) metric is less meaningful. If the available free space falls below the size specified in the threshold arguments, then a warning or critical alert is generated.
If the version of the monitored database target is Oracle Database 10g Release 1 or later and the tablespace uses Local Extent Management, then the Oracle Database Server evaluates this metric internally every 10 minutes. Alternatively, if the version of the monitored Database target is Oracle 9i or earlier, or the tablespace uses Dictionary Extent Management, then the Oracle Management Agent tests the value of this metric every 30 minutes.
MaximumSize� Total Used Space where:
TotalUsedSpace: Total used space in MB of tablespace
MaximumSize: Maximum size (in MB) of the tablespace. The maximum size is determined by looping through the tablespace�s data files, as well as additional free space on the disk that would be available for the tablespace should a data file autoextend.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
As segments within a tablespace grow, the available free space decreases. If there is no longer any available free space, meaning data files have reached their maximum size or there is no more disk space, then the creation of new segments or the extension of existing segments will fail.
This metric checks the Available Space Used (%) for each tablespace. If the percentage of used space is greater than the values specified in the threshold arguments, then a warning or critical alert is generated.
If the version of the monitored database target is Oracle Database 10g Release 1 or later and the tablespace uses Local Extent Management, then the Oracle Database Server evaluates this metric internally every 10 minutes. Alternatively, if the version of the monitored Database target is Oracle 9i or earlier, or the tablespace uses Dictionary Extent Management, then the Oracle Management Agent tests the value of this metric every 30 minutes.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-16 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
10.1.0.x |
Every 30 Minutes |
After Every Sample |
> |
85 |
97 |
1 |
Tablespace [%name%] is [%value% percent] full |
(TotalUsedSpace / MaximumSize) * 100 where:
TotalUsedSpace: Total used space in MB of tablespace
MaximumSize: Maximum size (in MB) of the tablespace. The maximum size is determined by looping through the tablespace�s data files, as well as additional free space on the disk that would be available for the tablespace should a data file autoextend.
Perform one of the following:
Increase the size of the tablespace by: Enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.
If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.
Relocate segments to another tablespace, thereby increasing the free space in this tablespace.
Run the Segment Advisor on the tablespace.
The metrics in this metric category check for the following:
The largest chunk-free space in the tablespace. If any table, index, cluster, or rollback segment within the tablespace cannot allocate one additional extent, then an alert is generated.
Whether any of the segments in the tablespace are approaching their maximum extents. If, for any segment, the maximum number of extents minus the number of existing extents is less than 2, an alert is generated.
Only the tablespaces with problem segments are returned as results.
Segments nearing the upper limit of maximum extents. This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| All Versions | Every 24 Hours |
The first 10 segment names approaching their MaxExtent in the tablespace.
If possible, increase the value of the segment�s MAXEXTENTS storage parameter. Otherwise, rebuild the segment with a larger extent size ensuring the extents within a segment are the same size by specifying STORAGE parameters where NEXT=INITIAL and PCTINCREASE = 0.
For segments that are linearly scanned, choose an extent size that is a multiple of the number of blocks read during each multiblock read. This ensures that the Oracle multiblock read capability is used efficiently.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric checks for segments nearing the upper limit of the number of maximum extents. If the number of segments is greater than the values specified in the threshold arguments, a warning or critical alert is generated.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-17 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every 24 Hours |
After Every Sample |
> |
0 |
Not Defined |
1 |
%value% segments in %name% tablespace approaching max extents. |
Number of segments for which the maximum number of extents minus the number of existing extents is less than 2.
For additional information about the data source, refer to the problemTbsp.pl Perl script located in the sysman/admin/scripts directory.
If possible, increase the value of the segment�s MAXEXTENTS storage parameter. Otherwise, rebuild the segment with a larger extent size ensuring the extents within a segment are the same size by using a locally managed tablespace. For a dictionary managed tablespace, specify STORAGE parameters where NEXT=INITIAL and PCTINCREASE = 0.
This metric checks for segments that cannot allocate an additional extent.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
The following table shows how often the metric's value is collected.
| Target Version | Collection Frequency |
|---|---|
| All Versions | Every 24 Hours |
The first 10 segment names that cannot allocate an additional extent in the tablespace.
Perform one of the following:
Increase the size of the tablespace by enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.
If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric checks for segments that cannot allocate an additional extent. If the number of segments is greater than the values specified in the threshold arguments, a warning or critical alert is generated.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-18 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
All Versions |
Every 24 Hours |
After Every Sample |
> |
0 |
Not Defined |
1 |
%value% segments in %name% tablespace unable to extend. |
After checking for the largest chunk free space in the tablespace, this is the number of segments that cannot allocate an additional extent.
For additional information about the data source, refer to the problemTbsp.pl Perl script located in the sysman/admin/scripts directory.
Perform one of the following:
Increase the size of the tablespace by enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.
If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.
Relocate segments to another tablespace, thereby increasing the free space in this tablespace.
This metric category contains the metrics that tell to what extent, and how consistently, a given session is blocking multiple other sessions.
This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.
This metric signifies that a database user is blocking at least one other user from performing an action, such as updating a table. An alert is generated if the number of consecutive blocking occurrences reaches the specified value. The sessions being blocked can come from different instances.
Note: The catblock.sql script needs to be run on the managed database prior to using the User Blocks test. This script creates some additional tables, view, and public synonyms that are required by the User Blocks test.
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Table 3-19 Metric Summary Table
| Target Version | Evaluation and Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|
|
8.1.7.4; 9.0.1.x; 9.2.0.x |
Every 5 Minutes |
Not Uploaded |
> |
11 |
Not Defined |
3 |
Session %sid% blocking %value% other sessions. |
Table 3-20 Metric Summary Table
| Target Version | Server Evaluation Frequency | Collection Frequency | Upload Frequency | Operator | Default Warning Threshold | Default Critical Threshold | Consecutive Number of Occurrences Preceding Notification | Alert Text |
|---|---|---|---|---|---|---|---|---|
|
10.1.0.x |
Every Minute |
Every 15 Minutes |
After Every Sample |
> |
11 |
Not Defined |
3 |
Generated By Database Server |
SELECT blocking_sid, num_blocked
FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked
FROM ( SELECT l.id1, l.id2,
MAX(DECODE(l.block, 1, i.instance_name||'-'||l.sid,
2, i.instance_name||'-'||l.sid, 0 )) blocking_sid,
SUM(DECODE(l.request, 0, 0, 1 )) num_blocked
FROM gv$lock l, gv$instance i
WHERE ( l.block!= 0 OR l.request > 0 ) AND
l.inst_id = i.inst_id
GROUP BY l.id1, l.id2)
GROUP BY blocking_sid
ORDER BY num_blocked DESC)
WHERE num_blocked != 0
Either have the user who is blocking other users rollback the transaction, or wait until the blocking transaction has been committed.