max_client_statements

Integer, 0 to 100

Can be set for an individual connection or for the PUBLIC group. Changing the value takes effect immediately.

10

Client statement caching reduces database requests and statement prepares when identical SQL statements are prepared multiple times. When the same SQL text is prepared and dropped repeatedly, the client caches the statement, leaving it prepared on the database server, even after it has been dropped by the application. Caching the statement saves the database server the extra work of dropping and re-preparing the statement. If a schema change occurs, a database option setting changes, or a DROP VARIABLE statement is executed, the prepared statement is dropped automatically and is prepared again the next time the SQL statement is executed, ensuring that a cached statement that could cause incorrect behavior is never reused.

This option specifies the maximum number of statements that can remain prepared (cached). Cached statements are not counted toward the max_statement_count resource governor.

The setting of this option applies to connections made using embedded SQL, ODBC, OLE DB, ADO.NET, and the SQL Anywhere JDBC driver. It does not apply to Sybase Open Client, jConnect, or HTTP connections.

Setting this option to 0 disables client statement caching. Increasing this value has the potential to improve performance if the application is repeatedly preparing and dropping more than ten of the same SQL statements. For example, if an application loops through 25 SQL statements, preparing and dropping them each iteration through the loop, and in each iteration each of these SQL statements has the exact same text, setting this option to 25 may improve performance.

Increasing the value of this option increases memory use on the client and places more cache pressure on the database server. If a significant number of cached statements cannot be reused because of schema changes or option settings, statement caching is disabled automatically for the connection. If statement caching is automatically turned off, the client periodically turns statement caching on again to re-evaluate the decision and determine whether re-enabling statement caching would be beneficial.

Client statement caching could deliver unexpected results in the following situation:

A statement is prepared and described and its describe returns that the statement has no result.

CREATE OR REPLACE FUNCTION test() RETURNS INT BEGIN RETURN 1;
   END;
  CALL test();

A DDL statement causes the same statement text (in this example, the CALL statement) to now return a result set on the same connection. For example:

CREATE OR REPLACE PROCEDURE test() BEGIN SELECT 2; 
 END;
CALL test();

When client statement caching is enabled, the second CALL test() statement is described incorrectly.

If the log is being analyzed using the tracetime.pl Perl script, the max_client_statements_cached option should be set to 0 to disable client statement caching while the request log is captured.

max_client_statements_cached option

Allowed values

Scope

Default

Remarks

See also