ASA Database Administration Guide
  Database Options
    Alphabetical list of options

MAX_PLANS_CACHED option [database]

Function 

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

Allowed values 

Integer

Scope 

Can be set for an individual connection or for the PUBLIC group. Takes effect immediately. DBA permissions are required to set this option for the PUBLIC group.

Default 

20

See also 

Access plan caching

Connection-level properties

Description 

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, 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.