3.6 BufferBusyWaits

Use this Knowledge Script to retrieve the number of buffer busy waits and the number of logical reads, and compute a ratio between the two numbers (expressed as a percentage). When you enable data collection, the repository stores the ratio. You can set multiple thresholds for the maximum ratio, and the job raises an event when any of the thresholds exceeds the value you specified. A higher ratio indicates an increased contention for buffers in the SGA memory of Oracle Database.

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

V_$SYSTEM_EVENT
V_$SYSSTAT
V_$VERSION

3.6.1 Resource Objects

Oracle Database folders. When you drop a script on an Oracle Database folder, a job executes on that database and collects the number of buffer busy waits and the number of logical reads. The ratio of buffer waits over logical reads is then computed.

3.6.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.6.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 ratio of buffer busy waits to logical reads?

Select Yes to collect data for charts and reports. If data collection is enabled, returns the ratio of buffer busy waits to logical reads as a percentage (%). The default is unselected.

Raise event if threshold is exceeded?

Select Yes to raise an event if the threshold exceeds the value you specified. The default is Yes.

Threshold - Maximum ratio of buffer busy waits to logical reads

Specify a threshold for the maximum ratio of buffer busy waits to logical reads. The default is .95.

Severity

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

Raise event if threshold is exceeded?

Select Yes to raise an event if the ratio exceeds the threshold specified below. The default is Yes.

Threshold – Maximum ratio of buffer busy waits to logical reads

Specify a threshold for the maximum ratio of buffer busy waits to logical reads. The default is .80.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 15 (yellow event indicator).

Raise event if threshold is exceeded?

Select Yes to raise an event if the threshold exceeds the value you specified. The default is unselected.

Threshold – Maximum ratio of buffer busy waits to logical reads

Specify a threshold for the maximum ratio of buffer busy waits to logical reads. The default is .60.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 25 (blue event indicator).

Raise event if threshold is exceeded?

Select Yes to raise an event if the threshold exceeds the value you specified. The default is unselected.

Threshold – Maximum ratio of buffer busy waits to logical reads

Specify a threshold for the maximum ratio of buffer busy waits to logical reads. The default is.40.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 35 (magenta 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).