バッチ処理にバインド変数はもうやめません？
2012/4/6 株式会社コーソル 渡部亮太
今日お伝えしたいこと
バッチ処理
SQLを
バインド変数化するの
はやめませんか？
OLTP処理SQLは バインド変数化してOKなんだけどね・・・自己紹介 + 所属企業の紹介
• 渡部 亮太（わたべ りょうた）
SE、PM を経験後、Oracle Databaseのエキスパートを目指して転職 執筆 「プロとしてのOracleアーキテクチャ入門」
「プロとしてのOracle運用管理入門」 講演 Developers Summit 2009
Oracle LOVERS シーズン2 第2回
Oracle DBA & Developer Days 2010, 2011
• 株式会社コーソル
「CO-Solutions＝共に解決する」の理念のもと、Oracle技術に特化した事 業を展開中。心あるサービスの提供とデータベースエンジニアの育成に注力して いる。社員数: 98名 （エンジニアのほぼ全員がOracle Master 所有者）
こんな現象を題材に考えます
• 稼動実績があるバッチ処理が、
突然パフ
ォーマンスダウン
するケース
– 突然SQLのパフォーマンスがダウン
– バインド変数を使用したSQL
– 試行錯誤していろいろ対処策を探っている
うちに、なぜか通常のパフォーマンスに戻った
– しかし、その後も不定期にパフォーマンスダウ
ンが再発
– 原因がつかめない・・・
正常稼働時
:
SELECT * FROM tbl0
WHERE :sval < val AND val < :eval
10 1
• 範囲検索条件をバインド変数化したSQL • 検索対象行数は比較的少ない
• INDEX RANGE SCANが適切と想定される
パフォーマンスダウン時
• TABLE FULL SCANが実行されている
• しかし、現場的には実行計画の変化に気づいていない・・・
• 不要なブロックアクセスが大量に発生
SELECT * FROM tbl0
WHERE :sval < val AND val < :eval
10 1
検索対象行
問題発生のメカニズム
• なぜこのような現象が発生するのか？
• 以下の2つの動作が影響している
– 1) バインドピーク機能 • ハードパース時に指定されたバインド変数値を元に実 行計画を作成する機能 – 2) 共有カーソル（子カーソル）の再利用 • 作成済みの共有カーソルが共有プールに存在する場 合、 2回目以降のSQL実行で再利用する仕組み • 共有カーソルには実行計画が含まれるため、実行計 画も再利用される共有プール
初回実行
:
SELECT * FROM tbl0
WHERE :sval < val AND val < :eval
検索対象行 10 1 実行＆フェッチ ハードパース バインド変数値 sval = 1 eval = 10 を元に実行計画を作成
共有プール
2回目以降の実行
: 実行＆フェッチ ソフトパース 作成済み 実行計画 を流用 SELECT * FROM tbl0WHERE :sval < val AND val < :eval
検索対象行
20 11
共有プール
共有カーソルのage-out
共有プールの領域不足、統計情報の更新など
共有プール
(age-out後の)初回実行
: 実行＆フェッチ ハードパース バインド変数値 sval = 100 eval = 5000 を元に実行計画を作成注目
SELECT * FROM tbl0WHERE :sval < val AND val < :eval
検索対象行
5000 100
共有プール
(age-out後の) 2回目以降の実行
実行＆フェッチ ソフトパース 作成済み 実行計画 を流用 SELECT * FROM tbl0WHERE :sval < val AND val < :eval
検索対象行
10 1
混乱した現場が・・・
• 共有プールの強制flush、統計情報の再収集
、インスタンスの再起動などを実行すると・・・
• 共有カーソルのage-out相当の処理が実行さ
れる形になる
• ハードパースが再実行され、想定通りの実行
計画となる
• （問題のメカニズムが分かっていれば、上記の
動作は当たり前の話なのだが・・・） 現場的に
は、なぜかよくわからないが問題が解消したよ
うに見える
• が、以後も偶発的に同様の現象が発生・・・
対処策は？
対処策 説明 SQLをリテラル（バイン ド変数を使用しない） に修正 リテラル値（≒WHERE条件）毎に実行計 画を作成 → 異なるWHERE条件が指定された SQLに対して、1つの実行計画を使いまわ さないようになる バインドピークを無効化 (_optim_peek_user _binds=false) デフォルト値を基準に実行計画を作成す る → 実行計画がバインド変数値に依存し ないようになる 実行計画を誘導 （ヒント、アウトライン、 SQL実行計画管理） 指定した実行計画が作成される →実行計画がバインド変数値に依存しな いようになる対処策1)
SQLをリテラルに修正
• WHERE条件ごとに実行計画が作成される
– 子カーソル（≒実行計画）が共有されない – 個々のWHERE条件に最適な実行計画が作成 される SELECT * FROM tbl0WHERE 1 < val AND val < 10
SELECT * FROM tbl0
WHERE 100 < val AND val < 5000 :
検索対象行
対処策2)
バインドピークを無効化
• _optim_peek_user_binds=false • デフォルト値(*1)を基準に実行計画が作成される – 子カーソル（≒実行計画）が共有される – ある意味・・・平均的な実行計画が作成される (*1) デフォルト値の例 '=', 'LIKE' の選択率 : 1/NUM_DISTINCT そのほかの選択率 : 0.05 SELECT * FROM tbl0WHERE :sval < val AND val < :eval
10 1
SELECT * FROM tbl0
WHERE :sval < val AND val < :eval
5000 100
:
検索対象行
対処策3)
実行計画を誘導
• 指定した実行計画が作成される
– 子カーソル（≒実行計画）が共有される – どの実行計画に誘導するか？という問題が残る SELECT /*+INDEX(tbl0) */ * FROM tbl0WHERE :sval < val AND val < :eval
10 1
SELECT /*+INDEX(tbl0) */
* FROM tbl0
WHERE :sval < val AND val < :eval
5000 100
:
検索対象行
「そもそも論」で考える
• 「そもそも」SQLをバインド変数化する目的と
は？
– バインド変数化するメリット、デメリット – バインド変数化が「ある意味」盲目的に推奨され ている背景• 「そもそも」実行計画の共有/非共有が、
「するか しないかの２択」なのはいかがなものか
– もう少し賢い仕組みがあってしかるべきでは「そもそも論」で考える
• 「そもそも」SQLをバインド変数化する目的と
は？
– バインド変数化するメリット、デメリット – バインド変数化が「ある意味」盲目的に推奨され ている背景• 「そもそも」実行計画の共有/非共有が、
「するか しないかの２択」なのはいかがなものか
– もう少し賢い仕組みがあってしかるべきではバインド変数化のメリット・デメリット
○/× 案 説明 ○ ハードパース実行回数の削 減によるCPU使用率の削減 異なるWHERE条件を指 定した多くのSQLが発行さ れる場合は効果大 ○ 共有プール使用量の削減 異なるWHERE条件を指 定した多くのSQLが発行さ れる場合は効果大 × WHERE条件により最適な 実行計画が異なる場合でも 同一の実行計画を使用 どのようなWHERE条件で も最適な実行計画が同じ 場合は問題とならないバッチ処理SQLとバインド変数化
○/× 案 バッチ処理SQLの場合 ○ ハードパース実行回数の削 減によるCPU使用率の削減 異なるWHERE条件を指 定した多くのSQLが発行さ れないため、効果小 ○ 共有プール使用量の削減 異なるWHERE条件を指 定した多くのSQLが発行さ れないため、効果小 × WHERE条件により最適な 実行計画が異なる場合でも 同一の実行計画を使用 WHERE条件により最適な 実行計画が異なる場合が 多い ⇒ バインド変数化の利点は、バッチ処理SQLの特 性に適合しないバインド変数化を盲目的に推奨する風潮？
• バインド変数化の有無は、開発者がそれぞれ
のSQLごとに判断する必要がある
– 現在のOracle Databaseでは・・・残念ながら• しかし、盲目的に
「バインド変数化＝善」
と判
断している風潮が見られる
– コーディング規約でのルール化 – DBアクセスロジックを過度に共通化突発的なパフォーマンスダウンを避けるため、
盲目的にバインド変数を使用することは避け
てほしい
「そもそも論」で考える
• 「そもそも」SQLをバインド変数化する目的と
は？
– バインド変数化するメリット、デメリット – バインド変数化が「ある意味」盲目的に推奨され ている背景• 「そもそも」実行計画の共有/非共有が、
「するか しないかの２択」なのはいかがなものか
– もう少し賢い仕組みがあってしかるべきでは実行計画の共有/非共有の判断
• WHERE条件が異なるSQLと実行計画の共有/非 共有 – nのSQLについて1つの実行計画を共有するか – nのSQLについてそれぞれnの実行計画を作成する • → 実行計画を共有するかしないか • 「そもそも」もうすこしインテリジェントな仕組みがあって も良いのでは？Adaptive Cursor Sharing (11.1-)
• 作成済みの実行計画（≒子カーソル）が不適
切な場合は、自動的に新規に子カーソルを作
成する機能
: SELECT * FROM tbl0WHERE :sval < val AND val < :eval
10 1
SELECT * FROM tbl0
WHERE :sval < val AND val < :eval
5000 100
検索対象行
検索対象行
SELECT * FROM tbl0
WHERE :sval < val AND val < :eval
5000 100 検索対象行 ① ② ② ①
