My favorite performance items in DB2 LUW v9.7 - #5: monreport.dbsummary

As is pretty well known by now, DB2 v9.7 introduced 'in-memory' metrics, usually seen through the MON_GET table functions (see favorite 9.7 performance item #1).  There is A LOT of good information here, building on what the snapshots provide with great things like the 'time spent' metrics.   These are invaluable if you need to find out where DB2 is spending its time.   That's the good news.  The less good news is that these new metrics are not available in the snapshots.  I'm a real advocate of using the MON_GET table functions to acquire, process & analyze monitoring data from DB2.

Note - If you're an IDUG member - and you should be! :-) - you can get access to many good presentations on the topic, to get you started.  One from last year's Vienna conference is session C08.

But sometimes, you want to be able to sit down and do a quick bit of ad hoc monitoring from the command line.  If you happen to have your favorite query scripted up & ready to go, great!  But if not, you might not feel like dashing off a fairly weighty SQL query of the MON_GET tables right from the command line.  One thing you can do, of course, is use a GET SNAPSHOT command!  They aren't being extended, but they certainly haven't gone away.  So if like me, you have an almost reflex-like tendency to use GET SNAPSHOT to see what's going on, that still works just fine.

How to get to the new metrics like wait times, though?   Are you doomed to spontaneous SQL querying from the command line?   Good news here - DB2 9.7 delivered the MONREPORT module, which contains a number of very handy reports which hit the highlights of DB2 monitoring metrics - including the latest ones.  Two great things are (1) it reports monitor values collected over a period of time (default 10 seconds), rather than since database activation, and (2) it calculates a number of key 'derived' metrics, such as bufferpool hit ratio, per-second rates of things like transaction & statement execution, etc.   About time too.  :-)

monreport.dbsummary (my favorite) gives a good approximation of a database snapshot, including

  • work volume and throughput in terms of statements and transactions - both total and precalculated in per-second amounts
  • overall bufferpool hit ratios for data, index, temp and XDA
  • a breakdown of time spent waiting, in categories like lock wait, IO wait, network wait, etc.
  • a breakdown of processing time, in categories like sorting, compiling, committing, etc.
  • a summary of connection-level statistics, such as CPU consumption, wait time, rows read & modified, etc.

While there are a few metrics in a database snapshot that aren't included, monreport.dbsummary definitely captures the main metrics in a very consumable way.   And it couldn't really be easier to call:

call monreport.dbsummary             # collects & displays for a 10s period
call monreport.dbsummary( nseconds ) # collects & displays for an N-second period

The monreport module also offers other categories of information

  • monreport.connection gives similar information that monreport.dbsummary does, but broken down at the connection level (think application snapshot)
  • monreport.pkgcache gives a great assortment of "Top 10" rankings for SQL statements, such as by CPU time, activity time, wait time, rows read, etc.  These are provided both as totals for all executions of a particular statement, and per execution.  A parameter lets you choose static or dynamic SQL only, but the default is both.   While it only displays the first small portion of the statement text, it gives you the EXECUTABLE_ID for each, which you can use to find all the low-level details (and the complete text) from MON_GET_PKG_CACHE_STMT.
  • monreport.lockwait gives (not surprisingly) lock wait information - who's waiting for a lock and in what mode, who's holding that lock, the lock name, etc.  I'm a big fan of this kind of output, as compared to just a lock list, since it doesn't bother showing uncontended locks, and so the output is much smaller.
  • monreport.currentsql gives "Top 10" rankings similar to monreport.pkgcache, but these are for SQL that is running right now, rather than historical summary.  Interestingly, it shows the entire SQL statement text, rather than the small portion that monreport.pkgcache does.
  • monreport.currentapps gives a summary breakdown of the state of current application execution on the system, including WLM information, the state of activities (executing, idle, waiting on locks.etc).


Recent Stories
Tips for getting the best INSERT performance

Statistics Event Monitors - terrible name, terrific feature

Measuring OVERHEAD and TRANSFERRATE on DB2 LUW - the why & how