SQL plan cache properties

The Plan Cache tab of the SQL Performance Center in IBM® i Access Client Solutions (ACS) shows high-level information about the SQL plan cache and overall query activity. This information includes cache size, number of plans, number of full opens and pseudo-opens that have occurred.

This information can be used to view overall database activity. If tracked over time, it provides trends to help you better understand the database utilization peaks and valleys throughout the day and week.

Several Plan Cache Properties can be changed by right-clicking a property and selecting Edit Value.

The following terms are used in regards to plan cache properties:
  • Active Queries – queries that are currently open or in pseudo close mode.
  • Full Open – describes a query run that requires a cursor to be built before the query executes and return rows. A query that reuses a cursor is called a Pseudo Open.
  • Unique Queries – unique SQL query statements. For the plan cache, this is uniqueness once tables are resolved to their schemas.
  • Hit Ratio – the percentage of time that the query optimizer, when searching the plan cache for a plan, finds an existing plan for the query.
  • Target Hit Ratio – the hit ratio percentage that the database tries to achieve by adjusting the plan cache’s size. A larger size means plans stay in the cache longer and are therefore more likely to be found and used for future runs of the query.
  • Job Scoped – queries that reference tables or indexes that reside in the job’s QTEMP library. This includes, for example, global temporary tables. By definition these job scoped plans and runtime objects cannot be reused across jobs.
  • Temporary Runtime Objects – the actual runtime executable objects used to process a query. These include the execution tree (ROQ), hash tables, sorted lists, lists, and buffers. A certain number of these objects are cached with the query plan in the cache so they can be reused.
  • Longest Runs -  information kept about the longest running instances of a query.
  • Activity Thresholds – highest or largest values that are tracked by the database.
  • …Since Start – the amount of activity since the cache was created (IPL).
Plan Cache Properties are divided into these main types:
  • Summary and Usage – information about plan usage, query usage and current conditions for the plan cache and queries.
  • Configuration – properties that can be adjusted by the user.
  • Temporary Index Summary – information about the use of maintained temporary indexes (MTIs).
  • Activity Threshold – tracked ‘high water mark’ of several key indicators.
  • Plan compression statistics (where hardware support is available) – information about the use of compression to reduce the temporary storage needed for caching plans.
  • Query Supervisor - information about Query Supervisor usage.

To view the Plan Cache properties, select the SQL Performance Center from the main ACS window or from the Tools menu of any ACS window.

The first tab in the SQL Performance Center window that appears will display all of the Plan Cache properties. Changes to configurable properties may be made by clicking the Change Configuration... button.

This SQL plan cache properties graphic is described in the previous paragraph
Table 1. Plan Cache Properties
Plan Cache Property Description
Time Of Summary Timestamp of when the properties were collected.
Plan Cache Creation Time Timestamp of when the plan cache was created (IPL)
   
Active Query Summary  
Number of Currently Active Queries Number of queries that are currently open or in pseudo close mode.
Number of Queries Run Since Start Shows the total number of SQL queries run since IPL
Note: This value includes job scoped queries.
Number of Query Full Opens Since Start Number of queries run since IPL that required a cursor to be built before the query executed and returned rows.
   
Plan Usage Summary  
Current Number of Plans in Cache Current total number of plans in the plan cache
Total Number of Plans Built Since Start Number of plans built since IPL. This includes the plans that have been pruned from the plan cache.
Total Number of SMP Plans Built Since Start The number of plans built since IPL that run with SMP parallel processing.
Total Number of Queries with Runtime Adjusted SMP Degree Total number of queries that had their runtime degree adjusted due to exceeding the maximum system CPU usage set by QAQQINI parameter PARALLEL_MAX_SYSTEM_CPU.
Total Number of Unique Queries Since Start This value reflects the total number of unique statements (SQL queries) run since IPL Note: This value includes unique job scoped queries
Current Plan Cache Size Current size in MB of the plan cache. This does not include the size of cached temporary runtime objects.
Current Plan Cache Size Threshold The current maximum allowed size of the plan cache.
This property is configurable.
  • *AUTO indicates that the database manager will manage the maximum size of the plan cache.
  • A user specified value between 50 and 51200. Size is specified in MB.
