Use this Knowledge Script to monitor the following parameters:
Top CPU Users: the CPU time for current user sessions. If the CPU utilization exceeds the threshold, the job raises an event.
You can specify the number of user sessions with the highest CPU utilization to display in the Event Properties dialog box. The Event Properties dialog box includes the CPU usage for each of the top N sessions, username, session ID, and program name. Enter 0 to display all user sessions.
This script requires that the Oracle timed_statistics parameter be turned on (set to TRUE) for the database you are monitoring.
Top IO Users: Physical reads and writes (I/O) for current user sessions. If the number of physical reads/writes per second (the physical read/write operations rate) exceeds the threshold you set, the job raises an event.
You can specify the number of user sessions with the highest physical read/write operations rate to display in the Event Properties dialog box. Information in the Event Properties dialog box includes the physical reads/writes per second for each of the top N sessions, username, session ID, and program name.
This script requires that the Oracle timed_statistics parameter is turned on for the database you are monitoring.
Top Lock Users: The current number of user‑held locks on an Oracle database. If the number of locks exceeds the threshold, the job raises an event.
You can specify the number of user sessions holding the most locks to display in the Event Properties dialog box, or enter 0 to display all sessions. Information in the Event Properties dialog box includes the number of locks held by each session, username, session ID, and program name.
Top Memory Users: The memory utilization (User Global Area and Program Global Area) for current user sessions. If the memory utilization exceeds the threshold, the job raises an event.
You can specify the number of user sessions with the highest memory usage to display in the Event Properties dialog box. Information in the Event Properties dialog box includes the memory in bytes for each session, username, session ID, and program name. Enter 0 for the Number of top user sessions to display parameter if you want to include memory utilization statistics for all user sessions in the event details.
Open Cursors: 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:
DBA_USERS
V_$SESSION
V_$SESSTAT
V_$STATNAME
V_$VERSION
V_$LOCK
V_$PARAMETER
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. |
|
Performance |
|
|
Top CPU Users |
Select Yes to monitor the Top CPU Users. The default is unselected. |
|
Top IO Users |
Select Yes to monitor the Top IO Users. The default is unselected. |
|
Top Lock Users |
Select Yes to monitor the Top Lock Users. The default is unselected. |
|
Top Memory Users |
Select Yes to monitor the Top Memory Users. The default is unselected. |
|
Open Cursors |
Select Yes to monitor the Open Cursors. The default is unselected. |
|
Event Notification |
|
|
Raise event if threshold is exceeded for Top CPU Users? |
Select Yes to raise an event if the CPU usage of any user session exceeds the threshold you set. The default is Yes. |
|
Raise event if threshold is exceeded for Top IO Users? |
Select Yes to raise an event if the physical read/write operations of any single user session exceed the threshold you set. The default is Yes. |
|
Raise event if threshold is exceeded for Top Lock Users? |
Select Yes to raise an event if the number of user-held locks on the server exceeds the threshold you set. The default is Yes. |
|
Raise event if threshold is exceeded for Top Memory Users? |
Select Yes to raise an event if the total memory usage of any user session exceeds the threshold you set. The default is Yes. |
|
Raise event if threshold is exceeded for Total Number of Open Cursors in the System? |
Select Yes to raise an event if the number of cursors open in the system 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 10 (red event indicator). |
|
Raise event if threshold is exceeded for Percentage of Cursors Opened per Session? |
Select Yes to raise an event if the percentage of cursors opened per session 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 10 (red event indicator). |
|
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 Top CPU Users? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the total CPU time for the top N users. The default is unselected. |
|
Collect data for Top IO Users? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the total number of physical reads/writes per second for the top N users. The default is unselected. |
|
Collect data for Top Lock Users? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the current number of user-held locks by the user sessions with the highest number of locks. The default is unselected. |
|
Collect data for Top Memory Users? |
Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the total memory usage (in MB) for the top N user sessions. The default is unselected. |
|
Collect data for Open Cursor? |
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. |
|
Monitoring |
|
|
Number of user sessions of display [Not Applicable to Open Cursor Counters] |
Specify the number of user sessions you want displayed in the Event Properties dialog box. Enter 0 if you want information for all user sessions. The default is 15. |
|
Threshold - Maximum amount of CPU time for a user session |
Enter a threshold for the maximum number of CPU cycles per 1/100th of a second that a single user session can use before the job raises an event. The default is 50. |
|
Threshold - Maximum read/write operations for a user session |
Enter a threshold for the maximum number of physical reads/writes per second allowed before the job raises an event. The default is 300. |
|
Threshold - Maximum number of locks held by a user session |
Enter a threshold for the maximum number of user‑held locks on an Oracle RDBMS. The default is 35. |
|
Threshold - Maximum amount of memory for a user session |
Enter a threshold for the maximum total memory usage (in MB) for any user session. The default is 10. |
|
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. |
|
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. |
|
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). |