Never do runstats on volatile tables

Posted by

Ok, so everyone will tell you that it doesn’t matter if you do runstats on volatile tables. The volatile flag is supposed to tell DB2 that the statistics are absolutely not correct, and heavily bias db2 to use indexes over table scans. Commerce has 8 tables in Commerce 6 (9 in Commerce 7) that are marked as volatile. At least two of them I would argue do not meet the definition for volatile, but that’s another post. We have seen clients with severe performance issues when they did runstats on volatile tables, so our scripts do not do runstats on volatile tables and advise all of our customers not to as well.

One particular client has some non-Commerce databases that we provide some tier-3 support for. One of those databases had a cripiling performance issue today that was causing thousands of deadlocks per hour. Ultimately the issue turned out to be that when runstats was done on it at 6 AM this morning, the table all of the deadlocks were on had 0 rows in it. And when this issue was occuring, there were about 4,000 rows in it.

Clearly they don’t follow our advice to skip runstats on volatile tables alltogether, but when they did runstats again on the table mid-morning, the problem resolved. So clearly DB2 was not ignoring the runstats at all. When I explained the problem query while the problem was occuring, the only index being used was the primary key – the where clause included three columns, none of which was the primary key. And there was an index with precisely those three columns that was not being used. After runstats, the query immediately started using the index designed for it, and the database went back to normal with no deadlocking.

My conclusion is that DB2 (ok, so this is 8, FP 18) does not properly respect the volatile flag and that runstats sould either never be done (my preference), or should be done with realistic values on the higher end of normal for the table, and then not done again.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

2 comments

    1. My choice is often actually to run runstats on volatile tables when the cardinality is highest. So I still ignore volatile tables for runstats in most of my scripts. However, I also leave automatic statistics on, and do not exclude the volatile tables from automatic runstats. In some environments where I have had problems with volatile tables, I check the cardinality of the table when I do runstats, and only do runstats if/when the current cardinality is higher than the last time they were collected.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.