SQL Plan Cache - Show Statements

By right-clicking the SQL Plan Cache icon, a series of options are shown which allow different views of current plan cache of the database. The SQL Plan Cache > Show Statements option opens a screen with filtering capability. This screen provides a direct view of the current plan cache on the system.

System i Navigator - SQL Plan Cache Statements

Press the Apply or Refresh button to display the current Plan Cache statements. The information shown includes the SQL query text, last time the query ran, most expensive single instance run, total processing time consumed, total number of times run, and information about the user and job that first created the plan entry.

The information also includes several per run averages, including average runtime, average result set size and average temporary storage usage. There is an adjusted average processing time which is the average discounting any anomalous runs.

The display also shows how many times, if any, that the database engine resued the results of a prior run, avoiding rerunning the entire statement. There is also a Save Results button (not shown) that allows you to save the statement list, for example, to a .csv file or spreadsheet.

Finally, the numeric identifier and plan score are also displayed. For more detail on the columns displayed, see rzajqcolumnsplancache.htm

Statement Options

By highlighting one or more plans and right clicking, a menu with several possible actions appears.

Visual Explain: Shows a visual depiction of the access plan and provides more detailed performance analysis. Note only one statement can be highlighted when performing this action.

Show Longest Runs: Shows details of up to 10 of the longest running instances of that statement. Within the Longest Runs list, you can right click a statement and select Visual Explain, Work With SQL Statement, Work With SQL Statement and Variables, Save to New... snapshot or Remove. Snapshots are useful for capturing the information for that specific run in Visual Explain. Removing old or superfluous runs makes room to capture future runs. Only one statement can be highlighted when performing these actions. Any runs removed only affect which runs are shown in the list. The total time, total number of runs, and other information for the statement are still calculated including the runs removed from the list.

Show Active Jobs: Displays a list of jobs on the system that are currently using that statement or statements.

Show User History: Shows a list of all user IDs that have run that statement along with the last time they ran it.

Work with SQL Statement: Displays a scripting window containing the SQL statement. The scripting window is useful for working with and tuning the statement directly, or for just viewing the statement in its own window. Only one statement can be highlighted when performing this action.

Work with SQL Statements and Variables: Displays a scripting window containing the SQL Statement and any parameter markers entered with their specific values for that run of the SQL statement.

Save to New...: Allows you to create a snapshot of the selected statements.

Plan

Right-click to show options for modifying the plan:

Change Plan Score allows you to set the score to a specific value. The plan score is used to determine when a plan might be removed from the cache. A lower score plan is removed before a higher score plan. By setting the plan score high, the plan remains in the cache for a longer time. Setting the plan score to a low value causes the plan to be pruned sooner than might otherwise have occurred.

Delete allows you to remove the plan immediately from the cache. Note under normal circumstances there might not be a need to modify the attributes of a plan. Normal database processing ages and prunes plans appropriately. These modifying options are provided mostly as tools for minute analysis and for general interest.

The User and Job Name for each statement on the Statements screen is the user and job that created the initial plan with full optimization. This user is not necessarily the same as the last user to run that statement. The Longest Runs screen, however, does show the particular user and job for that individual run.

Filtering Options

The screen provides filtering options which allow the user to more quickly isolate specific criteria of interest. No filters are required to be specified (the default), though adding filtering shortens the time it takes to show the results. The list of statements that is returned is ordered so that the statement consuming the most total processing time is shown at the top. You can reorder the results by clicking the column heading for which you want the list ordered. Repeated clicking toggles the order from ascending to descending.

The filtering options provide a way to focus in on a particular area of interest:

Minimum runtime for the longest execution of the statement:: Show statements with at least one long individual statement instance runtime.
Statements that ran on or after this date and time:: Show statements that have been run recently.
Top 'n' most frequently run statements:: Show statements run most often.
Top 'n' statements with the largest total accumulated runtime:: Show the top resource consumers. Shows the first 'n' top statements by default when no filtering is given. Specifying a value for 'n' improves the performance of getting the first screen of statements, though the total statements displayed is limited to 'n'.
Statements the following user has ever run:: Show statements a particular user has run. The user and job name shown reflect the originator of the cached statement. This user is not necessarily the same as the user specified on the filter (there could be multiple users running the statement).
Statements that are currently active: Show statements that are still running or are in pseudo-close mode. The user and job name shown reflect the originator of the cached statement. This user is not necessarily the same as the user specified on the filter (there could be multiple users running the statement).
Note: An alternative for viewing the active statement for a job is to right-click the Database icon and select SQL Details for Jobs...
Statements for which an index has been advised: Show only those statements where the optimizer advised an index to improve performance.
Statements for which statistics have been advised: Show only those statements where a statistic not yet collected might have been useful to the optimizer. The optimizer automatically collects these statistics in the background. This option is normally not that interesting unless, for whatever reason, you want to control the statistics collection yourself.
Include statements initiated by the operating system: Show the 'hidden' statements initiated by the database to process a request. By default the list only includes user-initiated statements.
Statements that reference the following objects:: Show statements that reference the tables or indexes specified.
Statements that contain the following text:: Show statements that include the text specified. This option is useful for finding particular types of statements. For example, statements with a FETCH FIRST clause can be found by specifying ‘fetch'. The search is not case sensitive for ease of use. For example, the string 'FETCH' finds the same statements as the search string 'fetch'. This option provides a wildcard search capability on the SQL text itself.

Multiple filter options can be specified. The candidate statements for each filter are computed independently. Only those statements that are present in all the candidate lists are shown. For example, you could specify options Top 'n' most frequently run statements and Statements the following user has ever run. The display shows those most frequently run statements in the cache that have been run by the specified user. It does not show the most frequently run statements by the user (unless those statements are also the most frequently run statements in the entire cache).