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 re-optimize the query, resulting in greater efficiency.

The following graphic shows the concept of re-usability of the query access plans stored in the plan cache:

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. The size of these objects depends largely on the complexity of the SQL statements that are being executed. It is the size of these objects that is counted when calculating the plan cache size.

The size of the plan cache may be controlled by either a system-managed auto-sizing algorithm or by an explicitly set value. By default, the system will use automatic sizing, but this can be disabled by setting the SQL Plan Cache Threshold Size. See the SQL plan cache properties topic for more information: SQL plan cache properties

When auto-sizing is enabled, the system will begin after each IPL with a default plan cache size of 512 MB. As queries run, the associated plans are placed into the plan cache. Once the plan cache exceeds the current limit set by auto-sizing, the system will evaluate the current hit ratio for the cache. This is the ratio of the number of times a query could re-use a plan from the cache compared to the number of times a query was run. For example, if ten queries were run and nine of the queries used plans from the plan cache while the other one required a full optimization, the hit ratio is 90%. If the current hit ratio equals or exceeds the SQL Plan Cache Target Ratio ( see the SQL plan cache properties topic for more information: SQL plan cache properties), the system considers the plan cache to be operating at an efficient size and will not adjust the size of the cache.

If the current hit ratio is below the target for the plan cache and no other storage constraints exist, the system will automatically increase (up to the Maximum Plan Cache Size for Auto-sizing) the size of the plan cache. The plan cache will grow incrementally, allowing more plans to be stored, until the current hit ratio meets the target ratio.

Once the target hit ratio has been achieved, the determined size of the cache is maintained by an automatically scheduled background task. The purpose of this task is to go through the plan cache and to remove plans when the cache grows too large. This task will remove access plans based upon age, how frequently it is used, and how much cumulative resources (CPU/IO) were consumed.

Each plan cache entry may also have query runtime objects associated with it. 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. Although these objects are not included the in the plan cache size calculation, they may indirectly affect and be affected by the plan cache size. This is because, in addition to honoring the determined plan cache size, the system also seeks to keep the total temporary storage usage for inactive, cached plans within an internally determined threshold. Unlike the plan cache size calculation, this temporary storage calculation considers both the cached plans and the associated runtime objects. If this temporary storage calculation exceeds a system determined percentage of the system auxiliary storage pool (ASP) or if the system storage lower limit (defined by QSTGLOWLMT) is surpassed, the system considers the plan cache to be using excessive temporary storage.

To reduce the excessive temporary storage, the automatically scheduled background task will begin by removing runtime objects from cached plans. This will continue until the plan cache’s temporary storage usage is within the system’s constraints, and this will happen even if all the cached plan entries themselves fit within the determined plan cache size. If temporary storage usage remains excessive even after the runtime objects are removed, the system will begin a process of incrementally reducing the plan cache size and will continue this reduction until it reaches the minimum value of 512 MB or until the plan cache temporary storage usage is no longer excessive.

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

On systems with hardware-based compression acceleration support, the plan cache may apply compression to further reduce the temporary storage required to store cached plans. Eligible systems include partitions running on a Power10 processor-based system where the partition has access to Nest Accelerator (NX) resources. When this capability is present, the plan cache will compress optimized query plans that are larger than 256 KB. This compression is automatic and transparent and requires minimal additional CPU capacity, due to the use of hardware acceleration.