Procedure cache

Adaptive Server maintains an MRU/LRU (most recently used/least recently used) chain of stored procedure query plans. As users execute stored procedures, Adaptive Server looks in the procedure cache for a query plan to use. If a query plan is available, it is placed on the MRU end of the chain, and execution begins.

If no plan is in memory, or if all copies are in use, the query tree for the procedure is read from the sysprocedures table. The query tree is then optimized, using the parameters provided to the procedure, and placed at the MRU end of the chain, and execution begins. Plans at the LRU end of the page chain that are not in use are aged out of the cache.

The memory allocated for the procedure cache holds the optimized query plans (and occasionally trees) for all batches, including any triggers.

If more than one user uses a procedure or trigger simultaneously, there will be multiple copies of it in cache. If the procedure cache is too small, a user trying to execute stored procedures or queries that fire triggers receives an error message and must resubmit the query. Space becomes available when unused plans age out of the cache.

Adaptive Server uses the default procedure cache size (in memory pages) at start up. The optimum value for the procedure cache varies from application to application, and it may also vary as usage patterns change. Use procedure cache size to determine the current size of the procedure cache (see Chapter 5, “Setting Configuration Parameters,” in System Administration Guide: Volume 1).