バッチ処理にバインド変数はもうやめません？バッチ処理にバインド変数はもうやめません？

Size: px

Start display at page:

Download "バッチ処理にバインド変数はもうやめません？バッチ処理にバインド変数はもうやめません？"

まれあひきぎ
5 years ago
Views:

1 バッチ処理にバインド変数はもうやめません? ~ バッチ処理の突発遅延を題材にして考えてみる ~ 2012/4/6 株式会社コーソル渡部亮太

2 今日お伝えしたいことバッチ処理 SQL をバインド変数化するのはやめませんか? OLTP 処理 SQL はバインド変数化して OK なんだけどね

2 回 Oracle DBA & Developer Days 2010, 2011 株式会社コーソル CO-Solutions= 共に解決するの理念のもと Oracle

3 自己紹介 + 所属企業の紹介渡部亮太 ( わたべりょうた ) 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 所有者 )

4 こんな現象を題材に考えます稼動実績があるバッチ処理が突然パフォーマンスダウンするケース突然 SQL のパフォーマンスがダウンバインド変数を使用した SQL 試行錯誤していろいろ対処策を探っているうちになぜか通常のパフォーマンスに戻ったしかしその後も不定期にパフォーマンスダウンが再発原因がつかめない

5 正常稼働時範囲検索条件をバインド変数化した SQL 数は比較的少ない INDEX RANGE SCAN が適切と想定される SELECT * FROM tbl0 WHERE :sval < val AND val < :eval 1 10 :

6 パフォーマンスダウン時 TABLE FULL SCAN が実行されているしかし現場的には実行計画の変化に気づいていない不要なブロックアクセスが大量に発生 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval 1 10 :

7 問題発生のメカニズムなぜこのような現象が発生するのか? 以下の 2 つの動作が影響している 1) バインドピーク機能ハードパース時に指定されたバインド変数値を元に実行計画を作成する機能 2) 共有カーソル ( 子カーソル ) の再利用作成済みの共有カーソルが共有プールに存在する場合 2 回目以降の SQL 実行で再利用する仕組み共有カーソルには実行計画が含まれるため実行計画も再利用される

8 初回実行 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval 1 10 ハードパースバインド変数値 sval = 1 eval = 10 を元に実行計画を作成実行 & フェッチ共有プール :

9 2 回目以降の実行 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval ソフトパース実行 & フェッチ作成済み実行計画を流用共有プール :

10 共有カーソルの age-out 共有プールの領域不足統計情報の更新など様々な理由で共有カーソルは age-out される場合がある共有プール

11 (age-out 後の ) 初回実行 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval 注目ハードパースバインド変数値 sval = 100 eval = 5000 を元に実行計画を作成実行 & フェッチ共有プール :

12 (age-out 後の ) 2 回目以降の実行 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval 1 10 ソフトパース実行 & フェッチ作成済み実行計画を流用共有プール : 非効率な SQL 実行!

13 混乱した現場が共有プールの強制 flush 統計情報の再収集インスタンスの再起動などを実行すると共有カーソルの age-out 相当の処理が実行される形になるハードパースが再実行され想定通りの実行計画となる ( 問題のメカニズムが分かっていれば上記の動作は当たり前の話なのだが ) 現場的にはなぜかよくわからないが問題が解消したように見えるが以後も偶発的に同様の現象が発生

14 対処策は? 対処策 SQL をリテラル ( バインド変数を使用しない ) に修正バインドピークを無効化 (_optim_peek_user _binds=false) 実行計画を誘導 ( ヒントアウトライン SQL 実行計画管理 ) 説明リテラル値 ( WHERE 条件 ) 毎に実行計画を作成異なる WHERE 条件が指定された SQL に対して 1 つの実行計画を使いまわさないようになるデフォルト値を基準に実行計画を作成する実行計画がバインド変数値に依存しないようになる指定した実行計画が作成される実行計画がバインド変数値に依存しないようになる

15 対処策 1) SQL をリテラルに修正 WHERE 条件ごとに実行計画が作成される子カーソル ( 実行計画 ) が共有されない個々の WHERE 条件に最適な実行計画が作成される SELECT * FROM tbl0 WHERE 1 < val AND val < 10 SELECT * FROM tbl0 WHERE 100 < val AND val < 5000 :

16 対処策 2) バインドピークを無効化 _optim_peek_user_binds=false デフォルト値 (*1) を基準に実行計画が作成される子カーソル ( 実行計画 ) が共有されるある意味平均的な実行計画が作成される SELECT * FROM tbl0 WHERE :sval < val AND val < :eval 1 10 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval : (*1) デフォルト値の例 '=', 'LIKE' の選択率 : 1/NUM_DISTINCT そのほかの選択率 : 0.05

17 対処策 3) 実行計画を誘導指定した実行計画が作成される子カーソル ( 実行計画 ) が共有されるどの実行計画に誘導するか? という問題が残る SELECT /*+INDEX(tbl0) */ * FROM tbl0 WHERE :sval < val AND val < :eval 1 10 SELECT /*+INDEX(tbl0) */ * FROM tbl0 WHERE :sval < val AND val < :eval :

18 そもそも論で考えるそもそも SQL をバインド変数化する目的とは? バインド変数化するメリットデメリットバインド変数化がある意味盲目的に推奨されている背景そもそも実行計画の共有 / 非共有がするかしないかの 2 択なのはいかがなものかもう少し賢い仕組みがあってしかるべきではというわけで Adaptive Cursor Sharing (11.1-)

