Db2:REOPT ONCEでスロークエリーを発生させたくない

# 背景
[OLTP](https://www.ibm.com/jp-ja/topics/oltp "IBMウェブサイト")のようなSQLを短時間に大量に発行するアプリケーションでは、SQLのwhere条件の値に**リテラル(文字列)を使うと値が異なるたびにハードパース**(アクセスプランの作成)が発生してDBサーバーのCPU使用率が高くなります。where条件の値に**バインド変数(パラメーターマーカー)を使うと値が異なってもソフトパース**(キャッシュにあるアクセスプランを再利用)になるため、CPU使用率の高騰を防ぐことができます。

ただし、処理速度から考えると**実際の値から統計情報を考慮してアクセスプランを作成できるリテラルの方がバインド変数より優れています。** この課題を解決する方法として、Db2には**バインド変数を使っていても実際の値を使ってアクセスプランを作成するREOPT**という機能があります。

ちなみにDb2のReoptに相当する機能は他のDBMSにもあります。**Oracleならバインドピーク(Bind Peek)、SQL Serverならパラメータースニッフィング(Parameter Sniffing)です。**

# 概要
REOPTには3種類の設定があります。NONEがデフォルトです。

- NONE　：値をセットせず、バインド変数のままSQLを実行
- ONCE　：最初のSQLを実行時に値をセットしてハードパース、以降はソフトパース
- ALWAYS：SQLを実行するたびに値をセットしてハードパース

REOPTはSQL単位、データソース単位などで設定できます。TomcatのJDBC設定で**ONCEをデフォルトとして指定しておいて特定のSQLのみALWAYSにすることも可能**です。

| 設定 | 処理速度 | CPU使用率 | 安定性 | デメリット |
|:-:|:-:|:-:|:-:|:-:|
| NONE  | △ | 〇  | 〇  | アクセスプランが最適にならない可能性  |
| ONCE  | 〇  | 〇  | △  | 最初のセットが外れ値だとスロークエリー   |
| ALWAYS  | 〇  | △  | 〇  | 毎回ハードパースするため、CPUを消費  |

REOPTの詳細はIBMのサイトに記載されています。
[[DB2 LUW] パラメーターマーカー使用時の SQL パフォーマンス](https://www.ibm.com/support/pages/node/464851 "IBMウェブサイト")

# 問題点
処理速度とCPU使用率から考えるとデフォルトはREOPT ONCEを使いたいです。**REOPT ONCEの弱点は最初にセットされる値によってスロークエリーになる可能性があることです。**[OLTP](https://www.ibm.com/jp-ja/topics/oltp "IBMウェブサイト")アプリケーションですと、ほとんどのSQLは単純ですので最初にセットされる値でスロークエリーになること少ないと思いますが、**一部の複雑なSQLは最初にセットされる値によってスロークエリー**になる場合があります。

この最初の値が**外れ値問題**は、Oracleのバインドピーク、SQL Serverのパラメータースニッフィングも同じ問題を抱えています。

例えば、データ量の偏りが大きいテーブルで1回目のselectの条件はデータ量の検索が少ない値、2回目はデータ量の検索が多い値の場合、1回目で作成されたアクセスプランを2回目の処理が再利用することによりスロークエリーになるケースです。特にジョインを多用するSQLの場合、データ量が少ない場合とデータ量が多い場合とでアクセスプランが違ってスロークエリーになる印象があります。多くの場合、**データ量が少ない場合もデータ量が多いアクセスプランを使っても体感速度では変わらないので、データ量が多いアクセスプランを採用してスロークエリーになるのを避けたい**と思います。

スロークエリーになるSQLへの対策は以下が挙げられます。
| No. | 対策 | デメリット |
|:-:|:-:|:-:|
| 1  | 対象SQLのみをREOPT NONE  | アクセスプランが最適にならない  |
| 2  | 対象SQLのみをREOPT ALWAYS  | CPU使用率が増加  |
| 3  | 対象SQLをヒント句でIndex指定  | 人間がアクセスパスを要解析  |
| 4  | 対象SQLのみをリテラルで記述  | CPU使用率が増加  |
| 5  | 対象SQLをシンプルに書き換える | 時間と工数がかかる  |
| 6  | 対象SQLを最適値で定期実行  | ピーク時にキャッシュアウトの可能性  |

1～3はSQLにヒント句(最適化ガイドライン)を指定することにより実装できます。
**1～5までが王道な対応と思いますが人間系の問題が伴います。SQLの修正はアプリケーションの修正なので開発部門に依頼、時間と人手がかかります。** 6は邪道な気がしますが今回説明する内容です。

# やりたいこと
スロークエリーになるSQLに対し、**where条件の値に最適値をセットしたSQLを用意、シェルスクリプトで定期実行することによりキャッシュに常に最適なアクセスプランがあるようにする(以降、SQL学習スクリプト)。**

- オフピーク時にパッケージキャッシュをフラッシュ
- フラッシュ直後にSQL学習スクリプトを実行
- 定期的にSQL学習スクリプトの実行を繰り返す

お手本SQLのメリットは以下が挙げられます。
| No. | メリット | 説明 |
|:-:|:-:|:-:|
| 1  | アプリケーション修正不要 | DB管理者がすぐに対応可能  |
| 2  | 外れ値がセットされたかを確認可能 | 最適値で実行により判断可能  |
| 3  | システムに対する影響が低い | selectしているだけなので  |

- 1のメリットはスロークエリーが発生しても**当日から翌日のスピード感で対応できること**です。アプリケーション修正でこのスピード感は厳しい会社もあるかと思います。
- 2はスロークエリーの原因が外れ値問題とは限らないのでそれかどうかを確認できるのがメリットです。**開発部門に対策1～5をやってもらうにしても前提として外れ値問題である証拠が必要**です。
- 3はアプリケーション修正ではなくSelectのSQLを実行しているだけなのでリスクが低いのがメリットです。

時節より**SQL学習スクリプト**の実装を説明します。

# SQL学習スクリプトの実装

### 処理内容

1. シェルスクリプトからJavaサーブレットをコンパイル、実行
1. JavaサーブレットはJDBC経由でSQLを発行

### 環境
アプリケーションの開発言語はJavaです。SQL学習スクリプトもAPサーバーと同じ環境で稼働させます。

- DBサーバー：IBM Db2 V11.5, Ubuntu 20.04 LTS
- APサーバー：Tomcat 9, JDK 8, Ubuntu 18.04 LTS

### パッケージキャッシュの種類
Db2は同じSQLを実行しても環境によってパッケージキャッシュが別になります。最初、SQLが同じならパッケージキャッシュも同じと考え、**シェルスクリプトからDb2コマンドで同じSQLを実行したのですが、Javaアプリケーションとは別のパッケージキャッシュになりました。** 対象アプリケーションとSQL学習スクリプトとの環境を合わせる必要があります。以下の単位でパッケージキャッシュが分かれることがわかりました。

- 開発言語：ドライバーが違うので分かれると思います。今回はJavaなのでJDBCです。
- 分離レベル：UR, CS, RS, RRでパッケージキャッシュが分かれます。
- SQL発行方式：Javaの場合、プリペアードステートメントかどうかでパッケージキャッシュが分かれます。

### スロークエリーの収集

Qiitaの記事、[Db2:後からスロークエリーを見つけたい](https://qiita.com/tsunenorikan/items/cd915bf9c0b9307db3e5 "Qitta")を参考にスロークエリーを収集します。後述する[ステートメント・コンセントレーター](https://www.ibm.com/docs/ja/db2/11.5?topic=plans-statement-concentrator "IBMウェブサイト")でリテラルをバインド変数に変換している場合、値は変換前のリテラルが表示されます。値をバインド変数で書いている場合、セットすべき最適値を開発部門に確認するか、IBMのサイト、[DB2: SQLステートメントのパラメーター・マーカーにセットされた値を確認する方法](https://www.ibm.com/support/pages/node/648145 "IBMウェブサイト")を参考にスロークエリーでセットされている値を確認します。

Db2にはリテラルで書かれているSQLをバインド変数のSQLに変換する[ステートメント・コンセントレーター](https://www.ibm.com/docs/ja/db2/11.5?topic=plans-statement-concentrator "IBMウェブサイト")という機能があります。**全てのリテラルをバインド変数のSQLに変換するという大胆なことをします**が、SQLがリテラルで書かれているアプリケーションをバインド変数に修正する時間と人手がない場合にはありがたい機能です。

### Javaプログラムの準備
Db2のマニュアルにあるサンプルプログラム、[単純な JDBC アプリケーションの例](https://www.ibm.com/docs/ja/db2/11.5?topic=SSEPGG_11.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_cjvjdbas.htm "IBMウェブサイト")を参考にJavaプログラムを準備します。**サンプルは通常ステートメントでSQLを発行していますが、アプリケーションがプリペアードステートメントで発行している場合はそのように書き換えます。** スロークエリーをプログラムの中で書くか、便利に使えるようにSQLファイルにして外出しにするかは適宜にお願いします。

### シェルスクリプトの準備

Javaをコンパイルして実行しているだけのスクリプトです。接続文字列に**currentPackageSet=NULLIDR1**を追加することによってREOPT ONCEで実行されます。

```shell:studysql.sh
#!/bin/bash

SQL_DIR=/db2jcc/execfiles
JDK_DIR=/usr/lib/jvm/temurin-8-jdk-amd64/bin
DB2_JAR=/db2jcc/lib/db2jcc4.jar
ISO_UR=defaultIsolationLevel=1\;
CON_URL=//192.168.1.1:50000/appdb01:currentSchema=MYSCHEMA\;currentPackageSet=NULLIDR1\;
DB_USERNAME=myuser
DB_PASSWORD=mypassword

# コンパイル
$JDK_DIR/javac -cp $SQL_DIR:$DB2_JAR $SQL_DIR/EzJava.java > $SQL_DIR/javac_EzJava.log   2>&1

# SQLを実行して結果をファイル出力：分離レベルCS
$JDK_DIR/java -cp $SQL_DIR:$DB2_JAR EzJava   $CON_URL        $DB_USERNAME $DB_PASSWORD > $SQL_DIR/EzJava_cs.log 2>&1

# SQLを実行して結果をファイル出力：分離レベルUR
$JDK_DIR/java -cp $SQL_DIR:$DB2_JAR EzJava   $CON_URL$ISO_UR $DB_USERNAME $DB_PASSWORD > $SQL_DIR/EzJava_ur.log 2>&1
```

注意点としてはアプリケーションが分離レベルCSでSQLを実行している場合、CSだけでなく分離レベルURでもSQLを学習させる必要があります。スクリプトでは接続文字列に**defaultIsolationLevel=1**を追加してURで実行しています。**どうやらDb2はアプリケーションが実行したSQLを裏で分離レベルURにして再実行しているようです。そのため、UR用のアクセスプランも用意しないとDb2が裏で実行したSQLがスロークエリーになる可能性が大です。**

### スクリプトのスケジュール
オフピーク時に以下のコマンドでパッケージキャッシュをフラッシュ、直後にSQL学習スクリプトを実行、その後、定期的に繰り返すようにcronに設定します。定期的に繰り返す間隔は後述のパッケージキャッシュの生存時間をもとに決めてください。
```
db2 flush package cache dynamic
```

# パッケージキャッシュの確認方法

モニター表関数でパッケージキャッシュの中身を確認できます。where条件にSQLステートメントの前半をlike節で記述して指定のSQLを見ることが可能です。

```sql:mon_mytable_01.sql
SELECT DATE(insert_timestamp)                 AS INS_DATE,
       TIME(insert_timestamp)                 AS INS_TIME,
       DATE(last_metrics_update)              AS UPD_DATE,
       TIME(last_metrics_update)              AS UPD_TIME,
       stmt_exec_time / num_exec_with_metrics AS exec_time_per_num_#,
       rows_read / num_exec_with_metrics      AS rows_read_per_num,
       num_exec_with_metrics,
       query_cost_estimate,
       effective_isolation                    as iso ,
       Rtrim(stmt_text)                       AS statement,
       executable_id
FROM   (SELECT t.*,
               RANK()
                 OVER(
                   PARTITION BY executable_id
                   ORDER BY (stmt_exec_time / num_exec_with_metrics )desc) AS RANK
        FROM   TABLE(Mon_get_pkg_cache_stmt(NULL, NULL, NULL, -2)) t
        WHERE  stmt_exec_time > 0
               AND rows_read > 0)
WHERE  RANK = 1 
  and stmt_text like 'SELECT COL1, COL2 FROM MYTABLE%'
ORDER  BY INS_DATE, INS_TIME
FETCH first 20 ROWS only;   
```
以下のように表示されます。

```
INS_DATE   INS_TIME UPD_DATE   UPD_TIME EXEC_TIME_PER_NUM_#  ROWS_READ_PER_NUM    NUM_EXEC_WITH_METRICS QUERY_COST_ESTIMATE  ISO STATEMENT                             EXECUTABLE_ID                                                      
---------- -------- ---------- -------- -------------------- -------------------- --------------------- -------------------- --- ------------------------------------- -------------------------------------------------------------
2023-03-24 09:50:15 2023-03-24 14:10:16                   11                 1444                    76                  316 CS  SELECT COL1, COL2 FROM MYTABLE        x'0100000000000000394E0A000000000000000000020020230324095015248057'
2023-03-24 09:50:29 2023-03-24 14:10:33                   15                 2332                   159                  316 UR  SELECT COL1, COL2 FROM MYTABLE        x'0100000000000000FA4E0A000000000000000000020020230324095029971113'

  2 レコードが選択されました。
```
**insert_timestampがアクセスプランを作成した時間、last_metrics_updateがアクセスプランを再利用した時間**です。
列の詳細はDb2のマニュアルを見てください。
[MON_GET_PKG_CACHE_STMT 表関数 - パッケージ・キャッシュ・ステートメント・メトリックの取得](https://www.ibm.com/docs/ja/db2/11.5?topic=mpf-mon-get-pkg-cache-stmt-table-function-get-package-cache-statement-metrics "IBMウェブサイト")

# パッケージキャッシュの生存時間

パッケージキャッシュの生存時間はいっぱいになると古いものから削除されます。**SQL学習スクリプトはスロークエリーを最適値でアクセスプランを作成、パッケージキャッシュに常駐させるためのものなので、常駐させるアクセスプランがキャッシュアウトされないように生存時間を把握する必要があります。** アプリケーションが使わないダミーSQLをSQL学習スクリプトで定期的に発行し、パッケージキャッシュのlast_metrics_updateの日時を更新します。次のSQLで現在時刻とlast_metrics_updateの最小値との差を求めます。このSQLでcron定期実行しログを取得します。

```sql:pkg_cache_last_metrics.sql
select current timestamp as timestamp, min(last_metrics_update) as last_metrics_update, 
TIMESTAMPDIFF(4,char(current timestamp - min(last_metrics_update))) as minutes 
from table(mon_get_pkg_cache_stmt(null, null, null, -2)) 
where section_type = 'D' ;
```

以下のように表示されます。MINUTES列にパッケージキャッシュの生存時間が表示されますのでこの最小値よりも短い間隔でSQL学習スクリプトを実行します。

```
TIMESTAMP                  LAST_METRICS_UPDATE        MINUTES    
-------------------------- -------------------------- -----------
2023-03-27-08.30.17.648884 2023-03-25-16.59.40.187504        2370
2023-03-27-08.40.17.660143 2023-03-25-16.59.40.187504        2380
2023-03-27-08.50.17.716954 2023-03-25-16.59.40.187504        2390
2023-03-27-09.00.17.808559 2023-03-27-06.46.40.225944         133
2023-03-27-09.10.17.962319 2023-03-27-08.01.43.610033          68
2023-03-27-09.20.18.047991 2023-03-27-08.47.57.395576          32
2023-03-27-09.30.17.208402 2023-03-27-08.56.08.003888          34
2023-03-27-09.40.17.290989 2023-03-27-09.05.18.076547          34
2023-03-27-09.50.17.401778 2023-03-27-09.16.22.197491          33
2023-03-27-10.00.17.485806 2023-03-27-09.23.55.696520          36
2023-03-27-10.10.17.576362 2023-03-27-09.36.42.416790          33
2023-03-27-10.20.17.653890 2023-03-27-09.43.16.483966          37
2023-03-27-10.30.17.718682 2023-03-27-09.53.47.433389          36
2023-03-27-10.40.17.748468 2023-03-27-10.01.00.213119          39
2023-03-27-10.50.17.902828 2023-03-27-10.08.27.485670          41
2023-03-27-11.00.17.982862 2023-03-27-10.22.32.553582          37
```

# まとめ
REOPT ONCEの最初の値に外れ値がセットされたときにスロークエリーになる対策として最適値でアクセスプランを作成してパッケージキャッシュに常駐させるという方法を説明しました。パッケージキャッシュの生存時間に注意しなければなりませんがプログラム修正を伴う方法に比べ、スピード感のある対策かと思います。
参考までにOracleのバインドピークには最初の値に外れ値がセットされる弱点への対応策があります。Oracle 11gより、Adaptive Cursor Sharingという機能でバインド変数の値が既存のアクセスプランでは不適切だと判断した場合、新たにアクセスプランを作成します。Db2もREOPT ONCEの機能を強化してもらえればと願います。