How to clear "Query Execution Timed Out" (or) "Socket Read Timed Out" error alerts during Oracle monitor data collection?

Applications Manager may report a Query Execution Timed Out or Socket Read Timed Out alert when an Oracle query takes longer than the configured timeout. 

Symptoms

You may see one of the following messages during Oracle monitor data collection:

Socket read timed out due to this query group(s): xxxxxxxx Error Message: java.sql.SQLRecoverableException: IO Error: Socket read timed out.

or:

Query execution timed out due to this query group(s): xxxxxxxx Error Message: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation.

Here, xxxxxxxx represents the query group name.

On this page

• General Timeout Solution
• INSTANCESTATUS / TABLESPACESTATUS Timeout Solution
• OracleAlertLogList Timeout Solution
• Additional Information

Solution

INSTANCESTATUS / TABLESPACESTATUS Query Group Timeout

The INSTANCESTATUS / TABLESPACESTATUS timeout can occur when queries against the Oracle data dictionary view DBA_FREE_SPACE take a long time to execute.

1. Check the Recycle Bin

Run the following query in the corresponding Oracle database:

SQL> SELECT count(*) FROM dba_recyclebin;

2. Purge the Recycle Bin

If the recycle-bin count is high, purge the recycle bin with the help of your Oracle DBA:

SQL> PURGE dba_recyclebin;

Perform the purge only after confirming with the customer/Oracle DBA that the recycle-bin objects can be permanently removed.

3. Gather Dictionary and Fixed Object Statistics

With the customer's permission and with the help of the Oracle DBA, gather dictionary and fixed-object statistics:

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

OracleAlertLogList Query Group Timeout

If the query group reported in the alert is OracleAlertLogList, the slowness may be caused by a large Oracle alert-log history. The query used by this query group is:

SELECT DECODE(MESSAGE_LEVEL,1,'CRITICAL',2,'SEVERE',8,'IMPORTANT',16,'NORMAL',MESSAGE_LEVEL) AS MESSAGE_LEVEL,
RECORD_ID,
ORIGINATING_TIMESTAMP,
HOST_ID AS HOST_ADDRESS,
MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE TRIM(COMPONENT_ID) = 'rdbms'
AND MESSAGE_TEXT LIKE '%ORA-%'
AND originating_timestamp > (SYSDATE - 15/1440);

SELECT DECODE(MESSAGE_LEVEL,1,'CRITICAL',2,'SEVERE',8,'IMPORTANT',16,'NORMAL',MESSAGE_LEVEL) AS MESSAGE_LEVEL,
RECORD_ID,
ORIGINATING_TIMESTAMP,
HOST_ID AS HOST_ADDRESS,
MESSAGE_TEXT
FROM v$appman_alertlog_ext
WHERE TRIM(COMPONENT_ID) = 'rdbms'
AND MESSAGE_TEXT LIKE '%ORA-%'
AND originating_timestamp > (SYSDATE - 15/1440);

Step 1: Check the ADR alert repository

Run the following commands in ADRCI:

adrci> show homes

adrci> set homepath diag/rdbms/<dbname>/<instance>

adrci> show alert -tail 1

Replace the following placeholders with the appropriate values for your Oracle environment:

• <dbname> – Oracle database name.
• <instance> – Oracle instance name.

Step 2: Check the number of alert files at the OS level

At the operating-system level, run:

ls $ADR_BASE/diag/rdbms/<dbname>/<instance>/alert | wc -l

Step 3: Purge old ADR alert data

With the help and approval of your Oracle DBA, purge old alert-log data using:

adrci> purge -age 43200 -type ALERT

Adjust the above retention period according to your organization's Oracle ADR retention requirements. Do not perform the purge if older alert-log data must be retained for auditing, troubleshooting, or other operational requirements.

Step 4: Re-run the OracleAlertLogList query

After the ADR purge is completed, execute the query shown above directly on the Oracle database and check its execution time.

Additional Information

• ADR purging should be performed with the knowledge and approval of the Oracle DBA.
• The recommended retention period may vary depending on the customer's operational and audit requirements.
• If the query remains slow even after reducing the volume of historical ADR alert data, share the query execution details and relevant Applications Manager logs with ManageEngine Support.