Use this Knowledge Script to monitor the percentage of cursors opened per session, as well as the total number of cursors open in the system. In the Oracle RDBMS environment, a cursor is a type of handle (or pointer) used to identify a query in the system. Cursors can be opened by users or by the system itself. A high number of open cursors can be caused by a programming error, and might cause database performance problems. In the init.ora file, you can specify the maximum number of cursors that might be opened by a session.
In this script, you can specify a maximum threshold for the percentage of open cursors allowed per session--a percentage of the number specified for the open_cursor parameter in the init.ora file. For example, if the init.ora file specifies that 60 cursors might be open in a session, and you set a maximum threshold of 75%, the script raises an event when 75% of the 60 allowed cursors (or 45 cursors) are open in any session.
You can also specify a maximum threshold for the total number of open cursors allowed in the system. The job raises an event if either threshold exceeds the value you specified.
The account you use to run this script must have SELECT permissions for the following tables:
V_$PARAMETER
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 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. |
|
Collect data for number of open cursors? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the total number of open cursors in the system. The default is unselected. |
|
Raise event if threshold is exceeded? |
Select Yes to raise an event if the number of cursors open in the system exceeds the threshold you set. The default is Yes. |
|
Threshold - Maximum number of open cursors in system |
Specify a maximum threshold for the number of cursors that might be open in the system. The default is 1000. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 10 (red event indicator). |
|
Raise event if threshold is exceeded? |
Select Yes to raise an event if the percentage of cursors opened per session exceeds the threshold you set. The default is Yes. |
|
Threshold - Maximum percentage of cursors opened per session |
Specify a maximum threshold for the percentage of open cursors per session. Note that this percentage is based on the number of open cursors allowed per session specified in the init.ora file. The default is 80. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 10 (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). |