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:
This Knowledge Script is supported on Oracle 11gR2 and Oracle 12c.
Oracle RAC Database Instance. When you drop a script on an Oracle RAC database Instance, a job executes on that database Instance and monitors only that database Instance.
The default interval for this script is Every 10 minutes.
|
Description |
How to Set It |
|---|---|
|
General Settings |
|
|
Cluster(s) to exclude |
Enter the cluster names to exclude with a comma (,) between each cluster name, to exclude the cluster from all jobs. |
|
Database Instance(s) to exclude. |
Enter the database instances to exclude with a comma (,) between each database instance name, to exclude the database instance from all jobs. |
|
Event severity when job fails |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default value is 5 (red event indicator). |
|
Enable debugging? |
Select Yes to enable debugging. The default is unselected. |
|
Raise event when AppManager fails to get metrics? |
Select Yes to raise an event when AppManager fails to get the metrics. The default is Yes. |
|
Event severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default value is 5 (red event indicator). |
|
Event Settings |
|
|
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 unselected. |
|
Threshold - Maximum Parses per user call |
Enter the maximum parses per user call. If the maximum parses per user call exceeds this threshold, the job raises an event. The default value is 0.2. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default value is 5 (red event indicator). |
|
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. |
|
Number of user sessions to display |
Enter the number of user session to display. The default value is 10 user sessions. |
|
Threshold - Maximum total user sessions |
Enter the total number of user session that exceed the threshold. If the total number of user sessions exceeds this threshold, the job raises an event. The default value is 75 sessions. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default value is 5 (red event indicator). |
|
Raise event if threshold is exceeded for User Rollback Ratio? |
Select Yes to raise an event if the user rollback ratio exceeds the threshold. The default is unselected. |
|
Threshold - Maximum user rollback ratio |
Enter the user rollback ratio that exceed the threshold. If the total number of user sessions exceeds this threshold, the job raises an event. The default value is 75%. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default value is 5 (red event indicator). |
|
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. |
|
Threshold - Maximum number of blocking sessions |
Enter the maximum number of blocking session that exceed the threshold. If the total number of blocking sessions exceeds this threshold, the job raises an event. The default value is 10 sessions. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default value is 5 (red event indicator). |
|
Data Collection |
|
|
Collect data for |
Select Yes check box to collect data for charts and reports. When you enable data collection, and if monitoring of each metric is enabled, the Knowledge Script returns the following statistics for each instance on which the script is running:
|