Runaway query notification

You can use the RunAwayQuery event type to monitor queries that exceed configured query timeout limits.

Note: Typically you do not aggregate this event because you should consider the performance impact of each individual runaway query.

The runaway query timeout is a limit that you can specify system-wide (for all users), or for specific groups or users. The default query timeout is unlimited for users and groups, but you can establish query timeout limits by using a system default setting, or when you create or alter users or groups. The runaway query timeout limit does not apply to the admin database user.

The following is the syntax for the event rule RunAwayQuery:
-name 'RunAwayQuery' -on no -eventType runawayQuery -eventArgsExpr '' 
-notifyType email -dst '' -ccDst '' -msg 'NPS system 
$HOST - long-running query detected at $eventTimestamp.' -bodyText 
'$notifyMsg\n\nsessionId: $sessionId\nplanId: $planId\nduration: 
$duration seconds' -eventAggrCount 0

The following table lists the arguments to the RunAwayQuery event rule. The arguments are case-sensitive.

Table 1. RunAwayQuery event rule
Arguments Description Examples
sessionId The ID of the runaway session Use these arguments for the email message.
planId The ID of the plan
duration The amount of time (in seconds) that the query was running when it exceeded its timeout.
When you specify the duration argument in the -eventArgsExpr string, you can specify an operator such as: ‘==’, ‘!=’, ‘>’, ‘>=’, ‘<’, or ‘<=’ to specify when to send the event notification. Use the greater-than (or less-than) versions of the operators to ensure that the expression triggers with a match. For example, to ensure that a notification event is triggered when the duration of a query exceeds 100 seconds, specify the -eventArgsExpr as follows:
-eventArgsExpr '$duration > 100'
If a query exceeds its timeout threshold and you added a runaway query rule, the system sends you an email that informs you how long the query ran. For example:
NPS system alpha - long-running query detected at 07-Nov-03, 15:43:49 
sessionId: 10056
planId: 27
duration: 105 seconds