単純な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
