[20250615]sqlplus大量赋值的问题.txt

[20250615]sqlplus大量赋值的问题.txt --//前段时间测试大量绑定变量sq语句l执行"缓慢"的问题，实际上问题出在sqlplus客户端，通过pstack观察发现sqlplus要大量调用 --//lxoBinCmpMutl函数。当时猜测也许是sqlplus获得绑定变量定位方式"不合理"导致的情况，一直没时间验证这个问题，主要实际的情 --//况以及执行语句很少存在非常多绑定变量的情况。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.建立测试脚本： $ cat a.txt set pagesize 0 set head off set feedback off set verify off set timing off spool b1.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; spool off spool b2.txt --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 --//分别建立2个文件b1.txt赋值，b2.txt脚本执行。 $ cat lx.gdb set pagination off set logging overwrite on set logging on set $lx_count  = 0 break lxoBinCmpMutl commands  silent  printf "lxoBinCmpMutl count %02d -", ++$lx_count  c  end --//set $lx_count  = 0 3.测试： --//session 1: SCOTT@book01p> @ spid ============================== SID                           : 142 SERIAL#                       : 40846 PROCESS                       : 3848 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SERVER                        : DEDICATED SPID                          : 3850 PID                           : 49 P_SERIAL#                     : 7 KILL_COMMAND                  : alter system kill session '142,40846' immediate; PL/SQL procedure successfully completed. --//sqlplus进程号是3848。 --//window 1: $ gdb -f -p 3848 -x lx.gdb --//session 1: SCOTT@book01p> variable c1 number --//window 1: Breakpoint 1 at 0x7f6edc932ec0 (gdb) c Continuing. lxoBinCmpMutl count 01 .... lxoBinCmpMutl count 47 --//可以发现简单的定义新的变量c1，调用lxoBinCmpMutl 47次。 --//按ctrl+c退出gdb，再次启动执行 $ gdb -f -p 3848 -x lx.gdb --//session 1,再次定义新的变量c2: SCOTT@book01p> variable c2 number --//window 1: Breakpoint 1 at 0x7f6edc932ec0 (gdb) c Continuing. lxoBinCmpMutl count 01 ... lxoBinCmpMutl count 47 lxoBinCmpMutl count 48 --//可以发现简单的定义新的变量c2，调用lxoBinCmpMutl 48次,比前面的variable c1 number多调用1次lxoBinCmpMutl。 --//可以推测定义变量越多调用lxoBinCmpMutl越多。赋值以及sqlplus执行sql语句也出现类似的情况，不再测试。 --//估计sqlplus探查定义变量的算法采用逐个探查的方式，如果类似存在hash链表之类的探查就不存在这个问题。 --//继续看后面的测试。 4.继续： --//假设sqlplus会话存在许多绑定变量。 SCOTT@book01p> @ a.txt 65535 -//输出略。 --//编辑b1.txt，并且适当编辑满足前面begin..end之间可以定义32767个绑定变量的限制。 SCOTT@book01p> @ b1.txt --//等待执行完成。 $ ps -efvp 4171   PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND  4171 pts/7    Ss+    0:01      0    13 122270 16460  0.2 /u01/app/oracle/product/21.0.0/dbhome_1/bin/sqlplus $ ps -efvp 4171   PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND  4171 pts/7    Rs+    2:05      0    13 153938 46240  0.5 /u01/app/oracle/product/21.0.0/dbhome_1/bin/sqlplus --//执行过程可以发现DRS，RSS不断增加。 $ ps -efvp 4171   PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND  4171 pts/7    Ss+    4:23      0    13 156906 50476  0.6 /u01/app/oracle/product/21.0.0/dbhome_1/bin/sqlplus 画一个表格： --------------------------------------------------- 执行语句                   gdb最后显示 --------------------------------------------------- select :b1 from dual      lxoBinCmpMutl count 5022 select :b2 from dual      lxoBinCmpMutl count 5023 select :b3 from dual      lxoBinCmpMutl count 5024 select :b4 from dual      lxoBinCmpMutl count 5025 ... select :b9 from dual      lxoBinCmpMutl count 5030 --//每次+1.5030-5022=8 select :b10 from dual     lxoBinCmpMutl count 5022 --//与前面:b1测试一样。 select :b11 from dual     lxoBinCmpMutl count 5023 --//与前面:b2测试一样。 select :b12 from dual     lxoBinCmpMutl count 5024 --//与前面:b3测试一样。 ... select :b99 from dual     lxoBinCmpMutl count 5111 --//每次+1.5111-5022 = 89 --//测试到这里，实际上可以猜测sqlplus按照变量的长度建立1个链表,根据定义顺序存放。或者按照变量的长度分类。 select :b100 from dual    lxoBinCmpMutl count 5022 --//验证自己的判断。 select :b101 from dual    lxoBinCmpMutl count 5023 ... select :b300 from dual    lxoBinCmpMutl count 5222 select :b300 from dual    lxoBinCmpMutl count 5322 ... select :b999 from dual    lxoBinCmpMutl count 5921 --//每次+1. 5921-5022  = 899 select :b1000 from dual   lxoBinCmpMutl count 5022 --//验证自己的判断。 ... select :b2000 from dual   lxoBinCmpMutl count 6022 ... select :b9999 from dual   lxoBinCmpMutl count 14021 --//每次+1. 14021-5022 = 8999 select :b10000 from dual  lxoBinCmpMutl count 5023 --//这里为什么比前面多1，是因为登录时我的环境定义了变量MY_SID，长度为6. ... select :b65535 from dual  lxoBinCmpMutl count 60558 --//每次+1. 60558-5023 = 55535 select :b0 from dual      lxoBinCmpMutl count 56    --//该变量不存在，按照理解应该至少调用lxoBinCmpMutl 5030次。5030-56= 4974 select :a01 from dual     lxoBinCmpMutl count 137   --//该变量不存在，按照理解应该至少调用lxoBinCmpMutl 5111次。5111-137 = 4974 select :b65536 from dual  lxoBinCmpMutl count 55584 --//该变量不存在，按照理解应该至少调用lxoBinCmpMutl 60558次。60558-55584 = 4974 --//gdb可以不需要退出，按ctrl+c中断后，执行set $lx_count=0，可以重新计数。 select :b1,:b1 from dual         lxoBinCmpMutl count 9996 select :b1,:b2 from dual         lxoBinCmpMutl count 9998 select :b65535,b65534 from dual  lxoBinCmpMutl count 121068 --//看看执行b2.txt的情况： SCOTT@book01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book01p> set timing on SCOTT@book01p> @ b2.txt COUNT(DATA_OBJECT_ID) ---------------------                  3970 1 row selected. Elapsed: 00:04:53.35 SCOTT@book01p> @ b2.txt COUNT(DATA_OBJECT_ID) ---------------------                  3970 1 row selected. Elapsed: 00:04:47.79 SCOTT@book01p> @ dpc '' '-peeked_binds -projection' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  97pbm7taxr6d8, 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: 2966233522 --------------------------------------------------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |        |       |  1013 (100)|          |      1 |00:00:00.03 |    1479 | |   1 |  SORT AGGREGATE    |      |      1 |      1 |     7 |            |          |      1 |00:00:00.03 |    1479 | |*  2 |   TABLE ACCESS FULL| T    |      1 |   4808 | 33656 |  1013  (60)| 00:00:01 |   3970 |00:00:00.03 |    1479 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / "T"@"SEL$1" Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter(("DATA_OBJECT_ID" IS NOT NULL AND INTERNAL_FUNCTION("OBJECT_ID"))) 39 rows selected. Elapsed: 00:00:15.65 --//执行计划选择全表扫描，但是注意看A-Time=00:00:00.03，大部分时间花在sqlplus的客户端探查绑定变量值到sql语句上。 5.小结： --//猜测sqlplus按照变量的长度建立1个链表,根据定义顺序存放或者按照变量的长度分类。 --//不清楚变量不存在的情况。