Use this Knowledge Script to monitor the following data ratios:
Consistent Change Ratio: the extent to which applications exercise the read consistency mechanism to ensure database consistency.
The consistent change ratio is based on the number of database changes and database reads. “Consistent changes” refers to the number of times a consistent Get had to retrieve an old version of a database block because of updates occurring after the cursor had been opened. When the ratio of consistent changes to consistent Gets exceeds the threshold, the job raises an event. The ratio is recalculated each time the script runs, based on the data collected during that monitoring interval.
Continued Row Ratio: the rows that span more than one database block and the ratio of continued rows fetched to all rows fetched.
In most cases, this ratio should be close to zero. If the continued row ratio increases over time (indicating that more and more rows span multiple database blocks), the PCTFREE storage parameter might be set too low for one or more tables. If the continued row ratio exceeds the threshold, the job raises an event. The ratio is recalculated each time the script runs, based on the data collected during that interval.
Row Source Ratio: the row source ratio for an Oracle RDBMS database. 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, review other statistics to find the source of the problem. When this ratio exceeds the threshold, the job raises an event.
Sort Overflow Ratio: 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 can 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
Oracle Database folders. When you drop a script on an Oracle Database folder, a job executes on that database and monitors only that database.
The default interval for this script is Every 1 Hour.
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. |
|
Ratios |
|
|
Consistent Change Ratio |
Select Yes to monitor Consistent Change Ratio. The default is unselected. |
|
Continued Row Ratio |
Select Yes to monitor Continued Row Ratio. The default is unselected. |
|
Row Source Ratio |
Select Yes to monitor Row Source Ratio. The default is unselected. |
|
Sort Overflow Ratio? |
Select Yes to monitor Sort Overflow Ratio. The default is unselected. |
|
Event Notification |
|
|
Raise event if threshold is exceeded for Consistent Change Ratio? |
Select Yes to raise an event if the consistent change ratio exceeds the threshold you set. The default is unselected. |
|
Raise event if threshold is exceeded for Continued Row Ratio? |
Select Yes to raise an event if the continued row ratio exceeds the threshold you set. The default is Yes. |
|
Raise event if threshold is exceeded for Row Source Ratio? |
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 Yes. |
|
Raise event if threshold is exceeded for Sort Overflow Ratio? |
Select Yes to raise an event if the sort overflow ratio exceeds the threshold you set. The default is Yes. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator). |
|
Data Collection |
|
|
Collect data for Consistent Change Ratio? |
Select Yes to collect data for charts and reports. When you enable data collection, the script returns the ratio of consistent changes to consistent gets. The default is unselected. |
|
Collect data for Continued Row Ratio? |
Select Yes to collect data for charts and reports. When you enable data collection, the script returns the continued row ratio. The default is unselected. |
|
Collect data for Row Source Ratio? |
Select Yes to collect data for graphs and reports. When you enable data collection, the script returns the percentage of rows retrieved using a full tables scan. The default is unselected. |
|
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 of sorts that are not using temporary segments. For example, a ratio of .75 indicates that 3 out of 4 sorts are using temporary segments. The default is unselected. |
|
Monitoring |
|
|
Threshold - Maximum consistent change ratio |
Enter a threshold for the maximum ratio of consistent block changes to consistent block gets during the monitoring interval. The default 0.01. |
|
Threshold - Maximum continued row ratio [0.0 to 1.0] |
Enter a threshold for the maximum ratio of continued rows fetched to all rows fetched. Enter values from 0.0 to 1.0, inclusive. The default is 0.01. |
|
Threshold - Maximum row source ratio |
Enter a maximum threshold for the row source ratio. The default ratio is .25. |
|
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. |
|
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). |