Received large no of query timed out! alert for oracle database

book

Article ID: 196291

calendar_today

Updated On:

Products

NIMSOFT PROBES DX Infrastructure Management

Issue/Introduction

In our environment we deployed the oracle probe for monitoring database and we received large no of query timed out! alerts frequently. Could you please let me know how to suppress this alerts.

Environment

Release : 9.2.0

Component : UIM - ORACLE

Resolution

Increase the probe/profile/query timeout settings.

Also you can create a nas preprocessing rule for now, to 'exclude' (delete) the alarms based on the alarm message, e.g., use an an alarm message filter, something like,

   /.*query timed out.*/

     or

   /.*timed out.*/

If the SQL query for the checkpoint is not executed in the configured sql_timeout interval, the probe will generate a "query timed out" alarm.
The next time the probe executes the same checkpoint from the same profile, it first clears the "query timed out" alarm, but if the query does not complete within the configured sql_timeout interval, it will raise this alarm again.

To prevent this alarm, make sure that you have configured the sql_timeout interval for the alarm to be large enough to allow the query to complete, to prevent the probe from generating this alarm.

Note that the same query to the same database using the same user login credentials configured in the probe from a utility like sqlplus executed on the same robot where the oracle probe is deployed may complete much faster than the same query executed from the probe.  This query  will take time to complete when executed from the oracle probe because it executes the query through an Oracle based API instead of directly executing the query.  The data Flow is:

Probe -> API -> Checking Connection -> Executing the query

For this reason, the SQL execution time for the probe will always be greater than the time the query takes when directly executed.

Also, the oracle probe may start to behave unexpectedly if there are more than 30 active profiles defined in the probe configuration. In this case, it is highly recommended that a second oracle probe be deployed to another robot and the active profiles from the first oracle probe be split over the 2 oracle probes keeping the maximum number of active profiles under 30.