When computing the following formulas, use a database snapshot or refer to file "dbsnap2.txt" from the preparation instructions.
Rows Fetched per Transaction (FETTX)
FETTX = Rows Selected / TXCNT (Commit statements attempted + Rollback statements attempted)
It is unlikely you will be able to reduce FETTX without making application changes, but this metric tells you important information about the average number of rows that each transaction is actually retrieving (fetching). If your FETTX seems unreasonably high, it is possible your application is retrieving bulk rows and doing its own filtering instead of pushing the filtration down to DB2 via appropriate WHERE predicates.
Rows Read per Transaction (RRTX)
RRTX = Rows Read / TXCNT
Please understand that Rows Read is different from Rows Selected (or Fetched). In pursuit of retrieving result sets (Rows Selected), DB2 may have to read one or more rows on data pages, evaluate WHERE predicates, and then decide if a row it has just read belongs in the result set or not.
A DBA can easily influence and improve RRTX by making physical design changes such as adding indexes. If an ideal index is missing, DB2 may have to read many rows (Rows Read) to find qualifying rows for result sets (Rows Selected). After making any physical design change, be sure to re-compute RRTX to verify that, indeed, the cost of reading rows has been reduced.
As a review, the Index Read Efficiency (IREF) metric compares the ratio of Rows Read to Rows Selected. Click here to review the IREF blog post, or Click Here to learn more about IREF from Brother-Eagle's Expert Advice.
A shameless marketing moment...
A new version, V2.0, of Brother-Eagle™ for DB2 LUW is available from DBI. V2 provides even more performance metrics and remarkable customization capabilities. You can even add your own metrics and drill downs with Enterprise Edition. Some customers are turning Brother-Eagle into a business dashboard that presents business metrics such as sales volumes, transaction volumes, deposits, withdrawals, inventory levels, and more. With Brother-Eagle Enterprise Edition, you can put virtually any business information into a scrolling stock ticker display! Get Brother-Eagle now by visiting www.Brother-Eagle.com.
Just for fun...
As many of you know, I travel a lot. I passed the million mile mark with Delta Airlines earlier this spring. So, here is a helpful travel tip. Most hotels provide plastic laundry bags in the room closet. Use these for your dirty laundry. And, be sure to put the extra, unopened, bars of soap into the plastic bag along with your dirty laundry. When you get home, your luggage and dirty laundry won't smell bad.
Until next time,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info