解决oracle绑定变量重复,基于ORACLE SQL优化之绑定变量（4）

绑定变量的使用过程当中，oracle建议绑定变量的个数不宜太多。目标SQL的SQL文本中的绑定变量个数不宜太多，否则可能会导致目标SQL总的执行时间大幅度的增长。增长的时间主要消耗在执行目标SQL时对每一个绑定变量都用其实际的值来替换(这个过程就是所谓的绑定变量值替换)，目标SQL的SQL文本中的绑定变量的个数越多，这个替换过程所消耗的时间就越长，该SQL总的执行时间也就越长。

如下为测试示例：

创建一张测试表t1，并建立一个过程

SQL>create table t1 as select*from dba_objects;

表已创建。

SQL>create or replace procedure P_GENERATE_MANY_BIND_V_DEMO

2(i_n_bind_v_number number,

3o_vc_return_flag out varchar2)is

4

5vc_sql varchar2(32767);

6vc_inlist varchar2(32767);

7n_temp number;

8begin

9vc_inlist:='1';

10fori in2..i_n_bind_v_number loop

11vc_inlist:=vc_inlist||','||to_char(i);

12end loop;

13

14vc_sql:='select count(*) from t1 where object_id in

('||vc_inlist||')'

15||' or object_id in ('||vc_inlist||')'

16||' or object_id in ('||vc_inlist||')'

17||' or object_id in ('||vc_inlist||')'

18||' or object_id in ('||vc_inlist||')'

19||' or object_id in ('||vc_inlist||')';

20execute immediate vc_sql into n_temp;

21

22o_vc_return_flag:=to_char(n_temp);

23exception

24when others then

25o_vc_return_flag:='E'||'_'||sqlcode||'_'||sqlerrm;

26return;

27end P_GENERATE_MANY_BIND_V_DEMO;

28/

过程已创建。

在存储过程P_GENERATE_MANY_BIND_V_DEMO中根据传进来的输入参数i_n_bind_v_number值构造字符串vc_inlist，并用6个vc_inlist构造目标SQL。

执行存储过程：

SQL>alter sessionsetcursor_sharing='EXACT';

会话已更改。

SQL>alter system flush shared_pool;

系统已更改。

SQL>setserveroutput on size100000

SQL>var vc_temp varchar2(4000)

SQL>alter sessionsetevents'10046 trace name context forever,level 12';

会话已更改。

SQL>settiming on

SQL>exec P_GENERATE_MANY_BIND_V_DEMO(1000,:vc_temp);

PL/SQL过程已成功完成。

已用时间:00:00:00.25

SQL>alter sessionsetevents'10046 trace name context off';

会话已更改。

看到执行时间是0.25秒，且从10046的trace文件中看出

关键字中e=43655，e是elapsed time的缩写，单位是微秒，1s=1000000微秒，即是0.044s

然后再强制cursor_sharing为force，再次执行如上步骤：

SQL>alter system flush shared_pool;

系统已更改。

SQL>settiming on

SQL>exec P_GENERATE_MANY_BIND_V_DEMO(998,:vc_temp);

PL/SQL过程已成功完成。

已用时间:00:00:02.31

SQL>alter system flush shared_pool;

系统已更改。

已用时间:00:00:00.33

SQL>alter sessionsetevents'10046 trace name context forever,level 12';

会话已更改。

已用时间:00:00:00.01

SQL>alter sessionsetcursor_sharing='FORCE';

会话已更改。

已用时间:00:00:00.00

SQL>exec P_GENERATE_MANY_BIND_V_DEMO(998,:vc_temp);

PL/SQL过程已成功完成。

已用时间:00:00:02.85

SQL>alter sessionsetevents'10046 trace name context off';

会话已更改。

已用时间:00:00:00.00

执行结果看使用了5988个绑定变量，时间从0.25s增长到2.85s，从10046trace文件看到

e=2171013，对应时间为2.17s，占了执行时间的一大半：

SQL>select2.17/2.85from dual;

2.17/2.85

----------

.761403509

已用时间:00:00:00.01

所以得到结论，绑定变量不宜过多，否则会造成比较严重的性能问题。

=========================================================

the end！！！