IBM Support

Getting Started with the Plan Cache Snapshot Tool

Troubleshooting


Problem

This document provides information on getting started with the plan cache snapshot tool available in IBM i Access Client Solutions.

Resolving The Problem

The snapshot tool is an excellent way for database engineers (DBE) to review and tune queries and, unlike an SQL performance monitor (DBMON), it does not need to be started before the query starts.

The output from this tool is very similar to the output from the SQL performance monitor.  The tool will filter the query plans currently stored on your system, based on your specified criteria.

To get to the plan cache snapshot tool, do the following:
1. Open IBM i Access Client Solutions.
2. In the Database section, click on SQL Performance Center.
3. The tool can be used to investigate queries in the live plan cache or harvest the contents of the plan cache by collecting and storing them into a snapshot.
4.
To investigate the live plan cache, click on the Plan Cache Tab and then Show Statements  button (image 1).
To create a snapshot file, expand File -> New -> Plan Cache Snapshot (image 2).  After the snapshot is created, it will appear in the Plan Cache Snapshots tab.
(Image 1 - Live plan cache investigation)
Image 1 live plan cache investigation, Show Statement button is highlighted

(Image 2 - Collect and retain snapshot)
Image 2 Collect and retain snapshot. Click on File > New > Plan Cache Snapshot


The statements will appear in the right panel. You may sort these statements on any column by clicking on the column heading. Alternative clicks toggle between ascending and descending order.
image shows the result of using Show Statements, right click on a query and choose Visual Explain

You may use Visual Explain on any of these queries; right click and select Visual Explain. The Visual Explain window will open showing the explain plan, which you can further investigate.

Also, there is a Save to New function on each statement which can be used to create a sub-file on a problem query if you want to save the problem query into its own snapshot or send one problem query to your IBM i Global Support Center service representative.

Note on the SQL Plan Cache: The plan cache snapshot tool can be reviewed while the query is still running, or after the query has finished running. The plan cache is an actively changing cache, so the cache will not always contain the same combination of statements. After you have identified there is a query performance problem, do not wait too long before analyzing the Plan Cache as the problem query may be pruned from the Plan Cache. Some examples of why a query's access plan may be pruned from the Cache is: the Cache is a finite size and/or the plan is found to be outdated and replaced by a newer plan.

If information over long periods of time is of interest, you may want to create snapshot files from the busiest times of your week, say Monday mornings, for comparison. It may also be helpful to retain these snapshots files, in case IBM needs to review past performance of a specific query whose access plan has changed.

An event monitor could also be defined to ensure information is captured on any plans that are removed from the cache over time.
Event Monitors can be configured using SQL Performance Center.
Image shows how to create a plan cache event monitor. File > New > Plan Cache Event Monitor

Refer to the following URL for more information on setting up event monitors: https://www.ibm.com/docs/en/i/7.5?topic=cache-event-monitor



[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CKdAAM","label":"Performance-\u003EDatabase Performance"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Historical Number

511868231

Document Information

More support for:
IBM i

Component:
Performance->Database Performance

Software version:
All Versions

Operating system(s):
IBM i

Document number:
635067

Modified date:
24 October 2024

UID

nas8N1013275

Manage My Notification Subscriptions