綁定變量的使用過程當中，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！！！

總結

以上是生活随笔為你收集整理的解决oracle绑定变量重复,基于ORACLE SQL优化之绑定变量（4）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。