共有プール領域に関する検証 その１１
＜共有プールに関する検証 その11＞ ペンネーム ダーリン
– ボーナストラック：宿題編
突然ですが、子カーソルについて –
今回は、私の師匠である”モンキーターン”先輩から、ありがたーい宿題が言い
渡されたので、これについて検証する。ぜひお付き合いいただきたい。
□■
宿題：子カーソルはどのような条件下で増えるのか？
カーソルについては、＜共有プールに関する検証 その4＞で述べたが、ひと
つのSQLに対して、1個以上の子カーソルが存在する。多くの場合、子カーソル
は、1つだがV$SQLをhash_value単位で集計すれば、複数の子カーソルが存在し
ているケースがあることが分かるはずだ。
1 select hash_value ,max(substrb(sql_text,1,30)) sql_text ,count(*) cnt
2 from v$sql
3 having count(*) > 1
4* group by hash_value
SQL> /
HASH_VALUE SQL_TEXT CNT
---------- ------------------------------ ----------
242587281 commit 2
787810128 select /*+ rule */ bucket_cnt, 2
931956286 select grantee#,privilege#,nvl 2
1453445442 select col#, grantee#, privile 2
1618602529 begin :c := 'TEST'; end; 2
2077477810 select value$ from sys.props$ 3
＜以下、省略＞
さて、皆さんはどのような場合に子カーソルが増えるとおもいますか？
1.バインド変数に違う値がセットされたとき？
2.複数セッションから同時に実行されたとき？
3.パラレルクエリーを実行したとき？
4.その他
今回は上記のケースを想定し、それぞれについて子カーソルが増えるかどう
かを検証してみた。
□■
1.バインド変数に違う値がセットされたとき？
まずは、バインド変数に違う値がセットされたとき場合どうなるかを検証する。
以下のSELECT文を実行してから、V$SQLの様子を確認する。
SQL> variable a number SQL> begin :a := 7900; end; 2 / PL/SQLプロシージャが正常に完了しました。 SQL> select EMPNO,ENAME from t_emp where EMPNO = :a;
続いて、V$SQLの内容を確認する。
（今回は以下のSQLを実行する際に、あらかじめhash_valueをV$SQLAREAから
取得しておいた。）
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 1
では、バインド変数の値を変更してみよう。
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 2
つまり、バインド変数に単純に違う値がセットされただけでは子カーソルは作
成されない。実行回数(EXECUTIONS)が増えるのみである。
あたりまえである。「子カーソルが増える」≒「翻訳(PARSE_CALLS)が発生して
いる」はずなので、バインド変数に異なる値をセットするたびに翻訳していて
は、バインド変数を使用する意味が半減する。
□■
2.複数セッションから同時に実行されたとき？
では、同時に実行された場合はどうなるか？
同様のSQLを2セッションから同時に実行してみる。
バインド変数の値はともに同じ値とする。
こちらも結論から言うと、実行回数のみが上昇し、子カーソルの数に変化は見
られなかった。
□■
3.パラレルクエリーを実行したとき？
これに関しては、以下のようなSQLを実行して検証してみた。
SQL> select /*+ parallel(t_emp,2) */ EMPNO,ENAME from t_emp;
上記の場合は、このSQLを実行するために通常1セッションのところ多重度2を
指定しているため、関連するセッション数が合計3セッションとなる。
（以下、※2が該当する。）
SQL> select sid,serial#,sql_hash_value,prev_hash_value,audsid,username
2 from v$session order by audsid,sid,serial#,username;
SID SERIAL# SQL_HASH_VALUE PREV_HASH_VALUE AUDSID USERNAME
---------- ---------- -------------- --------------- ---------- --------
1 1 0 0 0
2 1 0 0 0
3 1 0 0 0
4 1 1673306626 0 0
5 1 1714733582 0 0
6 1 3625995331 0 0
9 28 0 532003299 0 SYS
11 8 1494310188 1494310188 0 SYS
13 15 0 3174958923 0 SYS
（※2） 7 7 496432584 496432584 84024 SCOTT
（※2） 12 102 3283032523 3283032523 84024 SCOTT
（※2） 14 15 3283032523 3283032523 84024 SCOTT
＜以下、省略＞
これら（※2）のhash_valueに関しても、V$SQLを確認してみると、
SQL> select hash_value ,sql_text ,executions
2 from v$sql where hash_value = 496432584;
HASH_VALUE SQL_TEXT EXECUTIONS
---------- ----------------------------------------------- ----------
496432584 select /*+ parallel(t_emp,2) */ EMPNO,ENAME fro 2
m t_emp
SQL> select hash_value ,sql_text ,executions
2 from v$sql where hash_value = 3283032523;
HASH_VALUE SQL_TEXT EXECUTIONS
---------- ----------------------------------------------- ----------
3283032523 SELECT /*+ Q2000 NO_EXPAND ROWID(A1) */ A1."EMP 8
NO",A1."ENAME" FROM "SCOTT"."T_EMP" PX_GRANULE(
0, BLOCK_RANGE, DYNAMIC) A1
ここでも残念ながら、子カーソルはそれぞれのhash_valueについて1つずつで
あった。
つまり、今回検証した状況においては子カーソルは増えなかった。
ところが今回検証した条件の少し条件をかえると、実は子カーソルが増えるの
である。
次回は、子カーソルを増やしてみよう。
以上 Happy Birth Day To You.. 茅ヶ崎にて