Maximum Plan Cache Size For AutoSizing If AutoSizing is active, the maximum plan cache size.
This property is configurable if the Current Plan Cache Size Threshold is *AUTO.
  • *DEFAULT(nn) - The database manager determines, at IPL, the maximum size that the plan cache can grow to under autosizing. Only applicable if Size Threshold is set to *AUTO. The database determined size is shown in parentheses.
  • nn – A user specified size between 50 and 51200. Size is specified in MB. While supported, it should rarely be changed from *DEFAULT.
  • *DISABLED - Indicates that plan cache auto sizing has been disabled.
Current Plan Cache Hit Ratio The percentage of time the query optimizer found a matching plan in the plan cache.

This value indicates the efficiency of the plan cache. The higher the percentage the better.
Target Plan Cache AutoSize Hit Ratio This is the target hit ratio percentage that the database manager tries to meet by adjusting the plan cache size.
This property is configurable if the Current Plan Cache Size Threshold is *AUTO.
  • DEFAULT(nn) – The database manager sets the target hit ratio. The database determined ratio is shown in parentheses.
  • nn – percentage from 1 to 99. While supported, it should rarely be changed from *DEFAULT.
  • *DISABLED - Indicates that plan cache auto sizing has been disabled.
Total Number of Plan Cache Autosizing Adjustments If autosizing is active, the number of times the plan cache autosized. This includes both plan cache increase and decrease.
Last Plan Cache AutoSizing Adjustment If autosizing is active, the timestamp when the plan cache was last autosized.
Last Autosizing Limited Due to Temporary Storage If autosizing is active, the timestamp of when the plan cache last attempted to increase the size of the plan cache but was unable to do so because the temporary storage used on the system exceeded limits.
Current Number of Job Scoped (QTEMP) Plans Current number of plans in the plan cache that for queries that reference tables or indexes that reside in the job’s QTEMP library. This includes, for example, global temporary tables. By definition these job scoped plans and runtime objects cannot be reused across jobs.
Total Number of Job Scoped (QTEMP) Plans Built Since Start Total number of plans built for queries that reference tables or indexes that reside in the job’s QTEMP library.
Total Number of Unique Queries With Job Scoped (QTEMP) References Since Start This value reflects the total number of unique statements (SQL queries) referencing temporary files that have been run since IPL.
Total Times Plans Used from Cache Total number of plans that were reused from the plan cache. (i.e. Plans that did not require a reoptimization).
Total Plans Removed Number of plans removed from the plan cache during optimization. Reasons that a plan may be removed during optimization include the following, but not limited to:
  • plan no longer valid due to table and index changes
  • plan out of date due to changes in the number of rows in the table
  • more than 3 plans for the query exist in the plan cache
  • internal changes due to PTF applies or other system level changes
Total Plans Pruned Total number of plans the plan cache pruner task removed.
Number of Times Plan Cache Pruned Number of times that the plan cache pruner task ran and removed plans from the plan cache.
Time Plan Cache was Last Pruned The timestamp of when the plan cache pruner task last ran.
Current Number of Temporary Runtime Objects Stored in Cache Current Number of Temporary Runtime Objects Stored in Cache
Current Total Size of Temporary Runtime Objects stored in Cache Current Total Size of Temporary Runtime Objects stored in Cache
Maximum Number of Temporary Runtime Objects Stored Per Plan Maximum number of Temporary Runtime Objects stored per plan.
This property is configurable.
  • *DEFAULT(nn) -  database determines the maximum number of runtime objects (ROQs) to keep per plan. The database determined number is shown in parentheses.
  • nn – A user specified value between 1 and 50 that is the maximum number of runtime objects to keep per plan. A runtime ‘object’ is all the runtime constructs (except the cursor) used to execute the query. It includes the ROQ, hash tables, sorts, etc… The database will automatically clear big hash tables and sorts of data contents (leaving only their shell) before storing them with the plan. However, smaller hashes and sorts will retain their contents. Setting this value smaller will lessen the temporary storage usage on the machine. Setting this value higher can improve performance by having more runtime objects ready to go during full opens rather than having to build them.
