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 three 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.
Activity Threshold – tracked ‘high water mark’ of several key indicators.

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.

This System i Navigator graphic is described in the previous paragraph

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 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	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.
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 Pruned	Total number of plans removed from the plan cache.
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.
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.
Number of Plans Rebuilt due to AQP	Number of plans rebuilt to AQP.
Number of Query Mapping Errors Since Start	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

Plan Cache Activity Thresholds The Activity Thresholds group of properties track 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

Query Supervisor The Query Supervisor group of properties provide 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.