bind函数oracle,说说Oracle Bind Graduation（下）

最新推荐文章于 2021-08-31 15:56:38 发布

weixin_39778218

最新推荐文章于 2021-08-31 15:56:38 发布

阅读量220

点赞数

文章标签： bind函数oracle

上篇(http://space.itpub.net/17203031/viewspace-704144)中，我们解释了与bind peeking等价的Oracle绑定变量技术bind graduation。本篇中，我们继续深入探讨该技术，和思考在bind graduation的环境下我们应用开发应该注意些什么。

1、声明值还是实际值？

上面的实验，告诉我们在执行PL/SQL绑定变量时，会出现绑定变量元数据metadata分区的情况。那么，究竟是我们声明variable的变量引发的graduation，还是我们对变量赋值引发的graduation。我们就用上面发现的32 bytes分区作为实验对象。

SQL> declare

2n number(10);

3v varchar2(33);

4begin

5n := 14;

6v := 'Tom Kyte'; //v值显然不足32bytes；

8execute immediate 'insert /*+ bind_data_plsql */into t values (:x, :y)'

9using n, v;

10commit;

12v := lpad('Tom Kyte',33,'_');//达到33 bytes；

14execute immediate 'insert /*+ bind_data_plsql */into t values (:x, :y)'

15using n, v;

16commit;

17end;

18/

PL/SQL procedure successfully completed

此时，我们观察一下游标使用的情况。判断在相同的声明variable的情况下，游标是否发生共享。注意：本实验是在上面实验的基础上进行。

SQL> select sql_text, sql_id, address, version_count, EXECUTIONS fromv$sqlarea where sql_text like 'insert /*+ bind_data_plsql */%';

SQL_TEXTSQL_IDADDRESSVERSION_COUNT EXECUTIONS

---------------------------------------------------- ------------- -------- ------------- ----------

insert /*+ bind_data_plsql */into t values (:x, :y) a765qwfk986aj 4D74ECA824

SQL> select sql_id, child_number, address, child_address,executionsfrom v$sql where sql_id='a765qwfk986aj';

SQL_IDCHILD_NUMBER ADDRESSCHILD_ADDRESS EXECUTIONS

------------- ------------ -------- ------------- ----------

a765qwfk986aj0 4D74ECA8 4D74EA241

a765qwfk986aj1 4D74ECA8 49D19D5C3

增加的两次执行，全部落在了child_number=1的子游标上。我们再次观察该子游标对应的绑定变量元数据。

SQL> select * from v$sql_bind_metadata where address='49D19D5C';

ADDRESSPOSITIONDATATYPE MAX_LENGTHARRAY_LEN BIND_NAME

-------- ---------- ---------- ---------- ----------

49D19D5C211280 Y

49D19D5C12220 X

显然，超过了32bytes的绑定变量定义，都是落在了128上限的元数据metadata分区上。自然也就共享child_number=1的子游标。我们实验的目的也就达到了，Oracle在进行bind graduation的时候，使用的是PL/SQL绑定变量的声明类型长度。

2、上限

上面的一系列实验，让我们看到了对字符绑定变量，在执行PL/SQL代码时存在[0-32]，[33-128]至少两个分区。那么，varchar2的长度上限是4000。这个范围内，还存在什么分区呢？我们通过实验来确定。

SQL> declare

2n number(10);

3v varchar2(500); //增加到500长度；

4begin

5n := 14;

6v := 'Tom Kyte';

8execute immediate 'insert /*+ bind_data_plsql */into t values (:x, :y)'

9using n, v;

10commit;

11end;

12/

PL/SQL procedure successfully completed

当我们定义增加到500，实验游标情况。

SQL> select sql_id, child_number, address, child_address, executionsfrom v$sql where sql_id='a765qwfk986aj';

SQL_IDCHILD_NUMBER ADDRESSCHILD_ADDRESSEXECUTIONS

------------- ------------ -------- -----------------------

a765qwfk986aj0 4D74ECA8 4D74EA241

a765qwfk986aj1 4D74ECA8 49D19D5C3

a765qwfk986aj2 4D74ECA8 49D06E6C2(实验中，上面代码执行两次)

SQL> select * from v$sql_bind_metadata where address='49D06E6C';

ADDRESSPOSITIONDATATYPE MAX_LENGTHARRAY_LEN BIND_NAME

-------- ---------- ---------- ---------- ----------

49D06E6C2120000 Y

49D06E6C12220 X

在128 bytes之上，我们发现了第三个分区上限2000。也就是[128-2000]以内，是一个graduation范围。那么，在提高绑定变量声明长度，能否探查到新的分区了呢？

SQL> declare

2n number(10);

3v varchar2(2500);

4begin

5n := 14;

6v := 'Tom Kyte';

(篇幅原因，有省略……)

观察执行child cursor情况。

SQL> select sql_id, child_number, address, child_address,executionsfrom v$sql where sql_id='a765qwfk986aj';

SQL_IDCHILD_NUMBER ADDRESSCHILD_ADDRESS EXECUTIONS

------------- ------------ -------- ------------- ----------

a765qwfk986aj0 4D74ECA8 4D74EA241

a765qwfk986aj1 4D74ECA8 49D19D5C3

a765qwfk986aj2 4D74ECA8 49D06E6C3

依然共享2000上限游标。注意：虽然游标child_number=2的max length列标注的是2000，我们声明长度2500的时候，依然实现了共享。

SQL> declare

2n number(10);

3v varchar2(3000);

4begin

5n := 14;

6v := 'Tom Kyte';

实现使用3000的时候，依然共享了上限2000的child cursor。

SQL> select sql_id, child_number, address, child_address, executionsfrom v$sql where sql_id='a765qwfk986aj';

SQL_IDCHILD_NUMBER ADDRESSCHILD_ADDRESSEXECUTIONS

------------- ------------ -------- -----------------------

a765qwfk986aj0 4D74ECA8 4D74EA241

a765qwfk986aj1 4D74ECA8 49D19D5C3

a765qwfk986aj2 4D74ECA8 49D06E6C4

经过上面的实验，我们发现了bind graduation在变量长度变化时的一些特性。在128bytes之上，还存在一个有效的2000作为游标上限。突破2000之后，绑定变量的metadata数据上限始终显示2000。之后，即使我们声明变量长度超过2000，也是会共享这个2000的子游标。说明，当字符类型绑定变量长度声明超过了2000之后，bind graduation机制失去了效果。所有变量共享一个元数据cursor。

5、结论与思考

经过上面的实验，我们虽然没有证明《Troubleshooting》作者实验的SQL下bind graduation现象。但是起码证明了在Oracle 11gR2的情况下，PL/SQL使用字符绑定变量时，还是存在bind graduation现象的。笔者在思考一个问题，作为与bind peeking相同定位的bind graduation，Oracle推出它的意义何在呢？

经过和其他同事的讨论，认为可能原因有如下两个：

übind peeking缓解，提供多次peeking机会

从效果来看，Oracle bind graduation会增加子游标的数量。如果单就bind peeking而言，在Oracle 11g的ACS(Adaptive Cursor Sharing)出现之前，Oracle绑定变量使用的子游标数量是很少的。

Bind graduation出现之后，我们最直观的感觉是child cursor增多，对应的执行计划增多。原有的可能只用一个执行计划可以覆盖的绑定变量语句，可能要有多个执行计划才能覆盖。

对绑定变量语句而言，每次生成子游标，就意味着要进行一次hard parse，就意味着要进行一次peeking。生成与Peeking value对应的执行计划。PL/SQL代码中对变量声明长度的不一致，直接意味着不同的程序模块和功能模块。Oracle也许认为这样出现bind peeking问题的几率较高。于是取巧采用变量声明的方式进行区分管理。同时，划分区域又不是很多，从而限制了子游标出现的数量。多次peeking，形成多个子游标，配对更合适的执行计划。

ü绑定变量存储

对执行计划而言，Oracle是需要单独分配内存空间给执行计划进行保存的。如果其中有使用绑定变量，Oracle是会将绑定变量保存在child cursor中的。在分配varchar2类型的绑定变量大小空间时，使用bind graduation可以分配略小的适当空间。

最后，我们聊聊bind graduation的实际意义，主要是针对pl/sql程序而言的。虽然会存在bind graduation现象，但是我们说实现graduation的分区数量是有限的。也就是说，即使多次生成child cursor，带来version count过多的风险也是有限的。

如果要是很极端的情况，比如项目组希望实现绝对的共享或者说变量数目较多引起version count过多，可以使用10503事件控制bind graduation的出现，或者直接在代码中声明varchar2(2000)的绑定变量即可。