Monday, November 23, 2009

Cooperate for greater performance! (Share a scan)

If you can benefit from someones work (and it is legal), why don't do it? If it is mutual, i.e., both sides benefit from each other, it's even better and a win-win situation. And when it is inside a database system and results in better query performance and more throughput while utilizing less system resources, it should cause big smiles from everyone. What I am talking about?

One of the many new features in DB2 9.7 is Scan Sharing. The optimizer now can group transactions that perform a table scan or block index scan on the same data into so-called share groups. Scanners within a share group coordinate among themselves, i.e., cooperate. The idea is that I/O and bufferpool activity is reduced by having multiple parties work on the same pages at the same time. This is great when your system is I/O bound because the costly physical I/O could be significantly reduced.

How would I explain such a feature to my kids? With the holiday season and family visits approaching I would tell them to coordinate the "bio breaks" on roadtrips. Instead of everyone independently asking for a pit-stop and daddy stopping every 30 minutes, the entire family could agree on visiting the restrooms at the same time and refilling the gas. Coordinated stops (comparable to physical I/O) would result in us significantly faster reaching the destination.