version_count增加的原因(1)

最新推荐文章于 2022-12-07 18:05:40 发布

转载最新推荐文章于 2022-12-07 18:05:40 发布 · 216 阅读

本文探讨了在Oracle数据库中使用绑定变量插入不同长度的VARCHAR2类型数据时，如何影响子游标的生成及版本计数。通过实验展示了随着数据长度的变化，系统如何自动创建不同子游标以适应不同长度范围。

当使用绑定变量时，如果绑定变量列的为varchar2类型，且长度变化较大时，将产生过多的version_count：

SQL> create table t1(col1 varchar2(4000));

Table created.

SQL> declare
2 v_col1 varchar2(4000);
3 begin
4 v_col1 := 't';
5 for i in 1..30 loop
6 v_col1 := v_col1 ||'t';
7 insert into t1 values(v_col1);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

SQL> select sql_text,executions,child_number,address,hash_value from v$sql where sql_text like 'INSERT INTO T1%';

SQL_TEXT EXECUTIONS CHILD_NUMBER ADDRESS HASH_VALUE
-------------------------------------------------- ---------- ------------ -------- ----------
INSERT INTO T1 VALUES(:B1 ) 30 0 2CF2AE44 2351142747

SQL> declare
2 v_col1 varchar2(4000);
3 begin
4 v_col1 := 'tttttttttttttttttttttttttttttt';
5 for i in 31..4000 loop
6 v_col1 := v_col1 ||'t';
7 insert into t1 values(v_col1);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

SQL> select sql_text,executions,child_number,address,hash_value from v$sql where sql_text like 'INSERT INTO T1%';

SQL_TEXT                                           EXECUTIONS CHILD_NUMBER ADDRESS HASH_VALUE
-------------------------------------------------- ---------- ------------ -------- ----------
INSERT INTO T1 VALUES(:B1 )                                32            0 2CF2AE44 2351142747
INSERT INTO T1 VALUES(:B1 )                                96            1 2CF2AE44 2351142747
INSERT INTO T1 VALUES(:B1 )                              1872            2 2CF2AE44 2351142747
INSERT INTO T1 VALUES(:B1 )                              2000            3 2CF2AE44 2351142747

SQL> select * from v$sql_shared_cursor where address='2CF2AE44';

SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
M B M R O P M F L
- - - - - - - - -
g1j32jk6275uv 2CF2AE44 2CD32C20 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

g1j32jk6275uv 2CF2AE44 2CCDD35C 1 N N N N N N N N N N N N N N Y 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

g1j32jk6275uv 2CF2AE44 2CE0F4A8 2 N N N N N N N N N N N N N N Y 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

SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
M B M R O P M F L
- - - - - - - - -
g1j32jk6275uv 2CF2AE44 2CE0EECC 3 N N N N N N N N N N N N N N Y 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

SQL> select * from v$SQL_BIND_METADATA where address in(select child_address from v$sql where address='2CF2AE44');

ADDRESS    POSITION   DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ---------- ------------------------------
293C58A0          1          1         32          0 B1
2CC3F864          1          1        128          0 B1
2CCF335C          1          1       2000          0 B1
2CCF3500          1          1       4000          0 B1

可以看出，造成产生不同子游标的原因为BIND_MISMATCH，查看v$SQL_BIND_METADATA可以发现oracle会自动为某个长度范围内的绑定变量产生一个children cursor，
而不是为每个长度的都产生一个children cursor。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-613320/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-613320/