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 2 - Collect and retain 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.

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.

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
Related Information
[{"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
Was this topic helpful?
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