Choose SQL and PL/SQL Timeout Values

Performance impact: Variable

You should consider the relationship between certain connection attributes when setting timeout values.

  • SQLQueryTimeout or SQLQueryTimeoutMSec: Controls how long a SQL statement runs before timing out.

    By default, SQL statements do not time out. In some cases, you may want to specify a value for either the SQLQueryTimeout or SQLQueryTimeoutMSec connection attribute to set the time limit in seconds or milliseconds within which the database should run SQL statements. (Note that this applies to any SQL statement, not just queries.)

    Both SQLQueryTimeout and SQLQueryTimeoutMsec attributes are internally mapped to one timeout value in milliseconds. If different values are specified for these attributes, only one value is retained. See SQLQueryTimeout and SQLQueryTimeoutMSec in the Oracle TimesTen In-Memory Database Reference.

  • PLSQL_TIMEOUT: Controls how long a PL/SQL block runs before timing out.

    By default, PL/SQL program units (PL/SQL procedures, anonymous blocks and functions) are allowed to run for 30 seconds before being automatically terminated. In some cases, you may want to modify the PLSQL_TIMEOUT connection attribute value to allow PL/SQL program units additional time to run. You can also modify this attribute with an ALTER SESSION statement during runtime.

    See PLSQL_TIMEOUT in the Oracle TimesTen In-Memory Database Reference.

  • TTC_Timeout: Controls how long a TimesTen client waits for a response from the TimesTen Server when the client has requested the server to run a SQL statement or PL/SQL block.

  • TTC_ConnectTimeout: Controls how long the client waits for a SQLDriverConnect or SQLDisconnect request. It overrides the value of TTC_Timeout for those requests.

    See Choose Timeout Connection Attributes for Your Client.

If you use a TimesTen client/server, then SQLQueryTimeout (or SQLQueryTimeoutMSec) and PLSQL_TIMEOUT (relevant for PL/SQL) should be set to significantly lower values than TTC_Timeout, to avoid the possibility of the client mistaking a long-running SQL statement or PL/SQL block for a non-responsive server.

If you use PL/SQL, the relationship between SQLQueryTimeout (or SQLQueryTimeoutMSec) and PLSQL_TIMEOUT depends on how many SQL statements you use in your PL/SQL blocks. If none, there is no relationship. If the maximum number of SQL statements in a PL/SQL block is n, then PLSQL_TIMEOUT should presumably equal at least n x SQLQueryTimeout (or n x 1000 x SQLQueryTimeoutMSec), including consideration of processing time in PL/SQL.

Note:

If SQLQueryTimeout (or SQLQueryTimeoutMSec) and PLSQL_TIMEOUT are not set sufficiently less than TTC_Timeout, and the client mistakes a long-running SQL statement or PL/SQL block for a non-responsive server and terminates the connection, the server will cancel the SQL statement or PL/SQL block as soon as it notices the termination of the connection.