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 twenty-five SQL statements, preparing and dropping them each iteration through the loop, and each iteration
each of these SQL statements have 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.
When client statement caching is enabled, the max_client_statements_cached option should be set to 0 to disable client statement
caching while the request log is captured if the log will be analyzed using the tracetime.pl Perl script.