3.29 RollbackSegmentContention

Use this Knowledge Script to monitor rollback segment contention for a database. In the Oracle RDBMS environment, the rollback segment is a temporary location where changes are stored until the user makes the changes permanent. This script compares the number of requests waiting to access data from the rollback segment to the total number of requests for data during the monitoring interval.

You can specify the maximum percentage of requests allowed to wait for data from the rollback segment. If the percentage of waiting requests exceeds the threshold you set, the job raises an event. Such an event probably indicates that too many processes are waiting to access the rollback segment, in which case, you might need to create additional rollback segments.

The account you use to run this script must have SELECT permissions for the following tables:

V_$SYSSTAT
V_$VERSION
V_$WAITSTAT

3.29.1 Resource Objects

Oracle Database folders. When you drop a script on an Oracle Database folder, a job executes on that database and monitors only that database.

3.29.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.29.3 Setting Parameter Values

Set the following parameters as needed:

Description

How to Set It

Oracle Username

Enter the username that this script needs to access the target databases. If you run this script on more than one database, configure each database with the same username. To use SYSDBA authentication, leave this parameter blank. The default is blank.

Collect data for rollback segment contention?

Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the percentage of requests waiting to access the rollback segment. The default is unselected.

Raise event if threshold is exceeded?

Select Yes to raise an event if the percentage of total requests that are waiting to access the rollback segment exceeds the threshold you set. The default is unselected.

Threshold - Maximum percentage of requests waiting for access

Enter the maximum percentage of total requests that can be waiting to access data from the rollback segment before the job raises an event. The default is 1.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 10 (red event indicator).

Event severity for internal failure

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator).