LoginSignup
6
1
@newcastlein株式会社Works Human Intelligence

Oracleのバインド変数の効果を検証してみる(Java)

Posted at

はじめに

皆さんは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は下記のようになります。

実行SQL
SELECT * FROM TABLEA WHERE COLUMN_A = :MOJI AND COLUMN_B = :SUJI;

バインド変数の効果(トレース)

ここからは実際にバインド変数を使用したSQLと使用しなかったSQLを使ってどう違いが出るのかを確認します。
今回は以下のような条件でSQLを実行した際のトレースファイルを確認します。

  1. パラメータ違いのSQLを2つ(SQL_A, SQL_B)を使用し、これをグループ1とします。
  2. さらに2つのSQLにバインド変数を使用したSQLを2つ(SQL_A', SQL_B')を使用し、これをグループ2とします。
  3. 各グループの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で取得したファイルの中身を確認したところ下記のようになっていました。

グループ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
グループ2
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を実行してみると、

v$sqlstats(グループ1)
SQL_TEXT	| SQL_FULLTEXT
SQL_A       | SQL_A
SQL_B       | SQL_B
v$sqlstats(グループ2)
SQL_TEXT	| SQL_FULLTEXT
SQL_A'      | SQL_A'

上記のようにグループ1では実行したSQLが2つとも入っている状態であることが確認でき、
グループ2ではバインドしたSQLのみが入っているために共有プールには1つしかありません。
これによってバインド変数を使用した場合パラメータが異なる場合でもハードパースを回避できているように見受けられます。
では次に実際の運用をイメージしてSQLの速度に影響があるのかを検証してみます。

バインド変数の効果(速度)

今回の検証では検証用の処理をJavaで記述して実行していきます。
実行にあたって次のような条件で検証します。

  1. パラメータ違いのSQLを5つ(SQL_A, SQL_B, SQL_C, SQL_D, SQL_E)を使用し、これをグループ3とします。またグループ3のSQLを連続で実行します。
  2. グループ1のSQLのパラメータをバインド変数に置き換えたSQL(SQL_BIND)を使用し、これに5つのパラメータを渡して実行したものをグループ4とします。またグループ4のSQLを連続で実行します。
  3. 各グループの実行開始前に共有プールをクリアします。
  4. グループ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

トレースファイルの整形

6
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
1