3.22 MostExecutedSQLStatements

Use this Knowledge Script to determine which SQL queries or statements are being executed on a given Oracle database most frequently. This script identifies the N most frequently executed queries and returns the results in an event. You set a value for N using the Number of SQL statements to retrieve parameter.

When you enable data collection, the most frequently executed SQL statements are stored in the repository.

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

V_$SQLAREA

V_$VERSION

3.22.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 most executed SQL statements or queries.

3.22.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.22.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 most executed SQL statements?	Select Yes to collect data for charts and reports. If data collection is enabled, returns the top N most frequently executed SQL statements. Set a value for N using the Number of SQL statements to retrieve parameter (see below). The default is unselected.
Number of SQL statements to retrieve	The number of most frequently 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 if an error occurs during the retrieval of SQL statements. 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 details of the query results. 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).