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.
Setup parameter QueryThreshold in sys.odbc.ini.
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?
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).