単純なINSERT文でもバインド変数のサイズの違いで複数の子カーソルが生成される(Oracle Database)

表題の通り、やってみるやで彡(ﾟ)(ﾟ)

INSERTするバインド変数の大きさの順番／組み合わせで挙動が変わるので、
まずはバイト数が小さい順にINSERTしてみるやで。

バイト数の小さい順にINSERT

実行するSQLは下記

-- 共有プールをFLUSH
ALTER SYSTEM FLUSH SHARED_POOL;

-- テーブル作成
DROP TABLE TBL_A PURGE;
CREATE TABLE TBL_A(C1 VARCHAR2(4000));

-- 1バイトのバインド変数でINSERT
VAR B1 VARCHAR2(1);
EXEC :B1 := 'A';
-- sql_id = 'g47kmy54uf3nm'
INSERT INTO TBL_A VALUES(:B1);
COMMIT;

-- 40バイトのバインド変数でINSERT
VAR B1 VARCHAR2(40);
EXEC :B1 := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
-- sql_id = 'g47kmy54uf3nm'
INSERT INTO TBL_A VALUES(:B1);
COMMIT;

-- 200バイトのバインド変数でINSERT
VAR B1 VARCHAR2(200);
EXEC :B1 := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
-- sql_id = 'g47kmy54uf3nm'
INSERT INTO TBL_A VALUES(:B1);
COMMIT;

-- V$SQLで子カーソルを確認
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN SQL_TEXT FORMAT A60;
SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, CHILD_ADDRESS, SQL_TEXT FROM V$SQL
WHERE SQL_ID = 'g47kmy54uf3nm';

-- V$SQL_BIND_METADATAで子カーソルのバインド変数を確認
COLUMN BIND_NAME FORMAT A30;
SELECT ADDRESS, POSITION, DATATYPE, MAX_LENGTH, BIND_NAME FROM V$SQL_BIND_METADATA
WHERE ADDRESS IN (SELECT CHILD_ADDRESS FROM V$SQL WHERE SQL_ID = 'g47kmy54uf3nm');

-- V$SQL_SHARED_CURSORで子カーソルの生成理由を確認
SELECT SQL_ID, CHILD_ADDRESS, CHILD_NUMBER, HASH_MATCH_FAILED, BIND_LENGTH_UPGRADEABLE
FROM V$SQL_SHARED_CURSOR WHERE SQL_ID = 'g47kmy54uf3nm';

結果は以下の通り

：
SQL> -- V$SQLで子カーソルを確認
SQL> SET LINESIZE 300;
SQL> SET PAGESIZE 100;
SQL> COLUMN SQL_TEXT FORMAT A60;
SQL> SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, CHILD_ADDRESS, SQL_TEXT FROM V$SQL
  2  WHERE SQL_ID = 'g47kmy54uf3nm';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE CHILD_ADDRESS    SQL_TEXT
------------- ------------ --------------- ---------------- -----------------------------
g47kmy54uf3nm            0               0 000000006C45E450 INSERT INTO TBL_A VALUES(:B1) ★1つ目の子カーソル
g47kmy54uf3nm            1               0 000000006CD519E0 INSERT INTO TBL_A VALUES(:B1) ★2つ目の子カーソル
g47kmy54uf3nm            2               0 000000006ECC0660 INSERT INTO TBL_A VALUES(:B1) ★3つ目の子カーソル

SQL> -- V$SQL_BIND_METADATAで子カーソルのバインド変数を確認
SQL> COLUMN BIND_NAME FORMAT A30;
SQL> SELECT ADDRESS, POSITION, DATATYPE, MAX_LENGTH, BIND_NAME FROM V$SQL_BIND_METADATA
  2  WHERE ADDRESS IN (SELECT CHILD_ADDRESS FROM V$SQL WHERE SQL_ID = 'g47kmy54uf3nm');

ADDRESS            POSITION   DATATYPE MAX_LENGTH BIND_NAME
---------------- ---------- ---------- ---------- ---------
000000006C45E450          1          1         32 B1        ★バインド変数のMAX_LENGTH値が  32バイト
000000006CD519E0          1          1        128 B1        ★バインド変数のMAX_LENGTH値が 128バイト
000000006ECC0660          1          1       2000 B1        ★バインド変数のMAX_LENGTH値が2000バイト

