0

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

  • What's "IREF"? What's your Db2 version and platform? Have you considered using DB2 Design Advisor or its equivalent? – mustaccio Oct 2 '18 at 22:38
  • Updated requested info. I've considered DB2 Design Advisor but I need to contact the Customer before I use it – alex suru Oct 2 '18 at 22:55
0

The ?'s are Parameter markers. Read abut them here https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/c0020295.html

I would suggest you add an index on column AB

Also consider using IBM Data Server Manager to monitor you database. It includes the ability to see explain plans and also invoke the design advisor

0

You can find out which statement(s) that is causing most read rows:

select num_executions, rows_read, stmt_text
from sysibmadm.snapdyn_sql
order by 2 desc 
fetch first 5 rows only

If UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ? is among the culprits you likely need to add an index on X.AB:

CREATE INDEX ... ON X (AB)
ALLOW REVERSE SCANS
COLLECT SAMPLED DETAILED STATISTICS  

There's like a zillion possible options for an index, but something like above is usually good enough. If you have access to db2exfmt you can compare plans before and after like:

db2 "set explain mode explain"
db2 "UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ?"
db2exfmt -d <db> -g -1 -o before.exfmt
db2 "set explain mode no"
db2 "create index ..."
db2 "set explain mode explain"
db2 "UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ?"
db2exfmt -d <db> -g -1 -o after.exfmt
db2 "set explain mode no"

All from memory, so there might be a glitch or two in there

  • Hi Lennart, I tried that query to find out which statement is having most read rows before also but is not very readable: huge empty spaces between statements, lots of dashes... and num_executions and rows_read are always 1 and 0. – alex suru Oct 3 '18 at 20:49
  • pipe the result from the query to a file or less -S. If all you get is 0 or 1 you probably are not collecting information. Check what ` db2 get db cfg for YOURDB | grep MON_` – Lennart Oct 4 '18 at 0:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.