はじめに
サーバーリプレースでOracle 11gからPostgreSQL 9.6に移行し性能検証をしています。
性能検証ということでデータ量をかなり多くしてOracle 11gとPostgreSQL 9.6で既存アプリケーションを使用してみるわけです。
今年リリースしたASP.NETアプリケーションがあるのですが、PostgreSQL 9.6では少し時間がかかるものの抽出結果が表示されてくるのですが、Oracle 11gではまったく結果が返ってこないのです。
これでは性能検証として比較するどころではないわけです。
調査
Oracle 11gで実行したSQLをデータベース接続ツールで実行すると結果が返ってくるわけです。
データベース接続ツールはJDBCドライバーを使用しており、ASP.NETアプリケーションではODP.NETを使用しています。そこで違いが何かあるのかと、フォームとボタンのみの簡易アプリケーションを作成してSQLを実行すると結果が返ってくるわけです。
実際のアプリケーションでは、クエリパラメータ(バインド変数)を使用しており、データベース接続ツールで実行する際にはバインド変数のパラメーターを置き換えた状態で実行しておりました。
簡易アプリケーションでも同様にクエリパラメータ(バインド変数)を使用した状態に書き換えて実行してみたところ、結果が返ってこない状態になりました。
原因
ネットで「Oracle 結果が返ってこない バインド」で検索すると「バインドピーク」というキーワードが引っ掛かってきました。そこで「バインドピーク」で検索すると下記サイトが見つかりました。
- [Oracle] バインド・ピーク機能の落とし穴
- 門外不出のOracle現場ワザ 第4章 Oracleデータベースの頭脳 「オプティマイザ」徹底研究
- バインドピーク機能とは
- バッチ処理にバインド変数はもうやめません？ ~|バッチ処理の突発遅延を題材にして考えてみる~ SlideShare
バインドピーク機能とは
バインドピーク機能は9iから追加された新機能で、バインド変数を含むSQL文でオプティマイザが実行計画を生成する際、実際にバインド変数にセットされた値を考慮(覗く(peek)し実行計画を立てることができるようになる機能です。
バインドピーク機能のメリット
内部デフォルト値ではなく、ユーザーが指定した実際の値(バインド変数)と統計を利用できるため、より適切な実行計画が選択されるようになります。
バインドピーク機能のデメリット
バインド変数にセットされる値が不定の場合、実行計画が安定しなくなる。
ハードパース時にその時にバインド変数にセットされていた値をもとに実行計画が立てられますが、 これは統計情報に変化がなくともハードパース時にセットされていたバインド変数値によって実行計画が変わってしまう可能性がある。
11g以降、このデメリットを解消しうる機能として11gR1で優れたカーソル共有、11gR2でカーディナリティフィードバック、12cR1で適応計画といった新機能が追加されていますが、いずれの機能もこのデメリットを完全に払拭できていない。
主原因
抽出条件を変えながらSQLを10回程度実行してテーブルに登録しています。
SQLは同じテーブルを自己結合しているのですが、抽出条件によってレコード数の落差が激しいのではないかと、一番最初に抽出するレコードが少ない状態で実行計画が作成され、多い状態の際にフルスキャン状態になってしまっているではないかと考えています。
バインド変数化を盲目的に推奨する風潮
バインド変数化を盲目的に推奨する風潮？
• バインド変数化の有無は、開発者がそれぞれ のSQLごとに判断する必要がある
– 現在のOracle Databaseでは・・・残念ながら
• しかし、盲目的に「バインド変数化＝善」と判断している風潮が見られる
– コーディング規約でのルール化
– DBアクセスロジックを過度に共通化突発的なパフォーマンスダウンを避けるため、盲目的にバインド変数を使用することは避けてほしい
バッチ処理にバインド変数はもうやめません？ ~|バッチ処理の突発遅延を題材にして考えてみる~ SlideShare
SQLをバインド変数にして実行することは、パフォーマンスを良くする、SQLインジェクション対策などのメリットがあり、デメリットがあることは考えたことがありませんでした。コーディングルールでも指定されてたりと、まさに指摘されてるように盲目的に「バインド変数化＝善」という考えでした。
対応
| No | 対処策 | 説明 |
|---|---|---|
| 1 | SQLをリテラル（バインド変数を使用しない）に修正 | リテラル値（≒WHERE条件）毎に実行を作成 →異なるWHERE条件が指定された SQLに対して、1つの実行計画を使いまわさないようになる |
| 2 | 実行計画を誘導（ヒント、アウトライン、SQL実行計画管理） | 指定した実行計画が作成される →実行計画がバインド変数値に依存しないようになる |
| 3 | バインドピークを無効化(_optim_peek_user_binds=false) | デフォルト値を基準に実行計画を作成する →実行計画がバインド変数値に依存しないようになる |
1のSQLをリテラルに修正、プログラム的にあまり変更しないようにSQLはバインド変数を残したまま、実行前に値に置換する関数を作成しました。
ところが、SQLをリテラルに修正したにも関わらず、抽出条件によって遅いものが出てきてしまいました。よって、2の実行計画を誘導のヒント句を追加しました。
OracleとPostgreSQLでプログラムは共通化しているため、Oracleのみヒント句を追加。
※3のバインドピークを無効化は、Oracle上は「隠しパラメータ」で通常は「サポートセンターの指示なしでは設定すべきでない」パラメータなので今回は対策として除外します。
最後に
対応して結果は返ってくるようになったものの、PostgreSQLとの速度差は歴然としている。
SQL自体をそもそも見直す必要があるのかも知れません。