DB2 LUW Performance: Table Read I/O and Overflows

1. The Database Synchronous Read Percentage, SRP, previously described in this blog post
2. The Database Index Read Efficiency, IREF, previously described in this blog post
3. Table I/O measurements:
   • The average number of Rows Read per database Transaction (TBRRTX)
   • The percentage of Overflows over Rows Read (TBROVP)

TBRRTX - Average Rows Read per Database Transaction

To compute this metric, you need both a database snapshot (use 'dbsnap2.txt') and a table snapshot (use 'tbsnap2.txt').

TBRRTX should be computed for each table by dividing the number of Rows Read by the number of database Transactions (Commits Attempted + Rollbacks Attempted).

Remember that Rows Read is the number of rows that DB2 picked up from the data pages and evaluated for potential inclusion in the result set. Rows Read is not incremented when Index Only Access is used because the Table data rows are not accessed.

It is quite unlikely that every transaction will access every table. Therefore we reasonably expect, and commonly find, that TBRRTX for most tables with proper, optimized indexes is ordinarily less than ten and usually in the low single digits.

For OLTP databases, here are some generally helpful guidelines:

• When TBRRTX < 10, this is GOOD.
• When TBRRTX > 10 and < 100, index improvement opportunities probably exist
• When TBRRTX > 100, you definitely have index improvement opportunities
• When TBRRTX > 1,000, management is usually screaming, the performance crisis sirens are sounding, and executives may be inclined to needlessly waste money on unnecessary hardware upgrades

For Data Warehouse databases, the guidelines aren't as concrete, but TBRRTX can be used to help identify which tables are in most need of index tuning or physical design changes (such as using MQTs or converting to MDCs).

If you are running DB2 9, the query below will generate a report for you which shows table name, Rows Read, TBRRTX, and the number of transactions:

• select substr(a.tabname,1,30) as TABNAME,
  a.rows_read as RowsRead,
  (a.rows_read / (b.commit_sql_stmts + b.rollback_sql_stmts + 1)) as TBRRTX,
  (b.commit_sql_stmts + b.rollback_sql_stmts) as TXCNT
  from sysibmadm.snaptab a, sysibmadm.snapdb b
  where a.dbpartitionnum = b.dbpartitionnum
  and b.db_name = 'DBNAME'
  order by a.rows_read desc;

The report should look something like this:

TBROVP - The percentage of Overflows over Rows Read

For each table, using a table snapshot (or 'tbsnap2.txt') compute TBROVP = Overflows * 100 / Rows Read (+1 if you want to avoid division by zero).

A table write overflow occurs when a VARCHAR column is updated such that its length increases and, because the row is now larger (or wider), the row no longer fits on the data page where it was originally stored. DB2 relocates the row to a new data page and places a pointer in the original location to the new location.

A table read overflow occurs when DB2 attempts to read the row from its original location, then discovers the pointer and has to go read the row from its new location. Read overflows are particularly expensive because DB2 is now forced to do double the logical read I/O and probably double the physical I/O as well.

As a rule of thumb, when TBROVP exceeds 3% for any given table, then that table should be reorganized. Several companies use this metric in place of the reorgchk utility as an indicator for when to REORG a table. As a plus for avoiding reorgchk, the catalog statistics will not be updated so dynamic SQL access plans won't be disrupted and performance should be more predictable.

Catalog Tables - Bonus Tip

Too often we find that DB2 Catalog tables are neglected from basic and necessary maintenance. Be sure to run runstats periodically and, if TBROVP is greater than 3%, make sure you reorganize the catalog tables. SYSIBM.SYSINDEXES tends to be particularly prone to Overflow I/O problems.

The Shameless Marketing Moment

DBI's Brother-Panther® automatically computes SRP, IREF, TBRRTX, TBROVP, and dozens more performance metrics for you. Brother-Panther also allows you to click on a table name and quickly find the SQL statements that are driving the I/O to a particular table. You will quickly see the SQL statements that are causing I/O problems for a table, and be able to easily rectify performance problems using integrated Explain and DB2 Advisor tools.

Just for Fun

Check out this 24-May-2007 Computer World report on the Top 10 Dead (or dying) computer skills. I'm pleased to report that DB2 isn't on this list.

Cheers,
Scott

Scott Hayes
President & CEO, DBI
IBM DB2 GOLD Consultant
IBM Champion
Your Performance IS Our Business

Please Note: DBI has moved to a new web address!