ITPub博客

首页 > 数据库 > Oracle > [20210120]in list与绑定变量个数.txt

[20210120]in list与绑定变量个数.txt

原创 Oracle 作者:lfree 时间:2021-01-20 10:26:15 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2855
  • 访问量
    6643914