Use this Knowledge Script to monitor the following parameters:
User Calls Per Parse: the ratio of parse count (hard) to the number of user calls as a percentage. The number of user calls that result in a parse indicates how well an application is managing its context area. Changes in this ratio might indicate changes to the application itself, or to changing usage patterns (For example, because users are moving from one module to another, more or less frequently).
Generally, if the ratio of parsed calls to total user calls is low, it indicates that the SQL statements are executing efficiently without frequent reparsing. Otherwise, it can indicate that the private SQL area is too small.
This script raises an event when the percentage of user calls that are parsed exceeds the threshold you set.
User Sessions: the total number of user sessions accessing an Oracle database. If the total number of user sessions crosses the threshold, the job raises an event.
You can specify the number of user sessions to display in the Event Properties dialog box. The Event Properties dialog box displays the total number of user sessions exceeding the threshold. Information in the graph includes the number of sessions for each user and the username.
User Rollback Ratio: the user rollback ratio for an Oracle RDBMS Database. The user rollback ratio indicates the percentage of attempted application transactions that fail. The ratio compares the number of transactions rolled back to the total number of transactions attempted.
Because rolling back a transaction uses significant system resources, an increase in this ratio suggests resources have been wasted in attempting to execute failed transactions. If you see a continued increase in this ratio, it can indicate serious application or database performance problems. This script raises an event when the rollback ratio exceeds the threshold.
Blocking Sessions: the user sessions that are blocking other sessions and processes from accessing the Oracle database. You can set a maximum threshold for the number of sessions that are allowed to block other sessions and processes. If the number of blocking sessions 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_$LOCK
V_$VERSION
DBA_USERS
V_$SESSION
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 10 minutes.
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. |
|
User |
|
|
User Calls Per Parse |
Select Yes to monitor User Calls Per Parse. The default is unselected. |
|
User Sessions |
Select Yes to monitor User Sessions. The default is unselected. |
|
User Rollback Ratio |
Select Yes to monitor User Callback Ratio. The default is unselected. |
|
Blocking Sessions |
Select Yes to monitor Blocking Sessions. The default is unselected. |
|
Event Notification |
|
|
Raise event if threshold is exceeded for User Calls Per Parse? |
Select Yes to raise an event if the ratio of parses per user call exceeds the threshold you set. The default is Yes. |
|
Raise event if threshold is exceeded for User Sessions? |
Select Yes to raise an event if the number of user sessions exceeds the threshold. The default is Yes. |
|
Raise event if threshold is exceeded for User Rollback Ratio? |
Select Yes to raise an event if the number of user transaction rollbacks exceeds the threshold. The default is Yes. |
|
Raise event if threshold is exceeded for Blocking Sessions? |
Select Yes to raise an event if the number of blocking sessions 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 User Calls Per Parse? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the current percentage of user calls that are parsed. The default is unselected. |
|
Collect data for User Sessions? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the total number of user sessions. The default is unselected. |
|
Collect data for User Rollback Ratio? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the current user rollback ratio. The default is unselected. |
|
Collect data for Blocking Sessions? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the number of blocking sessions per interval. The default is unselected. |
|
Monitoring |
|
|
Threshold - Maximum Parses Per User Call |
Enter a threshold for the maximum ratio of parsed calls to total user calls allowed before an event is raised. The default is 0.2. |
|
Threshold - Maximum total user sessions |
Enter a threshold for maximum number of user sessions. The default is 75. |
|
Number of user sessions to display |
Specify the number of user sessions you want displayed in the Event Properties dialog box. Enter 0 if you want all user sessions displayed. The default is 10. |
|
Threshold - Maximum user rollback ratio |
Enter a threshold for the maximum percentage of transaction rollbacks allowed before the job raises an event. The default is 75. |
|
Threshold - Maximum number of blocking sessions |
Enter a threshold for the maximum number of user sessions allowed to block other user sessions and processes during the monitoring interval. The default is 10. |
|
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). |