March 23, 2010

Index-Only Access Reduces Getpages

I recently received this question from a DB2 developer: Will changing my SQL statement to index-only access cut my getpages in half?

My response is the same as it is to most queries: Rather than take my word, test the theory yourself.

To know what DB2 really does when an SQL statement is executed, you should use a performance monitor. However, the DB2 Explain command does offer a quick way to gauge the performance impact of SQL changes. Just keep in mind that the performance data generated with DB2 Explain can be wildly and misleadingly skewed by the statistics you collect. I know this from experience. I maintain that you should always use a performance monitor in these instances; just run the SQL statement and collect DB2 performance trace data to see what really happens. (And while I'm on my soapbox, I'll mention the golden rule for DBAs: Before moving any changes into a production environment, test, test, test!)

Still, using DB2 Explain and the information in these tables during the prototyping process can help you determine if changing to index-only access will improve a given statement's performance.

To get started with DB2 Explain, certain tables must be created, specifically PLAN_TABLE, which contains the access path, and DSN_STATEMENT_TABLE, which provides cost estimates of processor cost in milliseconds (PROCMS) and cost in service units (PROCSU).

Now, to give you a better idea of what I'm talking about, I'll use the delivered DB2 sample table, DSN8910.EMP. I created a copy of this table under my own user ID so I can create the indexes I need to experiment with. First I created a secondary non-unique index (XEMP02) on column FIRSTNME using this simple SQL statement:


Looking at the explain information for this statement, I'm using XEMP02, but it's not index-only. Now I check my performance information and I see that I have 34 getpages. Since this is dynamic SQL, I'm paying a performance price in preparing the statement. The catalog pages will show the extra getpages I've incurred by collecting this data.


When I next execute this statement, the getpages drop to four. This is because the statement is prepared and ready to execute again.


Looking at a bufferpool trace, I can see the getpages on the COLTR051 table space and XEMP02 index page sets.


Now I create a third index, XEMP03, on both FIRSTNME and LASTNAME. The initial execution uses 30 getpages.


I execute once more, and I see the getpages at two.


The screen below tells me that I'm now only accessing XEMP03. I'm not accessing the data found in COLTR051.


So in this example it looks like I can cut my getpages in half. Now, prior to DB2 10 you may have been in a situation where a unique index was being used to access the data. Though you'd like to add another column to the index to make the SQL index-only access, the additional column would break your unique rule. However, DB2 10 supports the capability to "include" columns on a unique index without having to use the column in the uniqueness. You can make a statement index-only, but this could negatively impact INSERT and UPDATE statements.