3.30 RowSourceRatio

Use this Knowledge Script to monitor the row source ratio for an Oracle RDBMS. This ratio measures the percentage of rows retrieved using full table scans. Because a full table scan is less efficient than retrieval by row ID, this ratio gives you an indication of potential database performance problems. If you see an increase in this ratio, you might want to review other statistics to find the source of the problem. When this ratio exceeds the threshold, the job raises an event.

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

V_$SYSSTAT
V_$VERSION

3.30.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.30.2 Default Schedule

The default interval for this script is Every hour.

3.30.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 row source ratio?

Select Yes to collect data for use in graphs and reports. When you enable data collection, the script returns the percentage of rows retrieved using a full table scan. The default is unselected.

Raise event if threshold is exceeded?

Select Yes to raise an event if the percentage of rows retrieved using a full table scan exceeds the threshold you set. The default is unselected.

Threshold - Maximum row source percentage

Enter a maximum threshold for the row source ratio. The default is 25.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (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).