My favorite performance items in DB2 LUW v9.7 - #2: section actuals

As well as something that gives you a static SQL snapshot (MON_GET_PKG_CACHE_STMT - see the previous post in this series), DB2 9.7 also introduces - ta da! - section actuals.

(Note - I'm not going to exhaustively go through all the details of collecting and using section actuals here, but there is lots of good information about them in the Information Center.  As well, if you were at IDUG EMEA 2010 in Vienna, my session C09 contained a fair bit of information on this topic.)

A bit of background.   If you're using complex SQL, it can sometimes be difficult to understand the operations DB2 used when compiling the statement.   To help in this area, DB2 provides the Explain facility, which shows how DB2 is going to execute the statement - for example, which tables are accessed & how, what joins will be used, what indexes, etc.   There are multiple flavors of explain, the most frequently used of which are the db2expln and db2exfmt.  For the purposes of looking at section actuals, we'll concentrate on db2exfmt, which provides the most detailed view of what will go on during statement execution.

To indicate what parts of the statement plan will likely do the heavy lifting, db2exfmt shows estimates of row counts that will flow through the plan, bottom-up.  Obviously, if DB2 has to read 100 million rows from an input table during processing of a query, that's a pretty expensive statement.  As those rows flow through the plan, they will be filtered out by predictes (e.g.  ... WHERE C1 = 5), joins with other tables, etc.  Below is a sample portion of db2exfmt output, showing where the optimizer's estimate of the number of rows at each operator is given.


The estimates are based on cardinality estimates from the catalog tables, which are collected with the RUNSTATS command.  In most cases, these estimates will be quite accurate - but sometimes they won't.  For example, if statistics are old, or if inadequate statistics were collected, the optimizer might end up making a poor decision.  As they say - "garbage in, garbage out".    But, how to tell if the estimates are inaccurate?

This is where the 'section actuals' feature in 9.7 comes in.  It allows DB2 to keep track of the actual number of rows that flow through each operator for a particular statement execution.   These actual row counts are then included in db2exfmt output, as follows -

A signficant difference between the optimizer's estimate and the actual number of rows processed may indicate a potential problem with the current statistics affecting that operator.  Collecting fresh or more detailed statistics on the affected table may be enough to resolve the problem.

Since the actual row count is specific to a particular execution of a statement (potentially with unique parameter values, etc.), the overhead of collecting this information can be quite high, and as a result, section actuals are not collected by default.   Two things have to be true: the database configuration parameter SECTION_ACTUALS needs to be set to BASE, and an activity event monitor must be set up to collect statement execution information (in this case, this includes the actual row counts for the statements that are tracked.)

Note that as with all activity event monitors, you should keep it scoped as tightly as possible - meaning that using a service subclass which is more narrowly defined than SYSDEFAULTSUBCLASS - if possible - will reduce the number of statements for which section actuals are collected, ideally to just the statement(s) you're interested in.  This helps keep the overhead of section actuals (and activity event monitors in general) to a minimum.

So, if you find yourself struggling with a possibly wayward access plan & you're having difficulty understanding where the bottleneck in the plan is, have a look at section actuals.  It can make finding problems in cardinality estimation MUCH easier!

Some useful links on section actuals -
- General introduction to section actuals
- db2exfmt
- Capturing activity events with the activity event monitor
- Using EXPLAIN_FROM_ACTIVITY to populate the db2exfmt explain tables from activity events

1 Like
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