SQL Observer

Automated Dumping of SQL Access Plans from Plan Cache

User controlled saving of input data needed for analysis of SQL access methods

The intensive and increased use of SQL has made optimization of frequently used SQL statements one of the most rewarding ways of saving server resources. IBM’s “SQL Performance Center” in the Database section of the Access Client Solution (ACS) tool is the gold standard for analysis of Access Plan snapshots from the Plan Cache.

However, the Plan Cache data is not dumped automatically given it would consume excessive resources. An IBM performance expert recently suggested that a tool offering an automated and user-controlled dumping of Access Plans that might be wanted for analysis could be a popular option. This idea is implemented as GiAPA’s “SQL Observer”.

The unique QRO code identifying an SQL activity (= one or more statements) must be supplied when requesting a dump of Access Method information. Therefore, the first step for GiAPA’s SQL Observer is to run IBM’s Job Watcher, requesting the QRO code(s) for job(s) specified by the user. At the same time the user defines the frequency for returning Job Watcher data and for dumping Access Plans. An additional parameter defines the number of days the collected data is kept.

 This provides numerous possibilities: a special situation may justify collection of data every few seconds e.g. for one or a few jobs. This results in very detailed information collected for these selected case(s) without overall using excessive resources for the data collection. For the normal everyday workload, collection of data every two or five minutes may suffice.

Current User Name is often wanted in connection with analyzing heavy resource usage of server jobs.

GiAPA’s SQL Observer also includes displaying user names per job and collection interval.