如何在Oracle监控器数据收集期间清除“查询执行超时”错误警报？

如何在Oracle监控器数据收集期间清除“查询执行超时”错误警报？

Applications Manager throws the query timed out error in some scenarios as below:  
Query execution 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.
xxxxxxxx - is the query group name

Solution:  

From the Applications Manager GUI, Go to respective Oracle Monitor's details page -> Monitor Actions -> Edit Monitor -> Increase the seconds mentioned in "Query Execution Timeout" field.

If the query group reported is INSTANCESTATUS or TABLESPACESTATUS and If the alert still continues even after performing the above step then check the below KB and perform the steps in the corresponding Oracle Database with the help of your Oracle DBA.

For other query group (s), reach out to our support (appmanager-support@manageengine.com) with the latest support information file generated with print all logs enabled from Applications Manager. 

This issue occurs on INSTANCESTAUS/TABLESPACESTATUS because the queries against the dictionary table DBA_FREE_SPACE were taking a long time to execute. 

(I) Recommendation of Oracle Support for "Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)" states the following reasons:  
  1. In release 10g, the view DBA _FREE_SPACE was modified to also include objects in the recycle bin. 
  2. A large number of objects in the recycle bin can slow down queries on DBA _FREE_SPACE. This is normal behavior. 
  3. For release 11g, the view DBA _FREE_SPACE does not contain a hint which in case there are only a few objects in the recycle bin, you may want to gather underlying stats of tables/dictionaries to get better performance. 
Run the below query and check count in recycle bin: 
  • SQL> SELECT count(*) FROM dba_recyclebin; 
If the count is higher, then purge the recycle bin using below command: 
  • SQL> PURGE dba_recyclebin; 

(II) Next recommendation of Oracle Support for "How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects (Doc ID 457926.1)" to gather dictionary and fixed table stats (with customers permission). 
  • SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; 
  • SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; 

For multi-tenant enabled Oracle, the container database and the pluggable databases require independent purge.