3.35 SortOverflowRatio

Use this Knowledge Script to monitor the sort overflow ratio. This ratio compares the number of sorts that are using temporary segments to the total number of sorts. If the sort overflow ratio exceeds the threshold you set, the job raises an event.

An increase in the sort overflow ratio indicates that more sort operations are allocating work space on disk. If an excessive number of sorts are allocating work space on disk, you might want to increase the sort area size.

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

V_$SYSSTAT

V_$VERSION

3.35.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.35.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.35.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 sort overflow ratio?	Select Yes to collect data for charts and reports. When you enable data collection, the script returns the ratio of the number of sorts using temporary segments versus the number that do not. For example, a ratio of .75 indicates that 3 out of 4 sorts are using temporary segments. The default is unselected.
Raise event if threshold is exceeded?	Select Yes to raise an event if the sort overflow ratio exceeds the threshold you set. The default is Yes.
Threshold - Maximum sort overflow ratio	Enter a threshold for the maximum sort overflow ratio allowed before the job raises an event. The default is .75.
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).