[20210120]in list與繫結變數個數.txt
[20210120]in list與繫結變數個數.txt
--//以前寫的連結http://blog.itpub.net/267265/viewspace-2215394/
--//裡面提到如果繫結變數個數太多,會導致執行時替換繫結變數時間太長,導致sql語句執行緩慢.
--//在dbsnake 《基於Oracle的SQL最佳化》提到,我當時的測試並沒有測試出來,我當時想也許是版本問題.
--//前一陣子正好看完該書,我決定自己在各個版本重複測試看看.
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t as select * from all_objects;
create unique index i_t_object_id on t(object_id);
--//分析表略.
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
--//這樣生成b.txt指令碼.然後執行b.txt指令碼:
Elapsed: 00:00:00.17
Elapsed: 00:00:00.04
Elapsed: 00:00:00.04
--//我測試並沒有作者那樣慢.第一次硬解析時間有點長是正常的.
SCOTT@book> alter session set statistics_level = all;
Session altered.
Elapsed: 00:00:00.14
Elapsed: 00:00:00.07
Elapsed: 00:00:00.06
--//也沒有很大的差異.即使我取消索引.
SCOTT@book> alter index i_t_object_id invisible;
Index altered.
Elapsed: 00:00:00.13
Elapsed: 00:00:00.08
Elapsed: 00:00:00.08
4.繼續測試:
--//換一個版本測試.
SCOTT@192.168.100.33:1521/test> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
create table t as select * from all_objects;
create unique index i_t_object_id on t(object_id);
--//分析表略.
Elapsed: 00:00:00.32
Elapsed: 00:00:00.07
Elapsed: 00:00:00.08
SCOTT@192.168.100.33:1521/test> alter session set statistics_level = all;
Session altered.
Elapsed: 00:00:00.36
Elapsed: 00:00:00.08
Elapsed: 00:00:00.08
--//總之,我沒有遇到作者的情況,只有在繫結變數很大的情況下才會更加嚴重.
5.換到11g的環境,繼續測試繫結變數更多的情況:
SCOTT@book> alter index i_t_object_id visible;
Index altered.
繫結變數數量 執行時間
--------------------------
1000 00:00:00.04
2000 00:00:00.12
3000 00:00:00.30
4000 00:00:00.52
5000 00:00:00.92
6000 00:00:01.19
10000 00:00:03.41 (第1次執行00:00:06.26)
--//實際上3000個變數,甚至4000個以內並沒有這麼嚴重.
--//在10000個繫結變數的情況下做一個snapper測試:
SYS@book> @ tpt/snapper all 10 1 114
Sampling SID 114 with interval 10 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
114, SCOTT , STAT, Requests to/from client , 1, .1, , , , , 1 per execution
114, SCOTT , STAT, opened cursors cumulative , 1, .1, , , , , 1 per execution
114, SCOTT , STAT, user calls , 2, .2, , , , , 2 per execution
114, SCOTT , STAT, DB time , 1, .1, , , , , 1 per execution
114, SCOTT , STAT, non-idle wait count , 80, 8.14, , , , , 80 per execution
114, SCOTT , STAT, session uga memory , -340288, -34.63k, , , , , -340.29k per execution
114, SCOTT , STAT, session cursor cache hits , 1, .1, , , , , 0 softparses avoided thanks to cursor cache
114, SCOTT , STAT, parse count (total) , 1, .1, , , , , ~ softparses per hardparse
114, SCOTT , STAT, execute count , 1, .1, , , , , 1 executions per parse
114, SCOTT , STAT, bytes sent via SQL*Net to client , 70023, 7.13k, , , , , 70.02k bytes per roundtrip
114, SCOTT , STAT, bytes received via SQL*Net from client , 578651, 58.89k, , , , , 578.65k bytes per roundtrip
114, SCOTT , STAT, SQL*Net roundtrips to/from client , 1, .1, , , , , 1 per execution
114, SCOTT , TIME, parse time elapsed , 506, 51.5us, .0%, [ ], , ,
114, SCOTT , TIME, PL/SQL execution elapsed time , 1402, 142.69us, .0%, [ ], , ,
114, SCOTT , TIME, DB CPU , 7000, 712.44us, .1%, [ ], , ,
114, SCOTT , TIME, sql execute elapsed time , 3853, 392.15us, .0%, [ ], , ,
114, SCOTT , TIME, DB time , 6810, 693.1us, .1%, [ ], , , 1.72 % unaccounted time
114, SCOTT , WAIT, SQL*Net message to client , 2, .2us, .0%, [ ], 1, .1, 2us average wait
114, SCOTT , WAIT, SQL*Net more data to client , 35, 3.56us, .0%, [ ], 8, .81, 4.38us average wait
114, SCOTT , WAIT, SQL*Net message from client , 9650049, 982.15ms, 98.2%, [WWWWWWWWWW], 1, .1, 9.65s average wait
114, SCOTT , WAIT, SQL*Net more data from client , 686, 69.82us, .0%, [ ], 71, 7.23, 9.66us average wait
-- End of Stats snap 1, end=2021-01-20 10:03:33, seconds=9.8
<No active sessions captured during the sampling period>
-- End of ASH snap 1, end=2021-01-20 10:03:33, seconds=10, samples_taken=96
PL/SQL procedure successfully completed.
--//實際上賦值的時間佔用還是很長的.在我指令碼加入顯示日期的內容,執行情況如下:
SCOTT@book> @ b.txt
SYSDATE
-------------------
2021-01-20 10:10:59
1 row selected.
SYSDATE
-------------------
2021-01-20 10:11:05
1 row selected.
Elapsed: 00:00:00.00
COUNT(DATA_OBJECT_ID)
---------------------
2135
1 row selected.
Elapsed: 00:00:03.44
SYSDATE
-------------------
2021-01-20 10:11:09
1 row selected.
Elapsed: 00:00:00.00
--//可以發現定義變數以及賦值使用6秒.而執行時間並不是很長.
--//取消賦值部分,snapper看到的情況是:
SYS@book> @ tpt/snapper all 5 1 114
Sampling SID 114 with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
114, SCOTT , STAT, Requests to/from client , 6, 1.23, , , , , 2 per execution
114, SCOTT , STAT, opened cursors cumulative , 3, .62, , , , , 1 per execution
114, SCOTT , STAT, user calls , 9, 1.85, , , , , 3 per execution
114, SCOTT , STAT, session logical reads , 266, 54.57, , , , , 19.25k total buffer visits
114, SCOTT , STAT, CPU used when call started , 2, .41, , , , , .67 per execution
114, SCOTT , STAT, CPU used by this session , 2, .41, , , , , .67 per execution
114, SCOTT , STAT, DB time , 2, .41, , , , , .67 per execution
114, SCOTT , STAT, non-idle wait count , 75, 15.39, , , , , 25 per execution
114, SCOTT , STAT, consistent gets , 266, 54.57, , , , , 88.67 per execution
114, SCOTT , STAT, consistent gets from cache , 266, 54.57, , , , , 88.67 per execution
114, SCOTT , STAT, consistent gets from cache (fastpath) , 151, 30.98, , , , , 50.33 per execution
114, SCOTT , STAT, consistent gets - examination , 115, 23.59, , , , , 38.33 per execution
114, SCOTT , STAT, logical read bytes from cache , 2179072, 447.06k, , , , , 726.36k per execution
114, SCOTT , STAT, calls to get snapshot scn: kcmgss , 3, .62, , , , , 1 per execution
114, SCOTT , STAT, no work - consistent read gets , 151, 30.98, , , , , 50.33 per execution
114, SCOTT , STAT, table fetch by rowid , 9511, 1.95k, , , , , 3.17k per execution
114, SCOTT , STAT, index fetch by key , 10000, 2.05k, , , , , 3.33k per execution
114, SCOTT , STAT, session cursor cache hits , 3, .62, , , , , 0 softparses avoided thanks to cursor cache
114, SCOTT , STAT, buffer is pinned count , 18983, 3.89k, , , , , 98.62 % buffer gets avoided thanks to buffer pin caching
114, SCOTT , STAT, buffer is not pinned count , 134, 27.49, , , , , 44.67 per execution
114, SCOTT , STAT, parse count (total) , 3, .62, , , , , ~ softparses per hardparse
114, SCOTT , STAT, execute count , 3, .62, , , , , 1 executions per parse
114, SCOTT , STAT, bytes sent via SQL*Net to client , 1600, 328.26, , , , , 266.67 bytes per roundtrip
114, SCOTT , STAT, bytes received via SQL*Net from client , 570118, 116.97k, , , , , 95.02k bytes per roundtrip
114, SCOTT , STAT, SQL*Net roundtrips to/from client , 6, 1.23, , , , , 2 per execution
114, SCOTT , TIME, parse time elapsed , 414, 84.94us, .0%, [ ], , ,
114, SCOTT , TIME, DB CPU , 21997, 4.51ms, .5%, [ ], , ,
114, SCOTT , TIME, sql execute elapsed time , 19326, 3.96ms, .4%, [ ], , ,
114, SCOTT , TIME, DB time , 22041, 4.52ms, .5%, [ ], , , 2.63 % unaccounted time
114, SCOTT , WAIT, SQL*Net message to client , 5, 1.03us, .0%, [ ], 6, 1.23, .83us average wait
114, SCOTT , WAIT, SQL*Net message from client , 4723914, 969.16ms, 96.9%, [WWWWWWWWWW], 6, 1.23, 787.32ms average wait
114, SCOTT , WAIT, SQL*Net more data from client , 659, 135.2us, .0%, [ ], 69, 14.16, 9.55us average wait
-- End of Stats snap 1, end=2021-01-20 10:19:43, seconds=4.9
<No active sessions captured during the sampling period>
-- End of ASH snap 1, end=2021-01-20 10:19:43, seconds=5, samples_taken=47
PL/SQL procedure successfully completed.
SYS@book> @ tpt/snapper ash 5 1 114
Sampling SID 114 with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
<No active sessions captured during the sampling period>
-- End of ASH snap 1, end=2021-01-20 10:21:09, seconds=5, samples_taken=42
PL/SQL procedure successfully completed.
--//ash 什麼也沒有抓到.
--//總之我沒有遇到作者的情況,也許是某個版本的問題吧.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2751278/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180930]in list與繫結變數個數.txt變數
- [20180930]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變數
- 繫結變數之基本概念變數