oracle probe checkpoint generating large number of alarms

Document ID : KB000099236
Last Modified Date : 05/06/2018
Show Technical Document Details
Introduction:
The oracle probe is generating a large number of query time out alarms similar to the following and they are not clearing:

checkpoint 'tablespace_free' - query timed out"
Question:
Why is the oracle probe generating query time out alarms and not clearing them?
Environment:
UIM 8.51
oracle: 5.30
Answer:
If the SQL query for the checkpoint  is not executed in the configured sql_timeout interval, the probe will generate the "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 in 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, it was discovered that the oracle probe starts 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.