SQL> -- V$SQL_SHARED_CURSORで子カーソルの生成理由を確認
SQL> SELECT SQL_ID, CHILD_ADDRESS, CHILD_NUMBER, HASH_MATCH_FAILED, BIND_LENGTH_UPGRADEABLE
  2  FROM V$SQL_SHARED_CURSOR WHERE SQL_ID = 'g47kmy54uf3nm';

SQL_ID        CHILD_ADDRESS    CHILD_NUMBER H B
------------- ---------------- ------------ - -
g47kmy54uf3nm 000000006C45E450            0 N N
g47kmy54uf3nm 000000006CD519E0            1 Y Y ★HASH_MATCH_FAILED列 と BIND_LENGTH_UPGRADEABLE列が N
g47kmy54uf3nm 000000006ECC0660            2 Y Y ★HASH_MATCH_FAILED列 と BIND_LENGTH_UPGRADEABLE列が N

バイト数の大きい順にINSERT

実行するSQLは下記

-- 共有プールをFLUSH
ALTER SYSTEM FLUSH SHARED_POOL;

-- テーブル作成
DROP TABLE TBL_A PURGE;
CREATE TABLE TBL_A(C1 VARCHAR2(4000));

-- 200バイトのバインド変数でINSERT
VAR B1 VARCHAR2(200);
EXEC :B1 := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
-- sql_id = 'g47kmy54uf3nm'
INSERT INTO TBL_A VALUES(:B1);
COMMIT;

-- 40バイトのバインド変数でINSERT
VAR B1 VARCHAR2(40);
EXEC :B1 := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
-- sql_id = 'g47kmy54uf3nm'
INSERT INTO TBL_A VALUES(:B1);
COMMIT;

-- 1バイトのバインド変数でINSERT
VAR B1 VARCHAR2(1);
EXEC :B1 := 'A';
-- sql_id = 'g47kmy54uf3nm'
INSERT INTO TBL_A VALUES(:B1);
COMMIT;

-- V$SQLで子カーソルを確認
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN SQL_TEXT FORMAT A60;
SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, CHILD_ADDRESS, SQL_TEXT FROM V$SQL
WHERE SQL_ID = 'g47kmy54uf3nm';

-- V$SQL_BIND_METADATAで子カーソルのバインド変数を確認
COLUMN BIND_NAME FORMAT A30;
SELECT ADDRESS, POSITION, DATATYPE, MAX_LENGTH, BIND_NAME FROM V$SQL_BIND_METADATA
WHERE ADDRESS IN (SELECT CHILD_ADDRESS FROM V$SQL WHERE SQL_ID = 'g47kmy54uf3nm');

-- V$SQL_SHARED_CURSORで子カーソルの生成理由を確認
SELECT SQL_ID, CHILD_ADDRESS, CHILD_NUMBER, HASH_MATCH_FAILED, BIND_LENGTH_UPGRADEABLE
FROM V$SQL_SHARED_CURSOR WHERE SQL_ID = 'g47kmy54uf3nm';

結果は以下の通りで、子カーソルは1つだけなんですやね彡(ﾟ)(ﾟ)

SQL> -- V$SQLで子カーソルを確認
SQL> SET LINESIZE 300;
SQL> SET PAGESIZE 100;
SQL> COLUMN SQL_TEXT FORMAT A60;
SQL> SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, CHILD_ADDRESS, SQL_TEXT FROM V$SQL
  2  WHERE SQL_ID = 'g47kmy54uf3nm';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE CHILD_ADDRESS    SQL_TEXT
------------- ------------ --------------- ---------------- -----------------------------
g47kmy54uf3nm            0               0 000000006EE9F7B8 INSERT INTO TBL_A VALUES(:B1) ★子カーソルは1つだけ

