cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Alert - Long Running Query (Oracle)

DESCRIPTION

This alert is for Oracle instances only and will tell you when SQL statements have executed more than a defined amount of time. This example uses a 300 second threshold, meaning any SQL statements that have been executing for longer than 300 seconds (5 minutes) will be alerted. The alert query executes against the monitored database directly so caution should be taken on how often the alert is executed, especially if less than the default of every 300 seconds.

ALERT DEFINITION

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

Alert Name: Oracle - Long Running Queries

Execution Interval: 5 Minutes

Notification Text: The following list of SQL statements have been running for over 5 minutes.

SQL Statement:

select 'SID:'||s.sid||', Serial#:'||s.serial#||', Username:'||s.username||', Machine:'||s.machine||

       ', Program:'||s.program||', HashValue:'||s.sql_hash_value||', SQL Text:'||nvl(substr(sql.sql_text,1,40),'Unknown SQL'), last_call_et

from v$session s

left outer join v$sql sql on sql.hash_value=s.sql_hash_value and sql.address=s.sql_address

where s.status='ACTIVE'

and s.type <> 'BACKGROUND'

and last_call_et >= 30

Execute Against: Monitored Database

Units: Seconds

High Threshold: MIN 300 (This value must match value found in query. Default: 300 ), MAX empty

Labels (1)
Comments

Couple of questions:

1) how is this different than the Long Running Transaction alert out-of-the-box of DPA?

2) how did you come up with the value of 30 for "and last_call_et >= 30"

Thanks

Version history
Revision #:
1 of 1
Last update:
‎04-25-2014 03:21 PM
Updated by: