Accessing the SQL plan cache with SQL stored procedures

qsys2.dump_plan_cache(‘lib', 'file')

This procedure creates a snapshot (database monitor file) of the contents of the cache. It takes two parameters, library name and file name, for identifying the resulting database monitor file. If the file does not exist, it is created. The file name is restricted to 10 characters.

For example, to dump the plan cache to a database performance monitor file called SNAPSHOT1 in library QGPL:

  CALL qsys2.dump_plan_cache('QGPL','SNAPSHOT1');

qsys2.start_plan_cache_event_monitor(‘lib', 'file')

This procedure starts an event monitor to intercept plans as they are removed from the cache and generate performance information into the specified database monitor file. It takes two parameters, library name and file name, for identifying the resulting database monitor file.

If the file does not exist, it is created. Initially the file is created and populated with the starting record id 3018 (column QQRID = 3018). Control returns to the caller but the event monitor stays active. Library QTEMP is not allowed. The file name is restricted to 10 characters.

For example, to start an event monitor and place plan information into a database performance monitor file called PRUNEDP1 in library QGPL:

  CALL qsys2.start_plan_cache_event_monitor('QGPL','PRUNEDP1');

qsys2.start_plan_cache_event_monitor(‘lib', 'file', monitorID)

This procedure starts an event monitor to capture plans as they are removed from the cache and generate performance information into a database monitor file. It takes three parameters, library name, file name, and monitorID. The library name and file name identify the resulting database monitor file.

If the file does not exist, it is created. Initially the file is created and populated with the starting record id 3018. The monitorID is a CHAR(10) output parameter set by the database to contain the 10 character identification of the event monitor that was started. Control returns to the procedure caller but the event monitor stays active. Library QTEMP is not allowed. The file name is restricted to 10 characters.

For example, start an event monitor to place plan information into a database performance monitor file called PRUNEDPLANS1 in library QGPL. Capture the monitor id into host variable HVmonid for use later:

  CALL qsys2.start_plan_cache_event_monitor('QGPL','PRUNEDP1', :HVmonid);

qsys2.end_all_plan_cache_event_monitors()

This procedure can be used to end all active plan cache event monitors started either through the GUI or use the start_plan_cache_event_monitor procedures. It takes no parameters.

  CALL qsys2.end_all_plan_cache_event_monitors();

qsys2.end_plan_cache_event_monitor(‘monID')

This procedure can be used to end the specific event monitor identified by the given monitor id value. This procedure works with the start_plan_plan_event_monitor to end a particular event monitor.

Example:

  CALL qsys2.end_plan_cache_event_monitor('PLANC00001');

qsys2.change_plan_cache_size(sizeinMeg)

This procedure can be used to change the size of the Plan Cache. The integer parameter specifies the size in megabytes that the plan cache is set to. Once designated, that size will remain at the fixed maximum size of the Plan Cache even across IPLs. If the value given is zero, the plan cache is reset to its default value which allows the plan cache to be auto-sized by the database.

Example:

  CALL qsys2.change_plan_cache_size(3072);

The following graphic illustrates that from System i Navigator, you can select the SQL Plan Cache Properties.

The next graphic shows that the selected SQL Plan Cache properties displays the current plan cache size and the timestamp that it was created.

qsys2.dump_plan_cache_properties(‘lib', 'file')

This procedure creates a file containing the properties of the cache. It takes two parameters, library name and file name, for identifying the resulting properties file. If the file does not exist, it is created. The file name is restricted to 10 characters. The file definition matches the archive file qsys2/qdboppcgen.

For example, to dump the plan cache properties to a file called PCPROP1 in library QGPL:

  CALL qsys2.dump_plan_cache_properties('QGPL','PCPROP1');

qsys2.dump_plan_cache_topN('lib', 'file', <number-of-TOP-queries-to-dump>

This procedure creates a snapshot file from the active plan cache containing only those queries with the largest accumulated elapsed time. The number of queries to capture is designated by the caller in the third parameter. This procedure provides a programmatic way capture the most noteworthy queries, making it easier to compare and contrast this aspect of database performance.

For example, to capture the 20 queries with the largest elapsed time and dump the details into a snapshot file named SNAPSHOTS/TOPN121413:

  CALL QSYS2.DUMP_PLAN_CACHE_topN('SNAPSHOTS', 'TOPN121413', 20);

qsys2.extract_statements

This procedure returns details from a plan cache snapshot in the form of an SQL table or a result set.

If the OUTPUT_SCHEMA and OUTPUT_TABLE parameters have the null value, a result set containing the extracted statement information is returned.

For example, extract the 100 most recent statements from monitor APRIL1014:

CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS', 'APRIL2014');

CALL QSYS2.EXTRACT_STATEMENTS('SNAPSHOTS', 'APRIL2014', '*AUDIT', 
  'AND QQC21 NOT IN 
   (''CH'', ''CL'', ''CN'', ''DE'', ''DI'', ''DM'', ''HC'', ''HH'', ''JR'', ''FE'',
    ''PD'', ''PR'', ''PD'')',
  ' ORDER BY QQSTIM DESC FETCH FIRST 100 ROWS ONLY ');

For example, extract all the queries where the query took longer than one second:

CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS', 'APRIL2014');

CALL QSYS2.EXTRACT_STATEMENTS('SNAPSHOTS', 'APRIL2014',
ADDITIONAL_SELECT_COLUMNS => ‘DEC(QQI6)/1000000.0 as Total_time, 
                              QVC102 as Current_User_Profile ',
ADDITIONAL_PREDICATES => ' AND QQI6 > 1000000 ',
ORDER_BY => ' ORDER BY QQI6 DESC ');

qsys2.import_pc_snapshot

This procedure is a programmatic alternative to using System i Navigator to import an existing SQL plan cache snapshot.The caller of this procedure must have the necessary authorities needed to query the target snapshot file.

This example shows how to programmatically capture information from the live plan cache for the 50 most expensive queries and import the snapshot into Navigator.

CALL QSYS2.DUMP_PLAN_CACHE_TOPN('SNAPSHOTS', 'JUNE2014', 50);
CALL QSYS2.IMPORT_PC_SNAPSHOT('SNAPSHOTS', 'JUNE2014', 'Top 50 Queries-June 2014');

qsys2.remove_pc_snapshot

This procedure is a programmatic alternative to using System i Navigator to delete an existing SQL plan cache snapshot. The caller of this procedure must have the necessary authorities needed to delete the target file. Any rows in the Navigator snapshot list which correspond to the input library and file name are removed. The input file name is deleted.

This example shows how to programmatically remove a snapshot that has aged beyond its usefulness.

CALL QSYS2.REMOVE_PC_SNAPSHOT('SNAPSHOTS', 'JUNE2013');

qsys2.import_pc_event_monitor

This procedure is a programmatic alternative to using System i Navigator to import an existing SQL plan cache event monitor. The caller of this procedure must have the necessary authorities needed to query the target event monitor file.

This example shows how to programmatically import an event monitor into Navigator that was used to capture queries pruned from the plan cache during the month of June, 2014.

CALL QSYS2.IMPORT_PC_EVENT_MONITOR('SNAPSHOTS', 'PRUNE0614', 'Pruned queries - June 2014');

qsys2.remove_pc_event_monitor

This procedure is a programmatic alternative to using System i Navigator to delete an existing SQL plan cache event monitor. The caller of this procedure must have the necessary authorities needed to delete the target file. Any rows in the Navigator event monitor list which correspond to the input library and file name are removed. The input file name is deleted.

This example shows how to programmatically remove an event monitor that has aged beyond its usefulness.

CALL QSYS2.REMOVE_PC_EVENT_MONITOR('SNAPSHOTS', 'PRUNE0613');