Use this Knowledge Script to determine which SQL queries for Oracle database instances 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
This Knowledge Script is supported on Oracle 11gR2 and Oracle 12c.
Oracle RAC Database Instance. When you drop a script on that Oracle Database Instance, a job executes on that database and collects the N SQL queries using the most CPU time, disk I/O, and memory.
The default interval for this script is Every hour.
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. |
|
Instances(s) to exclude |
Enter the database instances names to exclude with a comma (,) between each database instance 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 severity level 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 when AppManager fails to get metrics |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator). |
|
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. |
|
Event Settings |
|
|
Raise Event with top SQL statements consuming the most disk I/O? |
Select Yes to raise an event for top SQL statements that consume the most disk I/O space. The default is Yes. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator). |
|
Raise Event with top SQL statements consuming the most memory? |
Select Yes to raise an event for top SQL statements that consume the most disk memory space. The default is Yes. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator). |
|
Raise Event with top SQL statements consuming the most CPU time? |
Select Yes to raise an event for top SQL statements that consume the most CPU time. The default is Yes. |
|
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 SQL statements consuming the most… |
Select Yes to collect data for charts and reports. When you enable data collection, and if monitoring of each metric is enabled, the Knowledge Script returns the following statistics for each instance on which the script is running:
The default for all parameters is unselected. |