This discussion is archived
2 Replies Latest reply: Apr 10, 2012 4:42 AM by Molchanov RSS

Setup "QueryThreshold" parameter

Molchanov Newbie
Currently Being Moderated
Hi!
I need to control time of execution SQL.
Find parameter QueryThreshold and try setup it.

In doc. says :

QueryThreshold

Use this attribute to write a warning to the support log and throw an SNMP trap when the execution time of a SQL statement exceeds the specified value. For queries executed by the replication agent, see Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. You cannot set a query threshold for a SQL statement that is executed by the cache agent. The value of QueryThreshold applies to all connections. It applies to all SQL statements except those executed by the replication agent or the cache agent.

The value of this attribute can be any integer equal to or greater than 0. The default value is 0. A value of 0 indicates that no warning is issued. The unit is seconds.

http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e13069/attribute.htm#autoId50

Setup parameter QueryThreshold in sys.odbc.ini.

Testing...

If I connect through ttIsqlCS and execute SQL that exceeded the QueryThreshold I get a error :
+6111: SQL Query has reached its timeout limit and has been terminated+
The command failed.
and disconect from session.

But, nothing about disconnect client connection when SQL statement execution exceeded the QueryThreshold do not says in doc.
With direct connect (with ttIsql ) all working fine, wrote warning message to log file and do not disconnect session.

Can anyone explain me please why it works so or how to control execution time of SQL without disconecting client?
  • 1. Re: Setup "QueryThreshold" parameter
    ChrisJenkins Guru
    Currently Being Moderated
    What value did you set for QueryThreshold? How long did the query actually run for? Had you set any non-default values for SQLQueryTimeout or TTC_TIMEOUT?

    For client/server connections (but not for direct connections) there is a relationship between QueryThreshold, SQLQueryTimeout and TTC_TIMEOUT. Essentially, you must ensure that TTC_TIMEOUT is set larger than QueryThreshold / SQLQueryTimeout or that tTC_TIMEOUT is set to 0 (no timeout). By default (i.e. if not overridden), TTC_TIMEOUT=60 seconds and SQLQueryTimeout=59 seconds.

    Thge mesage you received (6111) is as a result of SQLQueryTimeout and TTC_TIMEOUT firing.

    Note that QueryThreshold does not control the execution time of a query - it simply logs a message and/or generates an SNMPO trap if any application query exceeds this limit. If you want to place an actual limit on the time a SQL operation (not just a query) may execute for then use SQLQueryTimeout and ensure that TTC_TIMEOUT is set sifgnificantly higher than the value used for SQLQueryTimeout since there is some 'fuzziness' around the firing of SQLQueryTimeout (e.g. if you set it to 59 then it may not fire until a query has run for 60 or even 61 seconds, maybe longer).

    Chris
  • 2. Re: Setup "QueryThreshold" parameter
    Molchanov Newbie
    Currently Being Moderated
    I find where I mistake!
    TTC_Timeout parameter raise this error )))

    LOBZIK! ))

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points