[20210120]in list與繫結變數個數.txt

lfree發表於2021-01-20

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章