Database Performance Alerts 

You might see the alerts described here when monitoring your Oracle database.

"[BRCONNECT]" below means that the alert is raised by the BRCONNECT database system check.

"[OPTIONAL]" below means that the alert can be turned off here and shown only in the Health monitoring tree. Such alerts always have corresponding Health alerts, as described in Database Health Alerts.

For more information, see Information on Oracle Database Alerts.

Optimizer

Last successful update statistics

Cause: Threshold is exceeded for number of days since the last successful run of brconnect -f stats with options -t all. Return codes 0 (success) or 1 (success with warning) indicate a successful update statistics.

Action: Run brconnect -f stats -t all.

 

Last brconnect -f stats run

Cause: Failed last run of brconnect -f stats, regardless of the options used. For more information, see LAST_STATS_FAILED in BRCONNECT Default Conditions for Database Operations.

Action: Run brconnect -f stats.

 

Harmful statistics [BRCONNECT] [OPTIONAL]

Cause: There are tables or indexes that have statistics, although they should not have these (for example, pool and cluster tables).

Action: Remove statistics from these tables or indexes. For more information, see brconnect -f stats.

The non-standard equivalent to this optional alert is Harmful statistics in Database Health Alerts.

 

Missing statistics [BRCONNECT] [OPTIONAL]

Cause: There are tables or indices that do not have any statistics, although they should have these.

Action: Create statistics for these tables or indexes. For more information, see brconnect -f stats.

The non-standard equivalent to this optional alert is Missing statistics in Database Health Alerts.

 

Buffers

Buffer cache

Cause: The buffer cache hit ratio fell below the configured threshold.

Action: Increase the size of the buffer cache only if the previous size increase improved the buffer cache hit ratio. For most applications, the buffer cache hit ratio should be above 90%. Applications that mostly execute long table scans cannot benefit as much from the buffer cache since such applications tend to overwrite the buffer cache. For additional information on how to tune the buffer cache, refer to Oracle's documentation.

 

Library cache

Cause: The library cache hit ratio exceeded the configured threshold.

Action: Increase the size of the shared pool in which the library cache resides until the V$LIBRARYCACHE.RELOADS value is near 0. This is done by increasing the value of the init<DBSID>.ora parameter SHARED_POOL_SIZE. The application should use identical SQL statements whenever possible. For more information, see Database Parameters and the Oracle documentation.

 

Redo log buffer

Cause: The number of redo entries per redo log space request fell below the configured threshold. Oracle recommends at least 5000 redo entries per redo log space request.

Action: Increase the size of the redo log buffer by increasing the init<DBSID>.ora parameter log_bufferuntil the number of redo entries per redo log space request stops increasing. For more information, see Database Parameters.

 

Locks

Age of oldest exclusive transaction lock

Cause: Age of the oldest exclusive transaction lock.

Action: Identify why the exclusive transaction lock is so old by identifying which transactions or programs own the lock.

 

Deadlocked resource [BRCONNECT] [OPTIONAL]

Cause: Result of the search in the alert log for the following Oracle message:

ORA00060: Deadlock while waiting for resource.

Action: Identify why the resource is deadlocked by searching for more information in the Oracle trace files.

The non-standard equivalent to this optional alert is Deadlock waiting for resource in Database Health Alerts.

 

Checkpoints

Checkpoint not complete [BRCONNECT] [OPTIONAL]

Cause: Result of the search in the alert log for the Oracle message: Checkpoint not complete. After a log switch, data is written from the database buffer into the datafiles, or synchronized. This error occurs if the next log switch is activated before the data has been written completely to the data files.

Action: If this error occurs often, there is a database performance problem. To correct the problem, enlarge the online redo log files. For more information, see the Oracle documentation.

The non-standard equivalent to this optional alert is Checkpoint not complete in Database Health Alerts.