[20180930]in list與繫結變數個數.txt
[20180930]in list與繫結變數個數.txt
--//跟別人的討論,提到如果繫結變數個數太多,會導致執行時替換繫結變數時間太長,導致sql語句執行緩慢.
--//在dbsnake 《基於Oracle的SQL最佳化》提到,我給測試看看:
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t as select * from all_objects;
Table created.
SCOTT@test01p> create unique index i_t_object_id on t(object_id);
Index created.
--//分析表略.
--//正好前幾天做了http://blog.itpub.net/267265/viewspace-2214966/=>[20180926]神奇的規避ORA-01795方法.txt
--//使用這個例子來演示看看.
2.建立測試指令碼:
$ cat a.txt
set pagesize 0
set head off
set feedback off
set verify off
set timing off
spool b.txt
select 'set termout off' from dual;
select 'variable b'||to_char(level)||' number;' from dual connect by level<=&1;
select 'begin' from dual;
select ':b'||to_char(level)||' :='|| to_char(level)||';' from dual connect by level<=&1;
select 'end;' from dual;
select '/' from dual ;
select 'set termout on' txt from dual;
select 'set timing on' txt from dual;
select 'select count(data_object_id) from t where (1,object_id) in (' txt from dual ;
select '(1,:b'||to_char(level)||'),' txt from dual connect by level<=&&1 -1 ;
select '(1,:b'||to_char(&&1)||'));' txt from dual ;
select 'set timing off' txt from dual;
spool off
set pagesize 9999
set head on
set feedback on
3.測試:
@ a.txt 1000
Elapsed: 00:00:00.08
Elapsed: 00:00:00.04
Elapsed: 00:00:00.04
--//第一次要經過1次硬解析,時間多一點點正常的.做一個表格,取第2次以後的執行時間:
繫結變數數量 執行時間
--------------------------
1000 00:00:00.04
2000 00:00:00.14
3000 00:00:00.36
4000 00:00:00.70
5000 00:00:01.17
6000 00:00:01.92
7000 00:00:02.76
10000 00:00:08.12 (第1次執行00:00:11.56)
--------------------------
--//實際上3000個變數,甚至4000個以內並沒有這麼嚴重.
--//補充7000的執行計劃:
Plan hash value: 1665370044
------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 305 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 7000 | 49000 | 305 (1)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_T_OBJECT_ID | 7000 | | 182 (2)| 00:00:01 |
------------------------------------------------------------------------------------------------
--//開啟statistics_level = all
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> @ b.txt
COUNT(DATA_OBJECT_ID)
---------------------
1619
1 row selected.
Elapsed: 00:00:04.87
SCOTT@test01p> @ b.txt
COUNT(DATA_OBJECT_ID)
---------------------
1619
1 row selected.
Elapsed: 00:00:03.65
SCOTT@test01p> @ b.txt
COUNT(DATA_OBJECT_ID)
---------------------
1619
1 row selected.
Elapsed: 00:00:02.74
SCOTT@test01p> @ dpc '' '-peeked_binds -note -alias'
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3q9p1rxcghc8t, child number 1
-------------------------------------
select count(data_object_id) from t where (1,object_id) in ( (1,:b1),
(1,:b2), (1,:b3), (1,:b4), (1,:b5), (1,:b6), (1,:b7), (1,:b8), (1,:b9),
(1,:b10), (1,:b11), (1,:b12), (1,:b13), (1,:b14), (1,:b15), (1,:b16),
(1,:b17), (1,:b18), (1,:b19), (1,:b20), (1,:b21), (1,:b22), (1,:b23),
(1,:b24), (1,:b25), (1,:b26), (1,:b27), (1,:b28), (1,:b29), (1,:b30),
(1,:b31), (1,:b32), (1,:b33), (1,:b34), (1,:b35), (1,:b36), (1,:b37),
(1,:b38), (1,:b39), (1,:b40), (1,:b41), (1,:b42), (1,:b43), (1,:b44),
(1,:b45), (1,:b46), (1,:b47), (1,:b48), (1,:b49), (1,:b50), (1,:b51),
(1,:b52), (1,:b53), (1,:b54), (1,:b55), (1,:b56), (1,:b57), (1,:b58),
(1,:b59), (1,:b60), (1,:b61), (1,:b62), (1,:b63), (1,:b64), (1,:b65),
(1,:b66), (1,:b67), (1,:b68), (1,:b69), (1,:b70), (1,:b71), (1,:b72),
(1,:b73), (1,:b74), (1,:b75), (1,:b76), (1,:b77), (1,:b78), (1,:b79),
(1,:b80), (1,:b81), (1,:b82), (1,:b83), (1,:b84), (1,:b85), (1,:b86),
(1,:b87), (1,:b88), (1,:b89), (1,:b90), (1,:b91), (1,:b92), (1,:b93),
(1,:b94), (1,:b95)
Plan hash value: 1665370044
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 305 (100)| | 1 |00:00:00.03 | 210 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.03 | 210 |
| 2 | INLIST ITERATOR | | 1 | | | | | 6745 |00:00:00.03 | 210 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 7000 | 7000 | 49000 | 305 (1)| 00:00:01 | 6745 |00:00:00.02 | 210 |
|* 4 | INDEX UNIQUE SCAN | I_T_OBJECT_ID | 7000 | 7000 | | 182 (2)| 00:00:01 | 6745 |00:00:00.01 | 105 |
-----------------------------------------------------------------------------------------------------------------------------------------
...
--//開啟statistics_level = all,實際上導致執行時間延長的. A-Time=00:00:00.03.
--//說明繫結變數替換需要時間還是蠻多的,但是要在3000個以內消耗並不是很大.
4.我也測試dbsnake書上的指令碼,並沒有他測試的問題這麼嚴重:
SCOTT@test01p> create table t1 as select * from all_objects;
Table created.
--//注:沒有建立索引
--//建立過程.
CREATE OR REPLACE PROCEDURE p_gennrate_many_bind_v_demo
(
i_n_bind_v_number NUMBER
,o_vc_return_flag OUT VARCHAR2
)
IS
vc_sql VARCHAR2 (32767);
vc_inlist VARCHAR2 (32767);
n_temp NUMBER;
BEGIN
vc_inlist := '1';
FOR i IN 2 .. i_n_bind_v_number
LOOP
vc_inlist := vc_inlist || ',' || TO_CHAR (i);
END LOOP;
vc_sql :=
'select count(*) from t1 where object_id in (' || vc_inlist || ')'
|| ' or object_id in ( ' || vc_inlist || ')'
|| ' or object_id in ( ' || vc_inlist || ')'
|| ' or object_id in ( ' || vc_inlist || ')'
|| ' or object_id in ( ' || vc_inlist || ')'
|| ' or object_id in ( ' || vc_inlist || ')';
EXECUTE IMMEDIATE vc_sql INTO n_temp;
o_vc_return_flag := TO_CHAR (n_temp);
EXCEPTION
WHEN OTHERS
THEN
o_vc_return_flag := 'E' || '-' || SQLCODE || '_' || SQLERRM;
RETURN;
END p_gennrate_many_bind_v_demo;
/
SCOTT@test01p> alter system set cursor_sharing=force scope=memory;
System altered.
SCOTT@test01p> alter system flush shared_pool;
System altered.
--//退出再進入:
SCOTT@test01p> var a varchar2(32767)
SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.99
--//第1次執行硬解析執行時間長一點是正常的.
SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.53
SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.50
SCOTT@test01p> print :a
A
---------
891
--//很奇怪使用他的例子問題沒有這麼嚴重.我猜測可能他使用儲存過程的原因.
SCOTT@test01p> create unique index i_t1_object_id on t1(object_id);
Index created.
SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.57
SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.31
SCOTT@test01p> exec p_gennrate_many_bind_v_demo(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.31
SCOTT@test01p> @ dpc 3z307yzbxg19z '-peeked_binds -note -alias'
...
Plan hash value: 351707332
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 181 (100)| | 1 |00:00:00.01 | 16 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.01 | 16 |
| 2 | INLIST ITERATOR | | 1 | | | | | 891 |00:00:00.01 | 16 |
|* 3 | INDEX UNIQUE SCAN| I_T1_OBJECT_ID | 1000 | 6000 | 30000 | 181 (2)| 00:00:01 | 891 |00:00:00.01 | 16 |
--------------------------------------------------------------------------------------------------------------------------------
--//如果建立索引.時間還更小一些.另外Starts=1000次.作者定義變數重複定義.
5.修改作者的指令碼:
CREATE OR REPLACE PROCEDURE p_gennrate_many_bind_v_demo1
(
i_n_bind_v_number NUMBER
,o_vc_return_flag OUT VARCHAR2
)
IS
vc_sql VARCHAR2 (32767);
vc_inlist0 VARCHAR2 (32767);
vc_inlist1 VARCHAR2 (32767);
vc_inlist2 VARCHAR2 (32767);
vc_inlist3 VARCHAR2 (32767);
vc_inlist4 VARCHAR2 (32767);
vc_inlist5 VARCHAR2 (32767);
n_temp NUMBER;
BEGIN
vc_inlist0 := '1';
FOR i IN 2 .. i_n_bind_v_number
LOOP
vc_inlist0 := vc_inlist0 || ',' || TO_CHAR (i);
END LOOP;
vc_inlist1 := '1001';
FOR i IN 2 .. i_n_bind_v_number
LOOP
vc_inlist1 := vc_inlist1 || ',' || TO_CHAR (i+1000);
END LOOP;
vc_inlist2 := '2001';
FOR i IN 2 .. i_n_bind_v_number
LOOP
vc_inlist2 := vc_inlist2 || ',' || TO_CHAR (i+2000);
END LOOP;
vc_inlist3 := '3001';
FOR i IN 2 .. i_n_bind_v_number
LOOP
vc_inlist3 := vc_inlist3 || ',' || TO_CHAR (i+3000);
END LOOP;
vc_inlist4 := '4001';
FOR i IN 2 .. i_n_bind_v_number
LOOP
vc_inlist4 := vc_inlist4 || ',' || TO_CHAR (i+4000);
END LOOP;
vc_inlist5 := '5001';
FOR i IN 2 .. i_n_bind_v_number
LOOP
vc_inlist5 := vc_inlist5 || ',' || TO_CHAR (i+5000);
END LOOP;
vc_sql :=
'select count(*) from t1 where object_id in (' || vc_inlist0 || ')'
|| ' or object_id in ( ' || vc_inlist1 || ')'
|| ' or object_id in ( ' || vc_inlist2 || ')'
|| ' or object_id in ( ' || vc_inlist3 || ')'
|| ' or object_id in ( ' || vc_inlist4 || ')'
|| ' or object_id in ( ' || vc_inlist5 || ')';
EXECUTE IMMEDIATE vc_sql INTO n_temp;
o_vc_return_flag := TO_CHAR (n_temp);
EXCEPTION
WHEN OTHERS
THEN
o_vc_return_flag := 'E' || '-' || SQLCODE || '_' || SQLERRM;
RETURN;
END p_gennrate_many_bind_v_demo1;
/
--//刪除索引測試.
SCOTT@test01p> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------- ----------
cursor_sharing string FORCE
SCOTT@test01p> exec p_gennrate_many_bind_v_demo1(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.09
SCOTT@test01p> exec p_gennrate_many_bind_v_demo1(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.49
SCOTT@test01p> exec p_gennrate_many_bind_v_demo1(1000,:a);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.48
SCOTT@test01p> print :a
A
----
5801
--//6000個繫結變數,測試也就是00:00:00.48.第一次硬解析時間長一點正常的.
6.注意引數cursor_sharing=force:
--//最後注意cursor_sharing=force對於集合方式查詢的影響,因為這樣(1,:b1) 變成 (:"SYS_B001",:b1),這樣無形繫結變數引數增加.
SCOTT@test01p> @ a.txt 2000
SCOTT@test01p> alter session set cursor_sharing=force ;
Session altered.
SCOTT@test01p> @ b.txt
COUNT(DATA_OBJECT_ID)
---------------------
1185
1 row selected.
Elapsed: 00:00:48.51 ==>第1次.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1yb6a2tn2gsz6, child number 0
-------------------------------------
select count(data_object_id) from t where (:"SYS_B_0000",object_id) in
( (:"SYS_B_0001",:b1), (:"SYS_B_0002",:b2), (:"SYS_B_0003",:b3),
(:"SYS_B_0004",:b4), (:"SYS_B_0005",:b5), (:"SYS_B_0006",:b6),
(:"SYS_B_0007",:b7), (:"SYS_B_0008",:b8), (:"SYS_B_0009",:b9),
(:"SYS_B_0010",:b10), (:"SYS_B_0011",:b11), (:"SYS_B_0012",:b12),
(:"SYS_B_0013",:b13), (:"SYS_B_0014",:b14), (:"SYS_B_0015",:b15),
(:"SYS_B_0016",:b16), (:"SYS_B_0017",:b17), (:"SYS_B_0018",:b18),
(:"SYS_B_0019",:b19), (:"SYS_B_0020",:b20), (:"SYS_B_0021",:b21),
(:"SYS_B_0022",:b22), (:"SYS_B_0023",:b23), (:"SYS_B_0024",:b24),
(:"SYS_B_0025",:b25), (:"SYS_B_0026",:b26), (:"SYS_B_0027",:b27),
(:"SYS_B_0028",:b28), (:"SYS_B_0029",:b29), (:"SYS_B_0030",:b30),
(:"SYS_B_0031",:b31), (:"SYS_B_0032",:b32), (:"SYS_B_0033",:b33),
(:"SYS_B_0034",:b34), (:"SYS_B_0035",:b35), (:"SYS_B_0036",:b36),
(:"SYS_B_0037",:b37), (:"SYS_B_0038",:b38), (:"SYS_B_0039",:b39),
(:"SYS_B_0040",:b40), (:"SYS_B_0041",:b41), (:"SYS_B_0042",:b42),
Plan hash value: 2966233522
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 651 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 20 | 140 | 651 (36)| 00:00:01 |
----------------------------------------------------------------------------
--//這樣執行計劃也發生了變化,走全表掃描.
SCOTT@test01p> @ b.txt
COUNT(DATA_OBJECT_ID)
---------------------
1185
1 row selected.
Elapsed: 00:00:07.48
--//這樣執行時間也增加不少.
--//加入提示 /*+ CURSOR_SHARING_EXACT */ ,繼續測試:
SCOTT@test01p> @ b.txt
COUNT(DATA_OBJECT_ID)
---------------------
1185
1 row selected.
Elapsed: 00:00:00.34
SCOTT@test01p> @ b.txt
COUNT(DATA_OBJECT_ID)
---------------------
1185
1 row selected.
Elapsed: 00:00:00.14
--//可以發現執行時間減少不少.
總結:
--//總之:繫結變數數量不多,問題沒這麼嚴重.反倒是我前面使用集合繫結變數多執行時間長一些.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2215394/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180930]in list與繫結變數.txt變數
- [20210120]in list與繫結變數個數.txt變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數變數
- [20180912]PLSLQ與繫結變數.txt變數
- 繫結變數的一個例子變數
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- Oracle 繫結變數Oracle變數
- [20170929]& 代替冒號繫結變數.txt變數
- [20160706]like % 繫結變數.txt變數
- 【sql調優】繫結變數與CBOSQL變數
- [20190506]檢視巢狀與繫結變數.txt巢狀變數
- [20171231]PLSQL使用繫結變數.txtSQL變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- [20220414]toad與繫結變數peek.txt變數
- 從不繫結變數與繫結變數兩種情況討論柱狀圖的作用變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- ORACLE 繫結變數用法總結Oracle變數
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- [20200326]繫結變數抓取與NULL值.txt變數Null
- [20231210]執行計劃與繫結變數.txt變數
- 繫結變數的測試變數
- Oracle 繫結變數窺探Oracle變數
- oracle 繫結變數（bind variable）Oracle變數
- 如何獲取繫結變數變數
- Oracle 繫結變數 詳解Oracle變數
- 繫結變數窺測的演變變數
- [20160224]繫結變數的分配長度.txt變數
- [20150812]關於抓取繫結變數.txt變數
- [20121102]PLSQL中的繫結變數.txtSQL變數
- MySQL高階特性——繫結變數MySql變數
- SQL Developer中使用繫結變數SQLDeveloper變數
- 繫結變數之基本概念變數