V$SQL SHARED CURSOR

各Child CursorごとCursor共有が行われていない理由を提供するビューです。例えばBind Mismatchが生じた場合には、BIND_MISMATCHカラムの値がYに記録されます。

8i以上

SQL Textは同じですが、共有されていないChild Cursorの数は、V $ SQL_AREA.VERSION_COUNTカラムの値で知ることができます。この列の値が高い場合によくVersion Countが高くなります。高いVersion Countは、次のような問題と関連があります。

不要なHard Parse
Library Cacheスペースの無駄
不安定な実行計画

Version Countが高すぎる場合には、V$ SQL_SHARED_CURSORビューを利用してCursorが共有されていない理由を把握した後、問題を解決しなければなりません。

1.Optimizer Modeの変更（ALL_ROWS / FIRST_ROWS / CHOOSE
  OPTIMIZER_MODE_MISMATCHカラムの値で確認
2.Optimizer Parameterの変更（例えば、OPTIMIZER_INDEX_CACHINGパラメータ）
  OPTIMIZER_MISMATCHカラムの値で確認
3.Trace Event有効（例えば、10046 Event、DBMS_MONITOR、SQL_TRACE）
  STATS_ROW_MISMATCHカラムの値で確認
4.Translation発生（例えば、Schema AとSchema Bが同じ名前のテーブルを使用して）
  AUTH_CHECK_MISMATCH、TRANSLATION_MISMATCHカラムの値で確認
5.Bind Mismatch発生（例えば、長さが他の文字列を使用）
  BIND_MISMATCHカラムの値で確認

同じBind変数のためのさまざまな長さの値を使用すると、Bind Mismatchが発生することがあります。下の例を見て見ましょう。

-- 同じTextのSQLをBind変数の長さを変更して実行
var X varchar2(1);
exec :X := 't';
select /* bind_test */ * from bind_test where name = :x;

var X varchar2(150);
exec :X := 't';
select /* bind_test */ * from bind_test where name = :x;

var X varchar2(300);
exec :X := 't';
select /* bind_test */ * from bind_test where name = :x;

-- Version Count가 3ですべて共有されなかったことを確認することができます。
SELECT version_count, sql_text FROM v$sqlarea WHERE sql_text like 'select /* bind_test */%';
version_count   sql_text   
----------------   --------------------------------------------------------------------                                                             
3                    select /* bind_test */ * from bind_test where name = :X

オラクルは、Varchar型のBind変数の場合、その長さを次のように区間の値に切り上げています。

・32
・128
・2000
・4000

同じ区間の間に属するBind変数は、同じ長さで扱われるが、区間が異なる場合には、Bind Mismatchが発生します。V$ SQL_BIND_METADATAビューを使用すると、これを確認することができます。

select address, max_length, bind_name from v$sql_bind_metadata
     where child_address in (
        SELECT child_address FROM v$sql WHERE sql_text like 'SELECT /* bind_test */%'
     );
address                         max_length            bind_name
-----------------------      ---------------       -------------
0000000084884B10               32                       X
0000000084973280               128                      X
0000000084973E70               2000                     X

上記のような現象を回避するには、同じサイズのBind変数を宣言する必要があります。

Java言語では、PreparedStatementを使用してBind変数を使用します。この場合にも、同じ問題が発生します。つまり、下の二つのPreparedStatementは、実際には共有ができません。

PreparedStatement stmt = null;
String s1 = "a";
stmt = con.prepareStatement("SELECT /* bind_test */ * FROM bind_test WHERE name = ?");
stmt.setString(1, s1);
ResultSet rs = stmt.executeQuery();
stmt.close();

String s2 = "a";
for(int idx=0; idx<50; idx++) s2 = s2+" ";
stmt = con.prepareStatement("SELECT /* bind_test */ * FROM bind_test WHERE name = ?");
stmt.setString(1, s2);
rs = stmt.executeQuery();
stmt.close();

これらの現象は、ほとんどの場合、大きな問題にはなりません。しかし、単一のSQL文の数が多くのBind変数を使用して、各Bind変数ごとにBind Mismatchが発生した場合、その数は指数関数的に増えることになります。このような場合には、次のような方法を使用することができます。

String X = "x";
// V$SQL_BIND_METADATA ビューを使用して確認した最大値だけDummy Blankを付けます。
for(int i = 0; i < xxxx; i++) X+= " ";
...
// RTRIM Functionを使用して、Dummy Blankが認識されないようにします。
stmt = con.prepareStatement("select /* bind_test */ * from bind_test where name = RTRIM(?)");
..

上記のようにコードを変更すると、常に同じサイズの文字列が送信されるので、Bind Mismatchが発生しません。

１. V$ SQL_BIND_METADATAビュー

１. Library Cache Internals – Julian Dyke