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;
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;
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.