ASA SQL User's Guide
Query Optimization and Execution
How the optimizer works
Normally, the optimizer selects an access plan for a query every time the query is executed. Optimizing at execution time allows the optimizer to choose a plan based on current system state, as well as the values of current selectivity estimates and estimates based on the values of host variables. For queries that are executed very frequently, the cost of query optimization can outweigh the benefits of optimizing at execution time. For queries and INSERT, UPDATE and DELETE statements performed inside stored procedures, stored functions, and triggers, the optimizer caches execution plans between executions of the query.
After a statement in a stored procedure, stored function, or trigger has been executed several times by one connection, the optimizer builds a reusable plan for the statement. A reusable plan does not use the values of host variables for selectivity estimation or rewrite optimizations. The reusable plan may have a higher cost because of this. If the cost of the reusable plan is close to the best observed cost for the statement, the optimizer will choose to add the reusable plan to a plan cache. Otherwise, the benefit of optimizing on each execution outweighs the savings from avoiding optimization, and the execution plan is not cached.
The plan cache is a per-connection cache of the data structures used to execute an access plan. Reusing the cached plan involves looking up the plan in the cache and resetting it to an initial state. This is typically substantially faster than optimizing the statement. Cached plans may be stored to disk if they are used infrequently, and they do not increase the cache usage. The optimizer periodically re-optimizes queries to verify that the cached plan is still relatively efficient.
You can use the database or connection property QueryCachePages to determine the number of pages used to cache execution plans. These pages occupy space in the temporary file, but are not necessarily resident in memory.
You can use QueryCachedPlans statistic to show how many query execution plans are currently cached. This property can be retrieved using CONNECTION_PROPERTY to show how many query execution plans are cached for a given connection, or DB_PROPERTY can be used to count the number of cached execution plans across all connections. This property can be used in combination with QueryCachePages, QueryOptimized, QueryBypassed, and QueryReused to help determine the best setting for the MAX_PLANS_CACHED option
The maximum number of plans to cache is specified with the option setting MAX_PLANS_CACHED. The default is 20. To disable plan caching, set this option to 0.
For more information, see MAX_PLANS_CACHED option [database].