共有プール領域に関する検証　その１２

＜共有プールに関する検証　その12＞　ペンネーム　ダーリン

–　ボーナストラック：続・宿題編
バインド変数は万能ではない！？–

今回は前回に引き続き”モンキーターン”先輩からの宿題である。

□■
宿題：子カーソルはどのような条件下で増えるのか？

前回は、以下の検証想定条件下では、子カーソルが増えないことがわかった。
（必ずしもすべてのパターンが網羅されているわけではないとおもいますので、
ご了承ください。）

1.バインド変数に違う値がセットされたとき？
2.複数セッションから同時に実行されたとき？
3.パラレルクエリーを実行したとき？

では、どのような条件で子カーソルが増えるのだろう。

前回、バインド変数に違う値をセットした場合は、子カーソルは増えないこと
を検証した。

これは正解だが、実は正解ではない。

単純に異なる値をセットした場合は無論子カーソルは増えない。但し、以下の
場合において、子カーソルが増えるのである。

　1.number属性のカラムに対して、number属性とvarchar2属性など、複数の種
　　類で定義されたバインド変数を用いてwhere句を記述した場合。
　2.varchar2属性のカラムに対して、最初はデータ長の短い値をバインド変数
　　にセットしていたが、そのあと同じバインド変数に長い文字列をセットし
　　た場合。

具体的には、以下のような操作をしたときである。

□■

　1.number属性のカラムに対して、number属性とvarchar2属性など、複数の種
　　類で定義されたバインド変数を用いてwhere句を記述した場合。

前回の結果：

SQL> variable a number
SQL> begin :a := 7902; end;
  2  /

PL/SQLプロシージャが正常に完了しました。

SQL> select EMPNO,ENAME from t_emp where EMPNO = :a;


SQL> select hash_value ,sql_text ,executions
  2 from v$sql where hash_value = 4129141103;

HASH_VALUE SQL_TEXT                                       EXECUTIONS
---------- ---------------------------------------------- ----------
4129141103 select EMPNO,ENAME from t_emp where EMPNO = :a          5

この状態で、別セッションで、以下のようにバインド変数を定義し、おなじ
SQL文を実行してみた。

SQL> variable a varchar2(100)
SQL> begin :a := '7902'; end;
  2  /

PL/SQLプロシージャが正常に完了しました。

SQL> select EMPNO,ENAME from t_emp where EMPNO = :a;


SQL> select hash_value ,sql_text ,executions
  2 from v$sql where hash_value = 4129141103;

HASH_VALUE SQL_TEXT                                       EXECUTIONS
---------- ---------------------------------------------- ----------
4129141103 select EMPNO,ENAME from t_emp where EMPNO = :a          5
4129141103 select EMPNO,ENAME from t_emp where EMPNO = :a          1

子カーソルが増えた。

これはなぜか？
共有プールに存在する、SQL文は通常同一のSQL文であれば共有されるが、それ
以外にも共有される条件がある。
上記の例では、SQL文自体はまったく同じだが、使用しているバインド変数の
型が異なったことが原因となり、異なる子カーソルとして存在しているようだ。

□■

　2.varchar2属性のカラムに対して、最初はデータ長の短い値をバインド変数
　　にセットしていたが、そのあと同じバインド変数に長い文字列をセットし
　　た場合。

次に、同じ型のバインド変数を使用して、かつ、同じSQL文を実行するが、バイ
ンド変数にセットする内容を大きく変えた例である。

SQL文の発行

SQL> variable c varchar2(1000)
SQL> begin :c := 'TEST' end;
  2  /

PL/SQLプロシージャが正常に完了しました。

SQL> select EMPNO,ENAME from t_emp where ENAME = :c;


SQL> select hash_value ,sql_text ,executions
  2 from v$sql where hash_value = 2793638237;

HASH_VALUE SQL_TEXT                                       EXECUTIONS
---------- ---------------------------------------------- ----------
2793638237 select EMPNO,ENAME from t_emp where ENAME = :c          3

続いて、バインド変数に長い文字列をセットしてみる。
SQL文の発行

SQL> variable c varchar2(1000)

  1  begin
  2  :c := '
  3  TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST
  4  TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST
  5  TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST
        ＜省略＞
 11  TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST
 12  ';
 13* end;
SQL> /

PL/SQLプロシージャが正常に完了しました。

SQL> select EMPNO,ENAME from t_emp where ENAME = :c;


SQL> select hash_value ,sql_text ,executions
  2 from v$sql where hash_value = 2793638237;

HASH_VALUE SQL_TEXT                                       EXECUTIONS
---------- ---------------------------------------------- ----------
2793638237 select EMPNO,ENAME from t_emp where ENAME = :c          3
2793638237 select EMPNO,ENAME from t_emp where ENAME = :c          1

こちらも先ほどと同様、子カーソルが増える状況を確認できた。

今回、子カーソルが増える状況として確認できたのは、いずれもバインド変数
に関する違いによるものだが、これ以外にも子カーソルが増える条件はあるは
ずである。

今回の、検証を通して押さえておきたい点は、バインド変数を使用していても、
子カーソルが増えることがあるという点である。これは、SQL文の共有化を進
めるという意味でバインド変数を利用したとしても、子カーソルレベルでは必
ずしも共有されないことを示している。

さて、いよいよ次回からは我が師匠、”モンキーターン”の復活である。

以上最近となりの扇風機より背が高くなってきたケルベラが怖い茅ヶ崎にて

共有プール領域に関する検証 その１２

共有プール領域に関する検証　その１２