I’d like to first begin by thanking all of the IDUG volunteers. IDUG Orlando 2013 marks my 25th North American Conference that I have attended. I served on the Conference Planning Committee (CPC) in 1999 and I know it is a lot of work, but it is also a lot of fun and a great way to connect with people. So, my first advice to anyone would be to consider becoming an IDUG volunteer. I first presented at IDUG in 1996, and I’ve offered one or more presentations every year since. Our DB2 community grows because of volunteers and people that are willing to share their knowledge and experiences. When the DB2 community grows, this is good for all of our careers. My next advice would be to encourage everyone to become an IDUG speaker. I can testify with certainty that speaking at IDUG, and volunteering if you can, will do wonders for your career.
Because education is so important to the DB2 community, I started The DB2Night Show™ Edutainment Webinar Series in 2009. Our guests include IBM speakers, consultants, and DB2 community experts. The DB2Night Show has delivered over 250,000 hours of free DB2 education. At 32 hours per class costing ~$1,500, this is over $10M of free DB2 education delivered to our DB2 community! Learn more at www.DB2NightShow.com.
Over the years, IDUG logos have changed, conference venues have changed, monitoring methods and commands have changed, and, for many of us, our hair color has changed. One thing, however, that hasn’t changed has been our relentless pursuit of performance tuning. Successful DB2 tuning leads to improved performance at optimized, or minimized, costs.
DB2 LUW has come a long ways in simplifying tuning. We used to have to adjust values for many parameters. Now we just set them to AUTOMATIC. But, for all the virtues of automated tuning, we still have to be concerned with the costs of SQL and physical design, including index design, to achieve optimized performance at the lowest possible costs.
Whether you use db2pd, db2top, SQL Snapshots, MON_GET table functions, or commercially available performance management tools, there is ONE SQL performance metric that is vital to understanding SQL execution costs and identifying which SQL needs tuning. If you run the command “DB2 GET SNAPSHOT FOR ALL on DBNAME”, thousands of raw performance values will be returned. Of all those thousands of raw performance values, the good news is – here is the secret – you can get a lot of tuning accomplished with just three of them. That’s right, I said three.
DB2 uses B-Tree indexes. When a query is run and a good supporting index is in place, DB2 will perform a logical read (GETPAGE for DB2 z/OS people) to the index root page first. The root page will usually point to an intermediate page, and then the intermediate page points to an index leaf page. This would be the case for an index with NLEVELS 3. If the number of levels is four, two intermediate index levels would be accessed. The index leaf page contains a RID (Row ID) list with data page addresses containing the desired rows. Indexes commonly have between three and five levels.
It stands to reason, then, that most SQL statements should have an index access cost of three to five index logical reads. A SQL that joins two or three tables might perform six to fifteen, or maybe twenty, logical reads. If a SQL statement performs ZERO logical reads, then guess what?!?!? It is performing a table scan! If a SQL statement performs hundreds or thousands of index logical reads, then it is not using the B-Tree index structure efficiently and is, most likely, performing costly scans of index leaf pages!
The following SQL snapshot statement will find SQL that is performing costly leaf page scans. Change “DESC” to “ASC” and it will find SQL performing table scans:
SELECT CAST( (A.POOL_INDEX_L_READS) / (A.NUM_EXECUTIONS)
AS DECIMAL (13,2)) AS IXLREAD_PER_EXEC,
SUBSTR(A.STMT_TEXT,1,180) AS SQLTEXT
FROM SYSIBMADM.SNAPDYN_SQL WHERE NUM_EXECUTIONS > 0
ORDER BY DBPARTITIONNUM ASC, 1 DESC FETCH FIRST 25 ROWS ONLY;
You can find more DB2 Tips and Advice by reading the DB2 LUW Performance Blogs at www.DBISoftware.com/blog/db2_performance.php. If you like this tip, show me three fingers when you see me in the halls at IDUG!
Scott Hayes is President & Founder of DBI Software, an IBM DB2 GOLD Consultant and Information Management Champion, and the host of The DB2Night Show™. Reach him @srhayes or Scott.Hayes@DBISoftware.com.