The statement cache allows Adaptive Server to store the text of ad hoc SQL statements. Adaptive Server compares a newly received ad hoc SQL statement to cached SQL statements and, if a match is found, 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. The syntax is:
sp_configure "statement cache size", size_of_cache
where size_of_cache is the size, in 2K pages. For example, to set your statement cache to 5000 2K pages, enter:
sp_configure "statement cache size", 5000
See “statement cache size configuration parameter” for more information.
Consider the following when you configure memory for the statement cache:
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. In the example above, the size of the procedure cache memory pool is increased by 5000 2K pages.
statement cache size limits the amount of procedure cache memory by cached SQL text and plans. That is, Adaptive Server cannot use more memory for the statement cache than you have configured with the statement cache size configuration parameter.
All procedure cache memory, including that memory allocated by the statement cache size configuration parameter, is available for stored procedures, which may replace cached statements on an LRU basis.
Increase the max memory configuration parameter by the same amount configured for the statement cache. That is, if you have configured the statement cache size to be 100 2K pages, increase max memory by the same amount.
If you have configured the statement cache with 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 databases configuration parameter. To estimate how many cached SQL statements to allow for, see “Statement cache sizing”.
| Copyright © 2004. Sybase Inc. All rights reserved. |
|
|
