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.
This script requires that the Oracle timed_statistics initialization parameter be turned on (set to TRUE).
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.
Top IO Users: Physical reads and writes (I/O) for current user sessions. If the number of physical reads/writes per interval (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.
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.
The default interval for this script is Every 30 minutes.
Set the following parameters as needed:
|
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 name, to exclude the database 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 |
|
|
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 value is 15 user sessions. |
|
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. |
|
Threshold - Maximum amount of 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 value is 50 CPU cycles per 1/100th of a second. |
|
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 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. |
|
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 value is 10 MB. |
|
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 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. |
|
Threshold - Maximum read/write operations per user session |
Enter a threshold for the maximum number of read/write Operations per Interval allowed before the job raises an event. The default value is 300 read/write Operations per Interval. |
|
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 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. |
|
Threshold - Maximum number of locks held by a user session |
Enter a threshold for the maximum number of user-held locks on per user session. The default value is 35 locks. |
|
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 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 unselected. |
|
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 value is 1000 open cursors. |
|
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 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 unselected. |
|
Threshold - Maximum percentage of cursor opened per session |
Specify a maximum threshold for the percentage of cursors that might be open per session. The default is 80. |
|
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 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 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 Top I/O 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 interval 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 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. |