SQL計画安定性のための CURSOR_SHARING = FORCEおよびFORCE_MATCHING_SIGNATUREの限界


http://blog.tanelpoder.com/2012/08/02/the-limitations-of-cursor_sharing-force-and-force_matching_signature-for-sql-plan-stability/
の翻訳です。基本的に意訳です。意味をとりやすくするために追加した箇所については、《…》で囲っています。

本文書に関する指摘については、原文著者のTanel Poderさんではなく、渡部まで御連絡お願いいたします。


日本語訳:渡部 亮太 (WR at Csus4 dot net)

履歴


  • 2013-01-21: 初稿up


— 2012年8月2日

Oracleが全てのリテラルをシステム生成のバインド変数のプレースホルダに置き換えるため、cursor_sharingパラメータをFORCEに設定できること、その後、Oracleがライブラリ?キャッシュ内の既存のカーソルを検索するためのSQLハッシュ値を計算することは、良く知られています。この機能により、ハード解析《の回数》と、共有プールのゴミの量が減るでしょう。

また、(ライブラリ?キャッシュを検索するために用いられるSQLハッシュ値計算の前に、バインド変数のプレースホルダでのリテラル値を置き換えるのと)同じメカニズムが、リテラル値が異なる複数のSQL文に対してSQLプロファイルを適用するために使用できます。単に、DBMS_SQLTUNE.ACCEPT_PROFILE(…、FORCE_MATCH => TRUE)を使用して、SQLプロファイルを受け入れるだけです。

しかし、この "force matching"には、アプリケーションが動的に生成したSQLを使用するデータベースでプラン?スタビリティを実現するためにほとんど役に立たないかもしれない、1つの制限があります。

一例を示しましょう。- SQL*Plus環境を構成して、CURSOR_SHARING= FORCEを設定します。("新しい" SQLテキストにハッシュ値計算が使用されていることを示そうとしているだけなので、DBMS_SQLTUNEのFORCE_MATCHは不要です) :

SQL> COL force_matching_signature FOR 99999999999999999999999999
SQL> COL sql_text FOR A100
SQL>
SQL> ALTER SESSION SET cursor_sharing = FORCE;
Session altered.
SQL>
SQL> SELECT * FROM dual WHERE rownum IN (1,2,3);
D

X

そして、私は実際のSQLテキスト(と関連するハッシュ値)がどのように見えるかを確認するために、V$SQLを問い合わせてみます:

SQL> SELECT sql_id,hash_value,force_matching_signature,sql_text FROM v$sql WHERE sql_id = (SELECT prev_sql_id FROM v$session WHERE sid = USERENV(‘;SID’;));
SQL_ID HASH_VALUE FORCE_MATCHING_SIGNATURE SQL_TEXT
————- ———- ————————— —————————————————————————————————-
fqcq5k1wd4d4h 2027041936 1308158718700150644 SELECT * FROM dual WHERE rownum IN (:”SYS_B_0″,:”SYS_B_1″,:”SYS_B_2″ )

右にスクロールして、SQLテキストが、リテラル値1,2,3が SYS_B_xバインド変数に置き換えられていることを見てください。この置き換えが行われた、SQLハッシュ値(およびSQL_IDとforce_matching_signature)が計算されます。

では、リテラル値が異なるだけの似たSQLを実行してみます:

SQL> SELECT * FROM dual WHERE rownum IN (999,888,777);
no rows selected
SQL> SELECT sql_id,hash_value,force_matching_signature,sql_text FROM v$sql WHERE sql_id = (SELECT prev_sql_id FROM v$session WHERE sid = USERENV(‘;SID’;));
SQL_ID HASH_VALUE FORCE_MATCHING_SIGNATURE SQL_TEXT
————- ———- ————————— —————————————————————————————————-
fqcq5k1wd4d4h 2027041936 1308158718700150644 SELECT * FROM dual WHERE rownum IN (:”SYS_B_0″,:”SYS_B_1″,:”SYS_B_2″ )

異なるリテラル値(999、888、777)を参照してください、それらは同数のバインド変数と(同じ順番で)に置き換えられたので、結果のSQLテキストはまったく同じです – 従って、SQLテキストのハッシュ値は先ほどの例と全く同じです。

これは、これらの機能が動作する仕組みです – リテラル値は、ライブラリ?キャッシュ?ルックアップのためのハッシュ値を計算する前に、SQLテキスト文字列に置き換えられます。

しかし、今度は3つではなく4つのリテラル値を使用して、もう1つのクエリを実行してみましょう。

