如何在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.

    • Related Articles

    • Oracle数据库监视器遇到“由于此查询组导致查询执行超时”的问题

      有的时候，Oracle数据库监视器变成不可用，提示：“由于此查询组导致查询执行超时...” 页面中显示错误信息：java.sql.SQLTimeoutException : ORA-01013 : 用户请求取消目前操作 解决方法： 管理 -- 性能轮询  -- 优化数据收集 选择 “脚本/数据库查询监视器”，把超时值改大，比如200

    • APM报由于查询组导致查询超时：TABLESPACESTATUS

      问题：APM报由于查询组导致查询超时：TABLESPACESTATUS 解决方案： 1.点击界面上的管理----性能轮询----优化数据收集----选择脚本/数据库查询监视器----把超时时间改成300秒  2.把每个监视器的查询执行超时时间设置成300秒： 3.如果方便，请把监视器删除，重新添加

    • 【APM】Oracle 查询组超时告警常用处理办法

      在某些情况下，APM 会抛出查询组超时错误，如下所示： 由于此查询组导致套接字读取超时：xxxxxxxx 错误消息：java.sql.SQLRecoverableException：IO错误：套接字读取已过时。 （或） 由于此查询组导致套接字读取超时：xxxxxxxx 错误消息：java.sql.SQLTimeoutException：ORA-01013：用户请求取消当前操作。 xxxxxxxx - 是查询组名称 解决方案： 从APM web ...