3.42 TopResourceConsumingSQL

Use this Knowledge Script to determine which SQL queries for Oracle database are consuming the most resources on their UNIX hosts. This script identifies the top N queries consuming the most memory, disk I/O, and CPU time. The job raises an event with results of the job, and/or if the job results in a failure to retrieve data.

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

DBA_USERS

V_$SQLAREA

V_$VERSION

3.42.1 Resource Objects

Oracle Database folders. When you drop a script on an Oracle Database folder, a job executes on that database and collects the N SQL queries using the most CPU time, disk I/O, and memory.

3.42.2 Default Schedule

The default schedule for this script is Every hour.

3.42.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 top resource-consuming SQL statements?	Set Yes to collect data for charts and reports. If data collection is enabled, returns the top N SQL statements consuming the most resources. The parameters below let you choose which resources to monitor. Set a value for N using the Number of SQL statements to retrieve parameter.The default is unselected.
Monitor SQL statements consuming the most disk I/O?	Select Yes to monitor SQL statements consuming the most disk I/O. The default is Yes.
Monitor SQL statements consuming the most memory?	Select Yes to monitor SQL statements consuming the most memory. The default is Yes.
Monitor SQL statements consuming the most CPU time?	Select Yes to monitor SQL statements consuming the most CPU time. The default is Yes. NOTE:You cannot use this metric on Oracle databases prior to version 9.0
Number of SQL statements to retrieve	The number of most executed SQL queries to be retrieved by the job. The default is 10. The maximum is 30.
Raise event if error occurs during retrieval?	Select Yes to raise an event when an error occurs during job execution. The default is Yes.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 10 (red event indicator.
Raise event with results from query?	Select Yes to raise an event with the results of the query. The default is unselected.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 40 (magenta 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).