[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个链表,根据定义顺序存放或者按照变量的长度分类。
--//不清楚变量不存在的情况。
--//前段时间测试大量绑定变量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个链表,根据定义顺序存放或者按照变量的长度分类。
--//不清楚变量不存在的情况。