SQL> SELECT * FROM dual WHERE rownum IN (999,888,777,666);
no rows selected
SQL> SELECT sql_id,hash_value,force_matching_signature,sql_text FROM v$sql WHERE sql_id = (SELECT prev_sql_id FROM v$session WHERE sid = USERENV(‘;SID’;));
SQL_ID HASH_VALUE FORCE_MATCHING_SIGNATURE SQL_TEXT
————- ———- ————————— —————————————————————————————————-
8btxq8q6avt6b 2360206539 15602626316910109322 SELECT * FROM dual WHERE rownum IN (:”SYS_B_0″,:”SYS_B_1″,:”SYS_B_2&quot ;,:”SYS_B_3″)

4つのリテラル(999, 888, 777, 666)を使用したので、SQLテキストで、(前述のクエリで3つだったのとは対照的に)それらは4つのバインド変数に置き換えられ、そのために、SQLテキストも異なってしまいました!したがって、ハッシュ値も異なることになり、SQLテキストのハッシュ値に基づくカーソルのmatching/lookupでは、もはや以前の実行計画を見つけることができません。同様な理由で、force_matching_signatureも以前とは値が異なることに注意してください。だから、このカーソルのために新しいハード解析とライブラリ?キャッシュの負荷が発生することになります – FORCE_MATCH=TRUEにより最初の2クエリで使用されたSQLプロファイルは
この新しいクエリには適用できません。 – それは要するに、新しいforce_matching_signatureに一致するプロファイルが見つからないからです。

単にINLIST に4つのリテラル値をもつクエリに対するプロファイルを作成すればよいという人もあるかもしれない。もちろんそれは正しい、しかし、3つと4つの INLIST値を持つクエリに対するプロファイルだけを持っているかもしれない。多くの場合、データ?ウェアハウスやレポーティング環境、また、いくつかのOLTPシステムでは、開発者は動的に生成されたINLISTをよく使用しています。今まで、inlistsに数万個のリテラル値があるクエリを見たことがある
ORA-01795: maximum number of expressions in a list is 1000エラーを回避するために、OR concatenationを使用している)My Oracle Supportには、60 000バインド変数を持つ問い合わせが失敗するというバグも確認されています! :)

このため、複数のテーブルをフィルタリングするために複数のIN-リストを使用する動的生成クエリがあり、そして、それぞれのIN-リスト内のアイテムの数が1から数万の間で変動する場合は、とりうる組み合わせの数が非常に大きくなる可能性があります – そのような各INLISTアイテム数のような組み合わせは 個別の force_matching_signature 《の値》をもつことになるでしょう。よって、たとえ、あなたがforce matchingプロファイルを使用して、実行計画の修正が完了したと考えたとしても、いつか将来、別の組み合わせ《をもつ問い合わせ》にでくわし、再びその "新しい"のクエリ《の実行計画を》を修正しなければならなくなるでしょう。

では、このような状況では何ができるのでしょうか?(CBOが新しい優れた問い合わせ計画を作成するために、自分のスキーマ、コード、統計および構成が適切であることを確認すること以外で) そうですね、このような動的に生成されるINLIST文字列を使用しないことでしょう!

1万個のリテラル値(またはバインド)を含む動的INLIST文字列を生成する代わりに、アプリケーション側でこれらの値の配列を作成し、単一のバインド変数としてクエリにその配列全体をバインドすることができます。どれだけの値をINリストに渡す必要があるかに関係なく、単一のバインド変数に格納される用になります。そして、force_matching_signatureがSQLハッシュ値を同じ値のままにすることができます。PL/SQLも、OCIベースのツールも、OracleのJDBC(そしておそらくODP.NETも)もその方法をサポートしています。だからこの方法を使用しない理由はありません :)

私は自分のデモコードを見つけられなかったため、私はJavaのquick exampleをGoogle検索で見つけました – http://blogs.itemis.de/kloss/2009/03/05/arrays-preparedstatements-jdbc-and-oracle/ 。あなたが自分のcode exampleを持っているなら、ぜひコメントにリンクをポストしてほしい。《訳注:どの記事にコメントしてほしいか不明》。

他のやり方もありますが、あまりきれいな選択肢ではありません。単一のバインド変数(VARCHAR2などあるいはCLOBのいずれか)のようなカンマ区切りの文字列を渡し、これを個々の値に分割してinlistに入れる文字列トークナイザ機能を使う。または、GTT《おそらくGlobal Temprary Tableのこと》にinlistアイテムをinsertしておき、メイン?クエリ?ブロックと結合する(INLISTに何百万の値があり、バインドおよびPL/SQLの配列を格納するためにプライベートメモリをあまり使用したくない場合、これは役に立つかもしれない)。

でも、1つの課題は存在すると思われます。 このような配列ベースのINリストに対しては、CBOによるカーディナリティの推定が正しくない可能性があるため、カーディナリティのヒント、またはSQLプロファイル、他の様々な手段 といった何らかの調整が必要となる場合があります :)