はじめに
皆さんはOracleのSQLのパフォーマンス問題にぶつかったことはありませんか？
先日自分もパフォーマンス問題にぶつかり、その際に改善案として挙がったもののうち、
今回はOracleのSQLのパフォーマンスを向上させる手法の1つであるハードパースの回数を減らすという方法について、実際に検証してみた結果についての共有になります。
あくまで実験に基づく仮説のようなものになりますので、間違いや指摘などあれば教えていただけるとありがたいです。
ハードパースについて
OracleでSQL実行される際には、SQLの解析結果が共有プールに存在する場合それを使用することができ、これをソフトパースといいます。
逆に解析結果が存在しない場合にはまず実行するSQLの解析が行われ、それを使用することになり、これをハードパースといいます。
単純にハードパースの方が手順が多いこともありますが、この解析によってかなりの負荷がかかるため一般的にできるだけハードパースを減らすことがパフォーマンスの改善に役立つとされています。
ではハードパースを減らすにはどういった方法があるかというと下記3つのような方法があります。
- SQLが同じになるようにする(大文字や小文字、スペースなど)
- バインド変数を使用する(変動する条件部分)
- 条件が異なる場合でも解析結果を使用するようにパラメータ(CURSOR_SHARING)を変更する
この中で2つ目のバインド変数についてもう少し触れていきます。
バインド変数
Oracleにおけるバインド変数というのはSQL内で使用できる変数のことで、主に条件に使用する値を変数に置き換えて、動的なSQL文を実行できます。
バインド変数は文字型と数値型しか使用できず、作成するコマンドは下記になります。
VARIABLE 変数名 変数の型
例)
VARIABLE MOJI VARCHAR2(20); 文字型
VARIABLE SUJI NUMBER; 数値型
作成したバインド変数に実際の値を入れるコマンドは下記になります。
EXECUTE :変数名 := 入れる値
例)
execute :MOJI := バインド;
execute :SUJI := 2023;
このようなバインド変数を使用したSQLは下記のようになります。
SELECT * FROM TABLEA WHERE COLUMN_A = :MOJI AND COLUMN_B = :SUJI;
バインド変数の効果(トレース)
ここからは実際にバインド変数を使用したSQLと使用しなかったSQLを使ってどう違いが出るのかを確認します。
今回は以下のような条件でSQLを実行した際のトレースファイルを確認します。
- パラメータ違いのSQLを2つ(SQL_A, SQL_B)を使用し、これをグループ1とします。
- さらに2つのSQLにバインド変数を使用したSQLを2つ(SQL_A', SQL_B')を使用し、これをグループ2とします。
- 各グループのSQL実行開始前に共有プールのクリアを実行します。
$ sqlplus user/pass@SID
$ ~トレース出力の設定の実行~
$ SQL_A実行
$ select value from v$diag_info where name='Default Trace File'; トレースファイルの確認
$ exit
$ tkprof {確認したトレースファイルのパス} {任意の名前}.prof sys=no
$ sqlplus user/pass@SID
$ ~トレース出力の設定の実行~
$ SQL_B実行
$ select value from v$diag_info where name='Default Trace File'; トレースファイルの確認
$ exit
$ tkprof {確認したトレースファイルのパス} {任意の名前}.prof sys=no ※1
上記の手順を各グループにした後に、※1で取得したファイルの中身を確認したところ下記のようになっていました。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 6.78 6.79 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 516 2.00 3.94 0 130294 0 7719
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 518 8.78 10.73 0 130294 0 7719
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 516 0.61 2.23 0 129368 0 7719
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 518 0.61 2.23 0 129368 0 7719
この2つはどちらも実行SQL部分のものになりますが、変数部分のみが変わったSQLを2回目に実行した際に、
グループ1(バインドしていない)の場合2回目にもかかわらずパースにリソースを使っているように見えます。
対してグループ2(バインドしている)の場合2回目にはパースにリソースを使用していないように見えます。
実際にハードパースが実行されたかの確認として下記SQLを実行してみると、
SQL_TEXT | SQL_FULLTEXT
SQL_A | SQL_A
SQL_B | SQL_B
SQL_TEXT | SQL_FULLTEXT
SQL_A' | SQL_A'
上記のようにグループ1では実行したSQLが2つとも入っている状態であることが確認でき、
グループ2ではバインドしたSQLのみが入っているために共有プールには1つしかありません。
これによってバインド変数を使用した場合パラメータが異なる場合でもハードパースを回避できているように見受けられます。
では次に実際の運用をイメージしてSQLの速度に影響があるのかを検証してみます。
バインド変数の効果(速度)
今回の検証では検証用の処理をJavaで記述して実行していきます。
実行にあたって次のような条件で検証します。
- パラメータ違いのSQLを5つ(SQL_A, SQL_B, SQL_C, SQL_D, SQL_E)を使用し、これをグループ3とします。またグループ3のSQLを連続で実行します。
- グループ1のSQLのパラメータをバインド変数に置き換えたSQL(SQL_BIND)を使用し、これに5つのパラメータを渡して実行したものをグループ4とします。またグループ4のSQLを連続で実行します。
- 各グループの実行開始前に共有プールをクリアします。
- グループ3とグループ4を交互に5回ずつ計測します。
検証に使用したソースは下記のようになります。
package jp.test.sql.parseCompare;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.sql.Date;
import oracle.jdbc.OraclePreparedStatement;
public class ParseCompare {
static String noBind1 = SQL_A;
static String noBind2 = SQL_B;
static String noBind3 = SQL_C;
static String noBind4 = SQL_D;
static String noBind5 = SQL_E;
static String bindSql = SQL_BIND;
static String[] noBinds = {noBind1, noBind2, noBind3, noBind4, noBind5};
static String jdbc = "jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:SID";
static String user = "user";
static String pass = "pass";
public static void main( String[] args ) {
int bindTyp = Integer.parseInt(args[0]);
int sqlNo = Integer.parseInt(args[1]);
long start = System.currentTimeMillis();
if (bindTyp == 0) {
noBindExecute(noBinds[sqlNo]);
} else {
bindExecute(sqlNo);
}
long end = System.currentTimeMillis();
System.out.print("実行時間：" + (end - start));
}
public static void noBindExecute(String sql) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
System.out.println(sql);
con = DriverManager.getConnection(jdbc, user, pass);
st = con.createStatement();
rs = st.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void bindExecute(int sqlNo) {
Connection con = null;
OraclePreparedStatement ops = null;
ResultSet rs = null;
SimpleDateFormat sdf = new SimpleDateFormat("YYYY/MM/DD");
Date date = Date.valueOf("2023-07-06");
String paramA;
String paramB;
String paramC;
String paramD;
String paramE;
switch (sqlNo) {
case 1:
date = Date.valueOf("2023-06-06");
paramA = "AA";
paramB = "BB";
paramC = "CC";
paramD = "DD";
paramE = "EE";
break;
case 2:
date = Date.valueOf("2023-05-06");
paramA = "AAA";
paramB = "BBB";
paramC = "CCC";
paramD = "DDD";
paramE = "EEE";
break;
case 3:
date = Date.valueOf("2023-04-06");
paramA = "AAAA";
paramB = "BBBB";
paramC = "CCCC";
paramD = "DDDD";
paramE = "EEEE";
break;
case 4:
date = Date.valueOf("2023-03-06");
paramA = "AAAAA";
paramB = "BBBBB";
paramC = "CCCCC";
paramD = "DDDDD";
paramE = "EEEEE";
break;
default:
paramA = "A";
paramB = "B";
paramC = "C";
paramD = "D";
paramE = "E";
}
try {
con = DriverManager.getConnection(jdbc, user, pass);
ops = (OraclePreparedStatement) con.prepareStatement(bindSql);
ops.setDateAtName("DATE_PARAM", date);
ops.setStringAtName("PARAMA", paramA);
ops.setStringAtName("PARAMB", paramB);
ops.setStringAtName("PARAMC", paramC);
ops.setStringAtName("PARAMD", paramD);
ops.setStringAtName("PARAME", paramE);
ops.setIntAtName("PARAMF", 0);
ops.setIntAtName("PARAMG", 0);
ops.setIntAtName("PARAMH", 1);
ops.setIntAtName("PARAMI", 0);
ops.setIntAtName("PARAMJ", 0);
rs = ops.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (ops != null) {
ops.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
実際の計測結果をまとめたものが下記になります。
グループ3
| 1回目 | 2回目 | 3回目 | 4回目 | 5回目 |
|---|---|---|---|---|
| 実行時間：2963 | 実行時間：2580 | 実行時間：2640 | 実行時間：2530 | 実行時間：2745 |
| 実行時間：2690 | 実行時間：2680 | 実行時間：2705 | 実行時間：2627 | 実行時間：2544 |
| 実行時間：2629 | 実行時間：2580 | 実行時間：2650 | 実行時間：2539 | 実行時間：2475 |
| 実行時間：2608 | 実行時間：2609 | 実行時間：2700 | 実行時間：2524 | 実行時間：2711 |
| 実行時間：2751 | 実行時間：2565 | 実行時間：2655 | 実行時間：2641 | 実行時間：2478 |
グループ4
| 1回目 | 2回目 | 3回目 | 4回目 | 5回目 |
|---|---|---|---|---|
| 実行時間：3847 | 実行時間：2809 | 実行時間：3270 | 実行時間：3637 | 実行時間：3724 |
| 実行時間：2154 | 実行時間：2920 | 実行時間：2375 | 実行時間：2169 | 実行時間：2295 |
| 実行時間：2242 | 実行時間：2177 | 実行時間：2252 | 実行時間：2178 | 実行時間：2282 |
| 実行時間：2453 | 処理時間：2226 | 実行時間：2164 | 実行時間：2130 | 実行時間：2380 |
| 実行時間：2136 | 実行時間：2387 | 実行時間：2155 | 実行時間：2442 | 実行時間：2338 |
元の処理時間があまり大きくないのでわかりづらいかもしれませんが、
5回実行してグループ3はほとんどが2500msを超えていることがわかります。
一方グループ4では初回のみグループ3と比較しても時間がかかっていますが、2つ目のSQL以降はほとんどのSQLが2500msを切っており半分以上が2300msを下回っていることがわかります。
おわりに
トレースの出力内容と実際の速度の計測から、バインド変数を利用することでパフォーマンスが改善できていそうなことが確認できました。
実際検証してみた感想としてはおもったよりきれいに結果の違いが出てきて面白かったです。
今後も色々と実験していきたいと思います。
参考にさせていただいたページ
バインド変数
OraclePreparedStatement
トレースファイルの整形