Db2:後からスロークエリーを見つけたい

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

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

データベースの性能を評価したい方は以下の記事もご覧ください。
[Db2:性能評価にSQL平均応答時間を用いる](https://qiita.com/tsunenorikan/items/6cf055daef790838c305 "Qiita")

# MON_CURRENT_SQL 管理ビュー

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

```sql: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 - 全メンバーの全アクティビティーに関する主要なメトリックの取得](https://www.ibm.com/docs/ja/db2/11.5?topic=mv-mon-current-sql-retrieve-key-metrics-all-activities-all-members "IBM")

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

```log: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

```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カ月以上前のレコードは削除するようにもします。

```sql: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が経過時間が長い順に出力されます。 

```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

```log: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

```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を調査する必要があると思います。