Want to run DB2 LUW Databases Faster?
Put your database processing costs on a Diet!
In 2014, I presented “Sage Advice: 20 Invaluable DB2® LUW Performance Insights from Around the Globe” at IDUG Phoenix, IDUG Prague, to regional DB2 User Groups in Minneapolis, Milwaukee, and Chicago, and on The DB2Night Show™ 14th November in Episode #142. If you are an IDUG member and attended one of these conferences, you may be able to access a PDF of the presentation via IDUG.org, or you can watch the replay of Episode #142 - which I highly suggest because you’ll see a cool video of topics in this article brought to life. This article is a derivative work of this presentation, plus enhancements and embellishments, of course!
I’ve been presenting at IDUG conferences since 1996, and for most of these years I have taught worldwide audiences about various DB2 LUW performance measurements and tuning techniques. Together we have learned about important ratios, like IREF – Index Read Efficiency – the ratio of rows read to rows fetched, and costs, like BPLRTX – Bufferpool Logical Reads per Transaction, and index design considerations for performance improvements. However, I have a confession to share with you. While all these ratios and costs are useful and valuable, there is another, better way to analyze DB2 LUW performance. For the last 15+ years as an IBM DB2 GOLD Consultant, I have been able to approach hundreds of databases around the world – basically blindfolded – and been confident of achieving incredible tuning successes within hours. You’re about to learn my secret.
WEIGHTS
More specifically, the secret is relative weights of aggregated costs. But, before I get all technical on you, please allow me to explain this simply with a real life metaphor.
Let’s imagine that you are going to run a marathon wearing a backpack. Yes, I realize this might sound absurd, but while running in the Austin Half Marathon a couple years ago I actually saw a member of the US Army running with his full uniform, military boots, and a backpack. After thanking him for his service to our country, I asked him how much his backpack weighed: 125LBs (56Kg). Now, if you were going to run a marathon wearing a backpack, or if you were going to go on a long hike, I’m pretty sure you would want that backpack to weigh as little as possible. With minimal weight, you could probably run faster and actually make it to the finish line without fizzling from exhaustion. Since everyone is ordinarily pressed for time these days, you’d also want to make the backpack weigh as little as possible as quickly as possible. I know very few DBAs that have abundant spare time on their hands!
Your DB2 database is running your business and it is wearing a backpack – it is called a workload! If, through optimal tuning efforts, you can make the workload weigh less, then DB2 can run faster and more efficiently while avoiding CPU or I/O exhaustion! This seems straight forward, but it isn’t necessarily intuitive.
Many DBAs are likely to run db2pd, db2top, IBM Optim Performance Manager, Get Snapshot commands, or SQL queries to SYSIBMADM performance views or MON_GET functions, and most everyone scans these data sources looking for the BIG NUMBERS. Surely the SQL with elapsed times of several seconds or minutes are the trouble makers, right? These might be problems, but, in my experience, are probably not the biggest rocks weighing down DB2’s backpack!
FINDING THE HEAVY WEIGHTS
For many DB2 objects like Bufferpools, Tablespaces, or Tables, finding heavy objects is relatively easy and often very illuminating. I usually focus on Tables first because this is where DBAs have the most control over physical design, options, and indexes. To find the relative table processing weights, you need to express the ROWS_READ for each table as a percentage of the SUM of all Database ROWS_READ. See Figure 1 for example SQL that is provided “as-is” without representations or warranties of any kind. You could, alternatively, compute this by hand from db2pd or Get Snapshot data, or you can probably write fancier SQL than I do.
Figure 1 – Table Weights
select substr(a.tabschema,1,20) as TABSCHEMA,
substr(a.tabname,1,25) as TABNAME,
a.rows_read as RowsRead,
CAST((((A.ROWS_READ) * 100.0)
/ (Select (SUM(Z.ROWS_READ) + 1.0)
FROM SYSIBMADM.SNAPTAB Z
WHERE A.DBPARTITIONNUM = Z.DBPARTITIONNUM
)) AS DECIMAL(5,2)) AS PCT_DB_TB_ROWSREAD
from SYSIBMADM.snaptab a
order by a.rows_read desc fetch first 20 rows only;
If I had a dollar for every time I heard a DBA say “Gosh, we knew that table THUS_AND_SUCH had high Rows Read, but we didn’t know it was over 75% of all Database Rows Read!”, I would be retired on a private island in Tahiti by now! It happens all the time! BIG ROCKS are in DB2’s backpack!
COST AGGREGATION
I’ve been teaching about the importance of SQL consolidation and cost aggregation at IDUG for many years. Some people get it, some don’t. Some people are smarter and more successful, and others flounder or bill more hours. The imperative point is to determine aggregate costs of SQL execution independent of, or irrespective of, any literal values that might likely appear in your SQL workloads. Performance data from db2pd, db2top, IBM Optim Performance Manager, Get Snapshots, MON_GET, or queries to SYSIBMADM performance views will return detailed costs for every statement’s execution. The Execution Count is only incremented, with costs summed, when statements and their literals match exactly – unless you are using prepared statements with parameter markers or the DB2 Statement Concentrator (DB CFG STMT_CONC). Let’s illustrate, see Figure 2.
Figure 2 – Sample SQL Workload
# Execs Statement Text CPU Secs
======= ===================================================== ========
1 SELECT COLA, COLB from TABLE_A where DESC = ‘IDUG’ 0.1
1 SELECT COLA, COLB from TABLE_A where DESC = ‘IS’ 0.1
1 SELECT COLA, COLB from TABLE_A where DESC = ‘REALLY’ 0.1
1 SELECT COLA, COLB from TABLE_A where DESC = ‘VERY’ 0.1
1 SELECT COLA, COLB from TABLE_A where DESC = ‘AWESOME’ 0.1
1 SELECT COLC, COLF from TABLE_A where CITY < ‘AUSTIN’ 0.3
6 Total 0.8
Performance data from db2pd, db2top, IBM Optim Performance Manager, MON_GET, or SYSIBMADM queries will show you six different statements each with one execution. However, if you look at the first five SQL queries, you will notice there is a pattern. The queries are the same except for the search value of column DESC. If the literal values are replaced by place holders (as STMT_CONC = LITERALS would do), there are actually only two distinct SQL statement patterns in the workload. See Figure 3.
Figure 3 – Consolidated SQL Workload
# Execs Statement Text CPU Secs
======= =============================================== ========
5 SELECT COLA, COLB from TABLE_A where DESC = :LS 0.5
1 SELECT COLC, COLF from TABLE_A where CITY < :LS 0.3
6 Total 0.8
After consolidating the SQL workload and aggregating the CPU costs of execution, we learn that the “DESC = :LS” query carries a total CPU cost of 0.5 seconds. The total CPU cost of the workload (backpack) is 0.8 seconds, so 0.5/0.8 = 62.5% of the CPU cost! On an individual execution basis (non-consolidated), the “DESC = :LS” consumed 0.1 CPU seconds which deceptively appears to be only 0.1/0.8 = 12.5% of the CPU. For additional information, the topic of SQL Cost Aggregation and Weighted Analysis are described more fully by US Patent 6,772,411 and the infamous DB2 LUW Performance blog on Identifying Mosquito Swarms.
STATEMENT CONCENTRATOR (STMT_CONC)
With the importance of SQL Cost Aggregation explained and relative Weights illustrated, you might be eager to turn on the Statement Concentrator. Not so fast! The Statement Concentrator should not be used in Data Warehouse databases because the result sets are normally rather large and you want the DB2 optimizer to consider column distribution statistics to derive optimal plans. The Statement Concentrator was implemented by IBM to help save statement Prepare and CPU time in transactional (OLTP) databases. While some DB2 customers have had success with it, others, as surveyed on The DB2Night Show polling questions, have experienced application problems, so you must be certain to test your application very carefully before turning it on. Because of the value of aggregation and reduced processing, some tool vendors, including IBM Optim Performance Manager, might advise you to turn on “STMT_CONC = LITERALS”, but you should only do so for OLTP databases after very thorough testing. For completeness, it should also be noted that there are tool vendors, like DBI Software, that will perform SQL Consolidation and Cost Aggregation without turning on the Statement Concentrator, post-execution, so that the optimizer gains full benefit of literal value distribution statistics and risks to application execution failures are mitigated.
FINDING THE HEAVY SQL
As you might have noticed from the sample illustration in Figures 1 and 2, cost aggregation is not only illuminating, but it can help you avoid chasing deceptive individual values. SQL can be evaluated in terms of CPU cost if a server is CPU bound, I/O cost if a server is I/O bound, or in terms of execution time if response times or batch programs are too slow. For any measurement such as ROWS_READ, Bufferpool Logical or Physical Reads, CPU time, or any other counter cost value, find the SUM for the cost attribute across the entire database workload, and then express the cost as a percentage of the total workload’s cost. Figure 4 provides sample SQL to find heavy CPU consumers, and Figure 5 provides sample SQL to find heavy I/O consumers by ROWS_READ. Either of these sample queries can be enhanced further to filter on the table name with the highest I/O weight (per the SQL in Figure 1) by adding STMT_TEXT LIKE ‘%HEAVY_WEIGHT_TABLE_NAME%’ to the WHERE clause. This trick works well unless your application SQL workload uses VIEW or ALIAS names instead of TABLE names – a problem solved by some commercially available tools.
Figure 4 – SQL Heavy CPU Consumers
SELECT
CAST( (
( (A.TOTAL_USR_CPU_TIME * 1000000) + A.TOTAL_USR_CPU_TIME_MS
+ (A.TOTAL_SYS_CPU_TIME * 1000000) + A.TOTAL_SYS_CPU_TIME_MS
)
/ A.NUM_EXECUTIONS )
AS DECIMAL (15,0)) AS AVG_CPU_TIME_MS,
CAST (A.NUM_EXECUTIONS AS INTEGER) AS NUM_EXECS,
CAST(((
((A.TOTAL_USR_CPU_TIME * 1000000) + A.TOTAL_USR_CPU_TIME_MS
+ (A.TOTAL_SYS_CPU_TIME * 1000000) + A.TOTAL_SYS_CPU_TIME_MS)
* 100.0)
/ (Select (SUM(B.TOTAL_USR_CPU_TIME) * 1000000)
+ (SUM(B.TOTAL_SYS_CPU_TIME) * 1000000)
+ SUM(B.TOTAL_USR_CPU_TIME_MS)
+ SUM(B.TOTAL_SYS_CPU_TIME_MS) + 1.0
FROM SYSIBMADM.SNAPDYN_SQL B
WHERE A.DBPARTITIONNUM = B.DBPARTITIONNUM
)) AS DECIMAL(5,2)) AS PCT_CPU_TIME,
SUBSTR(A.STMT_TEXT,1,110) AS CPU_SUCKING_SQL
FROM SYSIBMADM.SNAPDYN_SQL A
WHERE A.NUM_EXECUTIONS > 0
ORDER BY A.DBPARTITIONNUM ASC, 3 DESC, 1 DESC FETCH FIRST 25 ROWS ONLY;
Figure 5 – SQL Heavy I/O Consumers
SELECT CAST (A.NUM_EXECUTIONS AS INTEGER) AS NUM_EXECS,
CAST( (A.ROWS_READ + 0.001) / (A.NUM_EXECUTIONS + 0.001)
AS DECIMAL (13,4)) AS AVG_ROWS_READ,
CAST((((A.ROWS_READ) * 100.0)
/ (Select (SUM(B.ROWS_READ) + 1.0)
FROM SYSIBMADM.SNAPDYN_SQL B
WHERE A.DBPARTITIONNUM = B.DBPARTITIONNUM
)) AS DECIMAL(5,2)) AS PCT_ROWS_READ,
SUBSTR(A.STMT_TEXT,1,110) AS HEAVY_READER_SQL
FROM SYSIBMADM.SNAPDYN_SQL A
WHERE A.ROWS_READ > 0 AND A.NUM_EXECUTIONS > 0
ORDER BY A.DBPARTITIONNUM ASC, 3 DESC, 2 DESC FETCH FIRST 25 ROWS ONLY;
A Remarkable True Story
Once upon a time, not that long ago, I had the opportunity to work with a customer that needed a nightly batch process to run faster because they were barely staying within their window. The team had been looking at db2pd and db2top data, and trying to improve response times of SQL statements that were taking over ten seconds to complete. When we performed an elapsed time weight analysis, it turned out that 82% of four hour elapsed time was consumed by just THREE SQL statements each having average elapsed times in the range of 0.4 to 0.8 seconds. When they tuned these three SQL statements by adding indexes, miraculously the batch process was done in less than 30 minutes!
SUMMARY
By focusing on objects and SQL that weigh the most, that is, have the highest relative weights, you will be able to quickly identify the heaviest work in DB2’s backpack. When you combine relative weight analysis along with key ratios and costs, you will be able to focus your limited amount of precious time on fighting the right fires fast in pursuit of significant performance improvements! For a more complete tutorial on monitoring and tuning, read the DB2 LUW Performance Tuning Blogs Greatest Hits.
About the Author
Scott Hayes is President & Founder of DBI Software, an IBM DB2 GOLD Consultant, an inaugural IBM Champion, a DB2 LUW Performance Blogger, published author on IBMDataMag.com, and the Founder & Host of The DB2Night Show™ Edutainment Webinar series that has provided over 500,000 free hours of education to the DB2 community since 2009. Scott has spoken at every IDUG North American conference since 1996, attended all IDUG North American conferences since IDUG’s inception in 1988, plus attended and spoken at the majority of IDUG EMEA and AP conferences. DBI Software is an ISV providing Performance Management Tools for IBM DB2 LUW. Follow or contact Scott on Twitter @srhayes.