5 Replies Latest reply on Sep 16, 2015 3:46 AM by Sweeyav

    Timed out SQL Query on Oracle 10g

    msarvarie Hot Shot
      Hi,

      We would like to monitor a specific 10g table but the select being quit long to execute it often times out with the following agent stack trace:

      2011-06-09 11:00:05,909 ERROR [pool-1-thread-1] [ScheduleThread] Metric 'sqlquery:SELECT COUNT(MSGID) FROM Q WHERE SHIPPINGDONE%3D0:availability:jdbcDriver=oracle.jdbc.Or
      acleDriver,jdbcUrl=jdbc%3Aoracle%3Athin%3A@xxx.xxx.xx.xxx%3A1530%3AXXXXX1,jdbcUser=xxx,jdbcPassword=***' took too long to run (5833ms), cancelled (result=true)
      2011-06-09 11:00:05,915 DEBUG [pool-6-thread-1] [SQLQueryMeasurementPlugin] AVAIL_DOWN
      java.sql.SQLException: Io exception: Socket read timed out
              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
              at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:968)
              at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)
              at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)
              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
              at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1687)
              at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653)
              at org.hyperic.hq.product.JDBCMeasurementPlugin.getQueryValue(JDBCMeasurementPlugin.java:283)
              at org.hyperic.hq.plugin.sqlquery.SQLQueryMeasurementPlugin.getValue(SQLQueryMeasurementPlugin.java:371)
              at org.hyperic.hq.product.MeasurementPluginManager.getPluginValue(MeasurementPluginManager.java:176)
              at org.hyperic.hq.product.MeasurementPluginManager.getValue(MeasurementPluginManager.java:274)
              at org.hyperic.hq.measurement.agent.server.ScheduleThread.getValue(ScheduleThread.java:439)
              at org.hyperic.hq.measurement.agent.server.ScheduleThread.access$1400(ScheduleThread.java:74)
              at org.hyperic.hq.measurement.agent.server.ScheduleThread$MetricTask.run(ScheduleThread.java:505)
              at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
              at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
              at java.util.concurrent.FutureTask.run(FutureTask.java:123)
              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
              at java.lang.Thread.run(Thread.java:595)
      2011-06-09 11:00:05,915 WARN  [pool-6-thread-1] [ScheduleThread] Collection of metric: 'SQL Query:sqlquery:SELECT COUNT(MSGID) FROM Q WHERE SHIPPINGDONE%3D0:availability:
      jdbcDriver=oracle.jdbc.OracleDriver,jdbcUrl=jdbc%3Aoracle%3Athin%3A@xxx.xxx.xx.xxx%3A1530%3AXXXXX1,jdbcUser=xxx,jdbcPassword=***' took: 5839ms
      2011-06-09 11:00:05,916 DEBUG [pool-6-thread-1] [ScheduleThread] [2:11133:AVAILABILITY] Metric='sqlquery:SELECT COUNT(MSGID) FROM Q WHERE SHIPPINGDONE%3D0:availability:jd
      bcDriver=oracle.jdbc.OracleDriver,jdbcUrl=jdbc%3Aoracle%3Athin%3A@192.168.50.100%3A1530%3AWANAP1,jdbcUser=mmg,jdbcPassword=***' -> 0 timestamp=1307610005915
      2011-06-09 11:00:05,916 ERROR [pool-6-thread-1] [ScheduleThread] Metric Value not found while processing Metric 'SQL Query:sqlquery:SELECT COUNT(MSGID) FROM Q WHERE SHIPP
      INGDONE%3D0:Query:jdbcDriver=oracle.jdbc.OracleDriver,jdbcUrl=jdbc%3Aoracle%3Athin%3A@xxx.xxx.xx.xxx%3A1530%3AXXXXX1,jdbcUser=xxx,jdbcPassword=***'
      org.hyperic.hq.product.MetricNotFoundException: Query failed for Query, while attempting to issue query SELECT COUNT(MSGID) FROM Q WHERE SHIPPINGDONE=0:Closed Connection
              at org.hyperic.hq.product.JDBCMeasurementPlugin.getQueryValue(JDBCMeasurementPlugin.java:334)
              at org.hyperic.hq.plugin.sqlquery.SQLQueryMeasurementPlugin.getValue(SQLQueryMeasurementPlugin.java:364)
              at org.hyperic.hq.product.MeasurementPluginManager.getPluginValue(MeasurementPluginManager.java:176)
              at org.hyperic.hq.product.MeasurementPluginManager.getValue(MeasurementPluginManager.java:274)
              at org.hyperic.hq.measurement.agent.server.ScheduleThread.getValue(ScheduleThread.java:439)
              at org.hyperic.hq.measurement.agent.server.ScheduleThread.access$1400(ScheduleThread.java:74)
              at org.hyperic.hq.measurement.agent.server.ScheduleThread$MetricTask.run(ScheduleThread.java:505)
              at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
              at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
              at java.util.concurrent.FutureTask.run(FutureTask.java:123)
              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
              at java.lang.Thread.run(Thread.java:595)
      Caused by: java.sql.SQLException: Closed Connection
              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
              at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnection.java:722)
              at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnection.java:698)
              at org.hyperic.hq.product.JDBCMeasurementPlugin.getQueryValue(JDBCMeasurementPlugin.java:282)
              ... 12 more


      Is there a way to modify this behavior?

      Agent & Server both in 4.5.1.3.

      Thx