Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

max_plans_cached option

Specifies the maximum number of execution plans to be stored in a cache.

Allowed values

Integer

Default

20

Scope
  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY PUBLIC OPTION Yes Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily? Yes, with SET ANY PUBLIC OPTION Yes (current connection only) No
Remarks

This option specifies the maximum number of plans cached for each connection. The optimizer caches the execution plan for queries, INSERT, UPDATE, and DELETE statements that are performed inside stored procedures, functions, and triggers. After a statement in a stored procedure, stored function, or trigger is executed several times by a connection, the optimizer builds a reusable plan for the statement.

Reusable plans do not use the values of host variables for selectivity estimation or rewrite optimizations. As a result of this behavior, the reusable plan can have a higher cost than if the statement was re-optimized. When the cost of the reusable plan is close to the best observed cost for a statement, the optimizer adds the plan to the plan cache.

The cache is cleared when you execute statements, such as CREATE TABLE and DROP TABLE, that modify the table schema. Statements that reference declared temporary tables are not cached.

Setting this option to 0 disables plan caching.