I received a task from one of my colleagues to check why he always receives high CPU load alerts. I don't have too much experience with databases and I need some clarifications. I created a script to capture the first 5 threads that are using most of the CPU when the alerts are triggered. The order is almost always the same, first three are db2agents and the other 2 are db2pfchr. From my knowledge, I found nothing wrong with the SQL statements so I checked for table scans and IREF.
What I noticed immediately was the difference between rows read and rows selected which looks like this for example:
Rows deleted = 0 Rows inserted = 0 Rows updated = 1329 Rows selected = 714 Rows read = 3903430587518 Rows written = 3471
First 3 biggest table scan values are 26586532, 15538513 and 942177. The first table has one index that was never used, the second table has 1 index that was used around 70k times and the third table has no index.
So, my obvious guess is that indexes need to be added in order to avoid table scans
Since I never added an index in my life and must read more about it, I needed to be sure about my assumptions and maybe there something else to check? Also, all values in the queries are "?"... hidden? Like UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ?
This is an OLTP DB, and it's about DB2 V10.5 on RHEL IREF - Index Read Efficiency = ROWS READ/ROWS RETURNED