The statement cache lets Adaptive Server compare a newly received ad hoc SQL statement to cached SQL statements. If a match is found, Adaptive Server uses the plan cached from the initial execution. In this way, Adaptive Server 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.
If you deallocate or reduce the amount of memory for
the statement cache, the original memory allocated is not released
until you restart Adaptive Server.
The syntax is as follows, where size_of_cache is the size, in 2K pages:
sp_configure "statement cache size", size_of_cache
For example, to set your statement cache to 5000 2K pages, enter:
sp_configure "statement cache size", 5000
See Chapter 5, “Setting Configuration Parameters,” in the System Administration Guide: Volume 1.
When you configure memory for the statement cache, consider:
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. Adaptive Server 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.
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. To estimate how many cached SQL statements to allow for, see “Statement cache sizing”.
