DB2 LUW Performance: Average Result Set Size (ARSS)


Posted by Scott on April 7, 2009, 8:10 pm
in General ( DB2 Performance)

What type of database do you have?

You might think you have an OLTP transactional database. Or, you might think you have a Data Warehouse database. But what does your database think? How is it, or the queries within it, really performing?

The Average Result Set Size

Transactional databases tend to process small result set sizes (the actual number of rows retrieved for a given SELECT statement). Data Warehouse databases tend to process large result set sizes - often returning hundreds or thousands of rows for any given SELECT statement. My rule of thumb, or the tipping point between OLTP and Data Warehouse, is an average result set size (ARSS) of 10. If ARSS is less than or equal to 10, then the database is behaving like an OLTP database. If the ARSS is greater than 10, then your database is behaving like a Data Warehouse database. If the ARSS is just a little bit greater than 10, then you may have an OLTP database with some concurrent decision support (DW) queries running.

The ARSS Formula

Using a database snapshot (Use dbsnap2.txt from the class preparation instructions), simply divide the number of rows selected by the number of SELECT statements (ROWS_SELECTED / SELECT_SQL_STMTS).

A shameless marketing moment

You can learn more about the Average Result Set Size (ARSS) here from Brother-Eagle's Advice, or you can download, install, and run Brother-Eagle Standard Edition ( get it FREE ) to have this and several other metrics computed automatically for you.

FREE is a very good price.

Knowing what kind of database you have is very important as this will influence which key performance indicators and metrics are of most value to ascertaining the health and efficiency of your database.

In future posts, you will often see "If you have an OLTP database, then..." or "If you have a Data Warehouse database, then...".

If you are reading this blog and following along, I hope you'll post a comment and let everyone know what your ARSS is. As we go through the class, it becomes particularly valuable to all participants to have awareness of other participant's calculated values.

If you thought you had an OLTP database, but your ARSS is a rather large number (substantially greater than 10), then you've definitely got an opportunity for improvement (that's a polite synonym for PROBLEM on your hands. It's possible that your application is retrieving large result sets from the database, then doing its own filtering within the application instead of pushing the filtration down to DB2. This is something that should definitely be investigated.

Until next time,
Scott

Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.DBIsoftware.com

Post from : http://www.dbisoftware.com/blog/db2_performance.php
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=94