19 そもそも論で考えるそもそも SQL をバインド変数化する目的とは? バインド変数化するメリットデメリットバインド変数化がある意味盲目的に推奨されている背景そもそも実行計画の共有 / 非共有がするかしないかの 2 択なのはいかがなものかもう少し賢い仕組みがあってしかるべきではというわけで Adaptive Cursor Sharing (11.1-)

20 バインド変数化のメリットデメリット / 案ハードパース実行回数の削減による CPU 使用率の削減説明異なる WHERE 条件を指定した多くの SQL が発行される場合は効果大共有プール使用量の削減異なるWHERE 条件を指定した多くのSQLが発行される場合は効果大 WHERE 条件により最適な実行計画が異なる場合でも同一の実行計画を使用どのような WHERE 条件でも最適な実行計画が同じ場合は問題とならない

21 バッチ処理 SQL とバインド変数化 / 案ハードパース実行回数の削減による CPU 使用率の削減バッチ処理 SQL の場合異なる WHERE 条件を指定した多くの SQL が発行されないため効果小共有プール使用量の削減異なるWHERE 条件を指定した多くのSQLが発行されないため効果小 WHERE 条件により最適な実行計画が異なる場合でも同一の実行計画を使用 WHERE 条件により最適な実行計画が異なる場合が多いバインド変数化の利点はバッチ処理 SQL の特性に適合しない

22 バインド変数化を盲目的に推奨する風潮? バインド変数化の有無は開発者がそれぞれの SQL ごとに判断する必要がある現在の Oracle Database では残念ながらしかし盲目的にバインド変数化 = 善と判断している風潮が見られるコーディング規約でのルール化 DB アクセスロジックを過度に共通化突発的なパフォーマンスダウンを避けるため盲目的にバインド変数を使用することは避けてほしい

23 そもそも論で考えるそもそも SQL をバインド変数化する目的とは? バインド変数化するメリットデメリットバインド変数化がある意味盲目的に推奨されている背景そもそも実行計画の共有 / 非共有がするかしないかの 2 択なのはいかがなものかもう少し賢い仕組みがあってしかるべきではというわけで Adaptive Cursor Sharing (11.1-)

24 実行計画の共有 / 非共有の判断 WHERE 条件が異なる SQL と実行計画の共有 / 非共有 n の SQL について 1 つの実行計画を共有するか n の SQL についてそれぞれ n の実行計画を作成する実行計画を共有するかしないかそもそももうすこしインテリジェントな仕組みがあっても良いのでは?

25 Adaptive Cursor Sharing (11.1-) 作成済みの実行計画 ( 子カーソル ) が不適切な場合は自動的に新規に子カーソルを作成する機能 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval 1 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval 100 SELECT * FROM tbl0 WHERE :sval < val AND val < :eval :

26 Adaptive Cursor Sharing で万事 OK? 残念ながらそんなことはない Feedback-Base なので最低 1 回は痛い目にあわないとダメ ( 実行イメージ ) 作成済みの実行計画で実行 ( パフォーマンスダウン +) 予測値と実測値に乖離新規に実行計画を作成 ( 大量データを処理する ) バッチ処理の場合痛い目にあってからでは遅いしたがってバッチ処理の場合は SQL のリテラル化がやっぱりオススメ

27 ご参加いただきありがとうございましたひきつづき鼓動をお楽しみください

28 中間的な特性を持つ SQL では? バッチ処理と OLTP の中間的な特性を持つ SQL ではどういうアプローチが適切か WHERE 条件により最適な実行計画が異なる実行頻度はそれなりに高い条件の種類も多い対処策たまに発生する実行計画の作成ミスには目をつぶり ACS に頼る最適な実行計画が異なるバインド変数値を洗い出しその場合のみリテラル化 or 実行計画を誘導 ( それなりに大変なはず )

29 CURSOR_SHARING=SIMILAR CURSOR_SHARING リテラル SQL を自動的にバインド変数化する機能 CURSOR_SHARING=SIMILAR SQL の種類とオプティマイザ統計の取得状況が条件に合致する場合 WHERE 条件毎に別の子カーソル ( 実行計画 ) を作成する意図しない実行計画の共有を回避できる反面子カーソル数の肥大化を招く場合があるため適用は慎重に 11.2 以降で非推奨で使用不可

はじめにコースの概要と目的 Oracle をより効率的に使用するための SQL のチューニング方法について説明しますまた索引の有無 SQL の記述方法がパフォーマンスにどのように影響するのかを実習を通して理解します受講対象者アプリケーション開発者 / データベース管理者の方前提条件 S

はじめにコースの概要と目的 Oracle をより効率的に使用するための SQL のチューニング方法について説明しますまた索引の有無 SQL の記述方法がパフォーマンスにどのように影響するのかを実習を通して理解します受講対象者アプリケーション開発者 / データベース管理者の方前提条件 S はじめにコースの概要と目的 Oracle をより効率的に使用するための SQL のチューニング方法について説明しますまた索引の有無 SQL の記述方法がパフォーマンスにどのように影響するのかを実習を通して理解します受講対象者アプリケーション開発者 / データベース管理者の方前提条件 SQL トレーニングデータベースアーキテクチャコースを受講された方もしくは同等の知識をお持ちの

バッチ処理にバインド変数はもうやめません？ バッチ処理にバインド変数はもうやめません？

バッチ処理にバインド変数はもうやめません？バッチ処理にバインド変数はもうやめません？