The statement cache lets SAP ASE compare a newly received ad hoc SQL statement to cached SQL statements. If a match is found, SAP ASE uses the plan cached from the initial execution.
In this way, SAP ASE does not have to recompile SQL statements for which it already has a plan.
The statement cache is a server-wide resource, which allocates and consumes memory from the procedure cache memory pool. Set the size of the statement cache dynamically using the statement cache size configuration parameter.
Versions 15.7 ESD #2 and later – allow you to store individual SQL statements up to 2MB (for a 64-bit machine) in the statement cache after you increase the statement cache size and max memory configuration parameters.
Versions earlier than 15.7 ESD #2 – have a 16K limit on the size of individual statements stored in the statement cache, even if you configured statement cache size to a larger size.
Use show_cached_text to display the statement cache’s SQL query text if it is less than 16K. However, if the SQL query text is larger than 16K, show_cached_text truncates the SQL query text, even if the full text is available in the statement cache.
Use show_cached_text_long to display SQL query text larger than 16K. show_cached_text_long displays SQL query text up to 2MB in size.
sp_configure "statement cache size", size_of_cache
sp_configure "statement cache size", 5000
See System Administration Guide: Volume 1 > Setting Configuration Parameters.
The amount of memory allocated for the procedure cache memory pool is the sum of the statement cache size and the procedure cache size configuration parameters. The statement cache memory is taken from the procedure cache memory pool.
statement cache size limits the amount of procedure cache memory available for cached SQL text and plans. SAP ASE cannot use more memory for the statement cache than you have configured with the statement cache size configuration parameter.
@@nestlevel contains the nesting level of current execution with the user session, initially 0. Each time a stored procedure or trigger calls another stored procedure or trigger, the nesting level is incremented. The nesting level is also incremented by one when a cached statement is created. If the maximum of 16 is exceeded, the transaction aborts.
@@procid returns the ID of the currently executing stored procedure, and returns 0 for SQL statement. Once you enable the statement cache, the value fo @@procid is the value for the lightweight procedure.
All procedure cache memory, including the memory allocated by the statement cache size configuration parameter, is available for stored procedures, which may replace cached statements on an least recently run (LRU) basis.
Increase the max memory configuration parameter by the same amount configured for the statement cache. For example, if you have initially configured the statement cache size to 100 2K pages, increase max memory by the same amount.
If you have used the statement cache size configuration parameter, you can disable and enable the statement cache at the session level with set statement cache. By default, the statement cache is on at the session level if it has been configured at the server level.
Because each cached statement consumes one object descriptor, you must also increase the number of object descriptors accordingly, using the number of open objects configuration parameter.