SQL>
SQL> -- V$SQL_BIND_METADATAで子カーソルのバインド変数を確認
SQL> COLUMN BIND_NAME FORMAT A30;
SQL> SELECT ADDRESS, POSITION, DATATYPE, MAX_LENGTH, BIND_NAME FROM V$SQL_BIND_METADATA
  2  WHERE ADDRESS IN (SELECT CHILD_ADDRESS FROM V$SQL WHERE SQL_ID = 'g47kmy54uf3nm');

ADDRESS            POSITION   DATATYPE MAX_LENGTH BIND_NAME
---------------- ---------- ---------- ---------- ---------
000000006EE9F7B8          1          1       2000 B1       ★バインド変数のMAX_LENGTH値が2000バイト

SQL>
SQL> -- V$SQL_SHARED_CURSORで子カーソルの生成理由を確認
SQL> SELECT * FROM V$SQL_SHARED_CURSOR WHERE SQL_ID = 'g47kmy54uf3nm';

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON                                                                             CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
g47kmy54uf3nm 0000000070E32AF8 000000006EE9F7B8            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N                                                                                         0

SQL> SELECT SQL_ID, CHILD_ADDRESS, CHILD_NUMBER, HASH_MATCH_FAILED, BIND_LENGTH_UPGRADEABLE
  2  FROM V$SQL_SHARED_CURSOR WHERE SQL_ID = 'g47kmy54uf3nm';

SQL_ID        CHILD_ADDRESS    CHILD_NUMBER H B
------------- ---------------- ------------ - -
g47kmy54uf3nm 000000006EE9F7B8            0 N N

複数バインド変数の組み合わせで小さい順にINSERT

複数バインド変数の場合は、それぞれのバインド変数の組み合わせでも
子カーソルが生成されます。以下のSQLを実行してみます。

-- 共有プールをFLUSH
ALTER SYSTEM FLUSH SHARED_POOL;

-- テーブル作成
DROP TABLE TBL_B PURGE;
CREATE TABLE TBL_B(
    C1 VARCHAR2(4000)
  , C2 VARCHAR2(4000)
);

-- 1バイトのバインド変数*2でINSERT
VAR B1 VARCHAR2(1);
VAR B2 VARCHAR2(1);
EXEC :B1 := 'A';
EXEC :B2 := 'B';
-- sql_id = '5f5u0gky3kjpc'
INSERT INTO TBL_B VALUES(:B1, :B2);
COMMIT;

-- 1バイト＆40バイトのバインド変数でINSERT
VAR B1 VARCHAR2(1);
VAR B2 VARCHAR2(40);
EXEC :B1 := 'A';
EXEC :B2 := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
-- sql_id = '5f5u0gky3kjpc'
INSERT INTO TBL_B VALUES(:B1, :B2);
COMMIT;

-- 40バイト＆1バイトのバインド変数でINSERT
VAR B1 VARCHAR2(40);
VAR B2 VARCHAR2(1);
EXEC :B1 := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
EXEC :B2 := 'A';
-- sql_id = '5f5u0gky3kjpc'
INSERT INTO TBL_B VALUES(:B1, :B2);
COMMIT;

-- 40バイトのバインド変数*2でINSERT
VAR B1 VARCHAR2(40);
VAR B2 VARCHAR2(40);
EXEC :B1 := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
EXEC :B2 := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
-- sql_id = 'g47kmy54uf3nm'
INSERT INTO TBL_B VALUES(:B1, :B2);
COMMIT;

-- V$SQLで子カーソルを確認
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN SQL_TEXT FORMAT A60;
SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, CHILD_ADDRESS, SQL_TEXT FROM V$SQL
WHERE SQL_ID = '5f5u0gky3kjpc';

-- V$SQL_BIND_METADATAで子カーソルのバインド変数を確認
COLUMN BIND_NAME FORMAT A30;
SELECT ADDRESS, POSITION, DATATYPE, MAX_LENGTH, BIND_NAME FROM V$SQL_BIND_METADATA
WHERE ADDRESS IN (SELECT CHILD_ADDRESS FROM V$SQL WHERE SQL_ID = '5f5u0gky3kjpc')
ORDER BY ADDRESS, POSITION;

