Optimizing performance using the Plan Cache

In addition, procedures are provided to allow users to programmatically work with the plan cache. These procedures can be invoked using the SQL CALL statement.

The Plan Cache interface provides a window into the database query operations on the system. The interface to the Plan Cache resides under the System i Navigator > system name > Database.

Within the SQL Plan Cache folder are two folders, SQL Plan Cache Snapshots and SQL Plan Cache Event Monitors.

Clicking the SQL Plan Cache Snapshots folder shows a list of any snapshots gathered so far. A snapshot is a database monitor file generated from the plan cache at the time a 'New Snapshot' is requested. It can be treated much the same as the SQL Performance Monitors list. The same analysis capability exists for snapshots as exists for traditional SQL performance monitors.

Clicking the SQL Plan Cache Event Monitors shows a list of any events that have been defined. Plan Cache event monitors, when defined, generate database monitor information from plans as they are being removed from the cache. The list includes currently active events as well as ones that have completed. Like a snapshot, the event monitor is a database monitor file. Consequently, the same analysis capability available to SQL performance monitors and snapshots can be used on the event file.

The plan cache is an actively changing cache. Therefore, it is important to realize that it contains timely information. If information over long periods of time is of interest, an event monitor could be defined to ensure that information is captured on any plans that are removed from the cache over time. Alternatively, you could consider implementing a method of performing periodic snapshots of the plan cache to capture trends and heavy usage periods. See the discussion on IBM® supplied, callable SQL procedures later in this section on plan cache.