June 01, 2007
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.Database-Performance.info
Trackback Pings
TrackBack URL for this entry:
http://www.ibmdatabasemag.com/blog/main/archives/2007/06/db2_luw_perform.html
« Who Wants to Learn about DB2 LUW Performance? | Main | DB2 LUW Performance: Index Read Efficiency (IREF) »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.