Number of Plans Rebuilt due to AQP Number of plans rebuilt to AQP.
Number of Query Mapping Errors Since Start This is the number of SQE query mapping errors that have occurred since the last IPL. While a number of mapping errors greater than 0 does not indicate a problem, a significant number of mapping errors can negatively affect performance and may require further investigation.
Maximum Number of Longest Runs Allowed Per Plan The Maximum Number of Longest Runs Allowed Per Plan determines how many longest runs are maintained per plan.
This property is configurable.
  • *DEFAULT(nn) -  The database manager determines the maximum number of longest run entries to keep per plan. The database determined number is shown in parentheses.
  • nn – A user specified value between 1 and 50 indicating the maximum number of longest runs information to keep per plan
Note: These can be seen by bringing up a show statements of the live cache, right clicking, and selecting Longest Runs
   
Temporary Index Summary  
Total Number of Temporary Indexes Created Total number of SQE created temporary indexes (MTIs) since IPL.
Current Number of Temporary Indexes Current number of SQE created temporary indexes (MTIs) on the system.
Current Total Size of Temporary Indexes The total size of all SQE created temporary indexes (MTIs) currently on the system.
Total Number of Non-resusable Temporary Indexes Created Total number of Temporary Indexes (MTIs) that were created that could not be shared with other queries.
Current Number of non-reusable Temporary Indexes The current number of non-resusable MTIs on the system.
   
Plan Cache Activity Thresholds

The Activity Thresholds group of properties tracks the upper thresholds of activity for both plans and query activity. The values represent the maximum values achieved since the Threshold Start Time. Each threshold shows both the maximum value and the timestamp of when that maximum was reached.

  
Activity Thresholds Start Time The time from which the tracking started. This value can be reset (to zero) to reset all the thresholds and restart tracking.
Note: All thresholds are reset at one time, thresholds cannot be reset individually. Start Time restarts each IPL.
Highest Number of Active Queries at One Time The highest number of open plus pseudo closed cursors (queries) at a given point in time
Highest Number of Plans in Cache The largest number of plans in the plan cache at a given point in time
Highest Number of Temporary Runtime Objects Stored in Cache The highest number of inactive runtime executable objects stored (cached) away (for future reuse) in the plan cache at a given point in time
Largest Total Size of Temporary Runtime Objects Stored in Cache The largest amount of temporary storage, in MB, consumed by the inactive runtime executable objects stored (cached) away in the plan cache at a given point in time
   
Plan Compression Summary

The Plan Compression Summary group of properties provides information about the use of hardware-accelerated plan compression. Plan compression reduces the amount of temporary storage that large cached plans require. These properties are only visible for partitions that have hardware-based acceleration available.

  
Current Number of Compressed Plans in Cache The number of compressed plans that are currently in the plan cache. Plans must be larger than 256 KB to be eligible for compression.
Current Storage Saved Due to Plan Compression The amount, in MB, by which the Current Plan Cache Size has been reduced due to the use of plan compression.
   
Query Supervisor

The Query Supervisor group of properties provides historical information about the interaction of the Query Supervisor with queries running on the system.

 
Number of thresholds reached The number of Query Supervisor thresholds that have been reached since IPL.
Most recent job to reach a threshold The name and timestamp of the most recent job to reach a Query Supervisor threshold.
Number of queries terminated The number of queries that have been terminated since IPL at the request of a Query Supervisor exit program.
Most recent job to have a query terminated The name and timestamp of the most recent job to have a query terminated at the request of a Query Supervisor exit program.
Most recent exit point program failure The name and timestamp of the most recent job to encounter an error when using a Query Supervisor exit program.