-- V$SQL_SHARED_CURSORで子カーソルの生成理由を確認
SELECT SQL_ID, CHILD_ADDRESS, CHILD_NUMBER, HASH_MATCH_FAILED, BIND_LENGTH_UPGRADEABLE
FROM V$SQL_SHARED_CURSOR WHERE SQL_ID = '5f5u0gky3kjpc';

結果は以下の通りで、複数の子カーソルが生成されるんやで彡(ﾟ)(ﾟ)

SQL> -- V$SQLで子カーソルを確認
SQL> SET LINESIZE 300;
SQL> SET PAGESIZE 100;
SQL> COLUMN SQL_TEXT FORMAT A60;
SQL> SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, CHILD_ADDRESS, SQL_TEXT FROM V$SQL
  2  WHERE SQL_ID = '5f5u0gky3kjpc';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE CHILD_ADDRESS    SQL_TEXT
------------- ------------ --------------- ---------------- ----------------------------------
5f5u0gky3kjpc            0               0 000000006EB29608 INSERT INTO TBL_B VALUES(:B1, :B2) ★1つ目の子カーソル
5f5u0gky3kjpc            1               0 000000006C94E5C0 INSERT INTO TBL_B VALUES(:B1, :B2) ★2つ目の子カーソル
5f5u0gky3kjpc            2               0 000000006E6F3638 INSERT INTO TBL_B VALUES(:B1, :B2) ★3つ目の子カーソル

SQL>
SQL> -- V$SQL_BIND_METADATAで子カーソルのバインド変数を確認
SQL> COLUMN BIND_NAME FORMAT A30;
SQL> SELECT ADDRESS, POSITION, DATATYPE, MAX_LENGTH, BIND_NAME FROM V$SQL_BIND_METADATA
  2  WHERE ADDRESS IN (SELECT CHILD_ADDRESS FROM V$SQL WHERE SQL_ID = '5f5u0gky3kjpc')
  3  ORDER BY ADDRESS, POSITION;

ADDRESS            POSITION   DATATYPE MAX_LENGTH BIND_NAME
---------------- ---------- ---------- ---------- ---------
000000006C94E5C0          1          1         32 B1 ★2つ目の子カーソルのバインド変数定義
000000006C94E5C0          2          1        128 B2 ★2つ目の子カーソルのバインド変数定義
000000006E6F3638          1          1        128 B1 ★3つ目の子カーソルのバインド変数定義
000000006E6F3638          2          1        128 B2 ★3つ目の子カーソルのバインド変数定義
000000006EB29608          1          1         32 B1 ★1つ目の子カーソルのバインド変数定義
000000006EB29608          2          1         32 B2 ★1つ目の子カーソルのバインド変数定義

6 rows selected.

SQL>
SQL> -- V$SQL_SHARED_CURSORで子カーソルの生成理由を確認
SQL> SELECT SQL_ID, CHILD_ADDRESS, CHILD_NUMBER, HASH_MATCH_FAILED, BIND_LENGTH_UPGRADEABLE
  2  FROM V$SQL_SHARED_CURSOR WHERE SQL_ID = '5f5u0gky3kjpc';

SQL_ID        CHILD_ADDRESS    CHILD_NUMBER H B
------------- ---------------- ------------ - -
5f5u0gky3kjpc 000000006EB29608            0 N N
5f5u0gky3kjpc 000000006C94E5C0            1 Y Y
5f5u0gky3kjpc 000000006E6F3638            2 Y Y

という訳で単純なINSERT文でも複数の子カーソルが生成されることを確認できました。

参考資料(マニュアル)

マニュアルも見とくんやで！彡(^)(^)

• カーソルの不一致およびV$SQL_SHARED_CURSOR
  http://docs.oracle.com/cd/E82638_01/TGSQL/improving-rwp-cursor-sharing.htm#GUID-5AC5DAC7-D5D0-4FF6-85E6-9A4C3B28C890

• V$SQL_SHARED_CURSOR
  http://docs.oracle.com/cd/E82638_01/REFRN/V-SQL_SHARED_CURSOR.htm#GUID-4993A6DE-5658-4745-B43E-F5AD9DB8DCCC