さっき、データベースが遅かった

よく言われる言葉です。今触っても遅くありません。遅かった時に実行されていたSQL、いわゆるスロークエリーが何かを知るにはどうすればいいでしょうか。

この記事では後からスロークエリーを見つける方法を紹介します。
環境は IBM Db2 V11.5 Linux と Ubuntu 20.04 LTSです。

データベースの性能を評価したい方は以下の記事もご覧ください。
Db2:性能評価にSQL平均応答時間を用いる

MON_CURRENT_SQL 管理ビュー

現在実行中のSQLを表示するビューです。SQLでselectして照会します。列が多いので必要な列のみをselectします。

select_current_sql.sql

select current timestamp,
       application_handle,
       application_name,
       application_id,
       activity_state,
       elapsed_time_sec,
       total_cpu_time,
       rows_read,
       substr(stmt_text,1,100)        as stmt_text
from   sysibmadm.mon_current_sql ;

列の詳細についてはIBMのウェブサイトをご覧ください。
MON_CURRENT_SQL - 全メンバーの全アクティビティーに関する主要なメトリックの取得

Db2コマンド・ウィンドウからSQLを実行します。
db2 -tf select_current_sql.sql

select_current_sql.sqlの実行結果

$ db2 -tf select_current_sql.sql

1                          APPLICATION_HANDLE   APPLICATION_NAME                                                                                                                 APPLICATION_ID                                                                                                                   ACTIVITY_STATE                   ELAPSED_TIME_SEC TOTAL_CPU_TIME       ROWS_READ            STMT_TEXT                                                                         
-------------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------- ---------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------
2021-11-04-16.26.57.938503                14226 db2bp                                                                                                                            *LOCAL.blancdba.211104070911                                                                                                     EXECUTING                                       0                    0                    0 select current timestamp, application_handle, application_name, application_id, activity_state, elap

  1 レコードが選択されました。

$

実行したSQLそのものが表示されます。
ACTIVITY_STATEが活動状態、ELAPSED_TIME_SECが経過時間(秒)になります。

この管理ビューを毎分selectして、活動状態がEXECUTINGかつ経過時間が1秒以上のSQLワースト5を記録します。

記録用テーブルの作成

遅かったSQLを記録するテーブル（例では、mon_get_sql）を作成します。
Db2コマンド・ウィンドウから以下のSQLを実行します。
db2 -tf create_mon_get_sql.sql

create_mon_get_sql.sql

create table mon_get_sql  (
    ts,  
    application_handle,
    application_name,
    application_id,
    activity_state,
    elapsed_time_sec,
    total_cpu_time,
    rows_read,
    stmt_text
) as (
select current timestamp, 
    application_handle,
    application_name,
    application_id,
    activity_state,
    elapsed_time_sec,
    total_cpu_time,
    rows_read,
    stmt_text
from sysibmadm.mon_current_sql )
with no data in USERSPACE1 ;

create index idx_mon_get_sql_1 on mon_get_sql 
   (ts     asc) allow reverse scans ;

照会結果をインサートするSQLの作成

遅かったSQLをインサートするSQLファイルを作成します。インサートする条件は、活動状態がEXECUTINGかつ経過時間が1秒以上のSQLワースト5です。1カ月以上前のレコードは削除するようにもします。

insert_mon_get_sql_qiita01

connect to qiita01 ;

delete from mon_get_sql where ts < current timestamp - 720 hour ;

insert into mon_get_sql 
select current timestamp,
       application_handle,
       application_name,
       application_id,
       activity_state,
       elapsed_time_sec,
       total_cpu_time,
       rows_read,
       stmt_text
from   sysibmadm.mon_current_sql
where  elapsed_time_sec >= 1
  and  activity_state = 'EXECUTING'
order  by elapsed_time_sec desc
limit 5 ; 

connect reset ;

スケジュール実行の設定

インサートするSQLをLinuxならcron、Windowsならタスクスケジューラで毎分実行するように設定します。

記録された結果の参照

照会用のSQLファイルを作成します。elapsed_time_secを降順に指定することにより、遅いSQLが経過時間が長い順に出力されます。

select_mon_get_sql.sql

select date(ts)                       as date ,
       time(ts)                       as time ,
       application_handle ,
       substr(application_name,1,15)  as application_name ,
       substr(application_id,1,30)    as application_id ,
       substr(activity_state,1,15)    as activity_state ,
       elapsed_time_sec ,
       total_cpu_time, 
       rows_read , 
       substr(stmt_text,1,100)        as stmt_text
from   mon_get_sql
where  date(ts) = current date - 0 day 
  and  time(ts)   between '08:00:00' and '17:00:00'
order  by elapsed_time_sec desc, ts desc 
limit  50 
for read only ;

Db2コマンド・ウィンドウからSQLファイルを実行します。
db2 -tf select_mon_get_sql.sql

select_mon_get_sql.sqlの実行結果

$ db2 -tf select_mon_get_sql.sql

DATE       TIME     APPLICATION_HANDLE   APPLICATION_NAME APPLICATION_ID                 ACTIVITY_STATE  ELAPSED_TIME_SEC TOTAL_CPU_TIME       ROWS_READ            STMT_TEXT                                                                   
---------- -------- -------------------- ---------------- ------------------------------ --------------- ---------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------
2021-11-01 14:04:22                17128 testhost.exe     192.168.201.6.57745.2111010504 EXECUTING                      1                    0                    0 SELECT HSP_ID, ORD_SYBT, JISSI_DT, SEQ_SYBT, BUMON_NO FROM TC00BMN2 WHERE HSP_ID = '000001' AND ORD_
2021-11-01 13:56:22                16593 testhost.exe     192.168.201.6.65194.2111010456 EXECUTING                      1                    0                    0 UPDATE TPTUKE SET UKE_KBN = '1', UP_DT = '2021-11-01', UP_TM = '13:56:21', UP_USR = '999999', EXAMI_

  2 レコードが選択されました。

$

記録したSQLの抽出

記録したSQLは以下のSQLを実行することによりexportできます。where句でexportしたいSQLの条件を指定してください。
db2 -tf export_stmt_text.sql

export_stmt_text.sql

export to out_stmt_text.sql of del modified by nochardel 
select stmt_text || ' ;'
from   mon_get_sql
where  date(ts) = '2021-10-21'
  and  time(ts) = '11:36:22'
  and  application_handle = '13179'
for read only ;

まとめ

1分に1回の収集なのでスロークエリーの全てを補足できるわけではありません。逆に言うと、それで頻繁に記録されているようなら、記録されているSQLを調査する必要があると思います。