Plan cache

The plan cache is a repository that contains the access plans for queries that were optimized by SQE.

Access plans generated by CQE are not stored in the plan cache; instead, they are stored in SQL packages, the system-wide statement cache, and job cache. The purposes of the plan cache are to:

  • Facilitate the reuse of a query access plan when the same query is re-executed
  • Store runtime information for subsequent use in future query optimizations
  • Provide performance information for analysis and tuning

Once an access plan is created, it is available for use by all users and all queries, regardless of where the query originates. Furthermore, when an access plan is tuned, for example, when creating an index, all queries can benefit from this updated access plan. This updated access plan eliminates the need to reoptimize the query, resulting in greater efficiency.

The following graphic shows the concept of reusability of the query access plans stored in the plan cache:

This graphic is described in the next paragraph.

As shown in the previous graphic, statements from packages and programs are stored in unique plans in the plan cache. If Statement 3 exists in both SQL package 1 and SQL package 2, the plan is stored once in the plan cache. The plan cache is interrogated each time a query is executed. If an access plan exists that satisfies the requirements of the query, it is used to implement the query. Otherwise a new access plan is created and stored in the plan cache for future use.

The plan cache is automatically updated with new query access plans as they are created. When new statistics or indexes become available, an existing plan is updated the next time the query is run. The plan cache is also automatically updated by the database with runtime information as the queries are run.

Each plan cache entry contains the original query, the optimized query access plan, and cumulative runtime information gathered during the runs of the query. In addition, several instances of query runtime objects are stored with a plan cache entry. These runtime objects are the real executable objects and temporary storage containers (hash tables, sorts, temporary indexes, and so on) used to run the query.

By default the SQE Plan Cache will auto adjust from an initial threshold size of 512 MB to an internally managed maximum. Automatic management of the SQL Plan Cache Threshold Size by the system will not take effect if the plan cache threshold size is explicitly set on the system. See the SQL plan cache properties topic for more information: rzajqplancacheprops.htm
  • When processing is initiated to remove plans in the cache due to size constraint, the efficiency rating of the cache is checked. If the rating is too low, the database will automatically increase the plan cache size.
  • The plan cache auto-sizing maximum size will not exceed a small percentage of free storage on the system.
  • The plan cache auto-sizing will decrease the size if the temporary storage on the machine exceeds a certain percentage.
  • The auto-sized adjusted threshold value does not survive an IPL. The default plan cache size is used after an IPL and auto sizing begins again.
  • To reset an explicitly set plan cache size in order to allow auto-sizing to take effect, set the plan cache size to zero.

    Example: 

    CALL qsys2.change_plan_cache_size(0)

When the plan cache exceeds its designated size, a background task is automatically scheduled to remove plans from the plan cache. Access plans are deleted based upon age, how frequently it is used, and how much cumulative resources (CPU/IO) were consumed.

The total number of access plans stored in the plan cache depends largely upon the complexity of the SQL statements that are being executed. The plan cache is cleared when a system Initial Program Load (IPL) is performed.

Multiple access plans for a single SQL statement can be maintained in the plan cache. Although the SQL statement is the primary key into the plan cache, different environmental settings can cause additional access plans to be stored. Examples of these environmental settings include:

  • Different SMP Degree settings for the same query
  • Different library lists specified for the query tables
  • Different settings for the share of available memory for the job in the current pool
  • Different ALWCPYDTA settings
  • Different selectivity based on changing host variable values used in selection (WHERE clause)

Currently, the plan cache can maintain a maximum of three different access plans for the same SQL statement. As new access plans are created for the same SQL statement, older access plans are discarded to make room for the new access plans. There are, however, certain conditions that can cause an existing access plan to be invalidated. Examples of these conditions include:

  • Specifying REOPTIMIZE_ACCESS_PLAN(*YES) or (*FORCE) in the QAQQINI table or in Run SQL Scripts
  • Deleting or recreating the table that the access plan refers to
  • Deleting an index that is used by the access plan