3.47 UserCallsPerParse

Use this Knowledge Script to monitor 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 might 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.

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

V_$SYSSTAT
V_$VERSION

3.47.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.47.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.47.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 parses per user call?

Set to y 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 n.

Raise event if ratio exceeds threshold?

Select Yes to raise an event if the ratio of parses per user call exceeds the threshold you set. The default is Yes.

Threshold - Maximum ratio of 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.

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).