News
Abstract
QSYS2.dump_plan_cache_topN procedure
Content
The dump_plan_cache_topN 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);
The output of the procedure is an SQL Plan Cache snapshot containing the top N queries. After the snapshot exists, the user can Analyze it and use Visual Explain.
Enhanced with IBM i 7.4 SF99704 Level 13 and IBM i 7.3 SF99703 Level 24:
- Add the CATEGORY optional filter parameter
Was this topic helpful?
Document Information
Modified date:
12 April 2021
UID
ibm11171192