Identifying Problem SQL

Many DBAs have their own methods and approaches in this area,  but there are some basic sources and methods that I use. I’ll cover at least the basics of SQL  analysis in one or more future posts

Get SQL from Developers

So the optimum situation is that your developers give you the SQL they’re planning to use in custom code (or better yet, they have a dba write it in the first place). More likely, developers will identify a problem area where “the database is slow”, and can give you the SQL from that part of their code. I also ask on any new table creation that the developers provide the SQL that they intend to use, so I can index for it ahead of time. I’ve seen significant site slowdowns caused by no indexes on a custom table.

Dynamic SQL snapshot

I use my own scripts to parse out a SQL snapshot using perl, and then I import it to Excel to play with it and order it in different ways and such. Once I’ve got it in Excel, I rank the statements based on their Rows Read, Execution time, CPU time, Sort time, and sometimes on the number of executions or the avg execution time. I then look at what’s in the top 10 in each category, and see if there are queries that stand out. Most frequently I find 1-5  bad queries that I can easily help with indexes and another 1-5 that I don’t like, but can’t index for.

Querying the admin view or snapshot table function

So this is a bit easier than using the dynamic sql snapshot – though I think the sql below only works on 9.7 because it uses the sysibmadm views. The SQL I use for this looks something like this:

db2 "select stmt_text, rows_read, rows_written, stmt_sorts, sort_overflows, num_executions, rows_read/num_executions avg_rows_per_exec from sysibmadm.snapdyn_sql order by rows_read desc fetch first 5 rows only with ur"

Ordering by different values for CPU, etc. Then assuming it’s not a one-time execution, I analyze the queries to see what I can do for them.

Deadlock event monitor

If you have deadlocks where SELECT statements are involved, it is important to analyze the SQL for them. The general approach to making this work is:

• Make sure you have a detailed deadlock event monitor on at all times. DB2 has one on by default, but it can quickly run out of space. I usually re-create it with more space using something like this:

db2 "create event monitor emb_detaildeadlock for deadlocks with details write to file 'emb_detaildeadlock' maxfiles 2000 maxfilesize 10000 blocked append autostart"
db2 "set event monitor emb_detaildeadlock state=1"
db2 "set event monitor db2detaildeadlock state=0"
db2 drop event monitor db2detaildeadlock

• Flush the event monitor

db2 flush event monitor emb_detaildeadlock

• Convert the event monitor output to human-readable format, replacing with the appropriate path for your database

db2evmon -path /db_home/NODE0000/db_seq_num/db2event/emb_detaildeadlock

• Look through or write a script to analyze the output

SQL from stored procedures

So I’ll actually cover this in a post on how to analyze SQL. You cannot usually just copy the SQL out of a stored procedure to explain it – it requires different techniques. You will see the execution of stored procedures show up in the dynamic sql snapshot, so you’ll be able to rank them with other queries to know if you need to analyze them.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home