ORACLE数据库SQL中使用大量绑定变量导致数据库关闭（ORA-07445）

数据库在晚上九点多挂掉了，查看报错日志，如下

Tue Jul 14 21:57:28 2020
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1F72E] [PC:0x184FE23, opiaba()+583] [flags: 0x0, count: 1]
Errors in file /oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_ora_61686.trc  (incident=622249):
ORA-07445: exception encountered: core dump [opiaba()+583] [SIGSEGV] [ADDR:0x1F72E] [PC:0x184FE23] [Address not mapped to object] []
Incident details in: /oracle/diag/rdbms/xxxxx/xxxxx/incident/incdir_622249/xxxxxx_ora_61686_i622249.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Jul 14 21:57:38 2020
Dumping diagnostic data in directory=[cdmp_20200714215738], requested by (instance=1, osid=61686), summary=[incident=622249].
Tue Jul 14 21:57:42 2020
Sweep [inc][622249]: completed
Sweep [inc2][622249]: completed
Tue Jul 14 21:58:38 2020
Errors in file /oracle/diag/rdbms/xxxx/xxxx/trace/xxxxxx_pmon_28148.trc  (incident=600017):
ORA-00600: internal error code, arguments: [17147], [0x1209F04AC8], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/xxxxxxx/xxxxxx/incident/incdir_600017/xxxxxx_pmon_28148_i600017.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/diag/rdbms/xxxxxx/xxxxxx/trace/xxxxxx_pmon_28148.trc:
ORA-00600: internal error code, arguments: [17147], [0x1209F04AC8], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 28148): terminating the instance due to error 472

Tue Jul 14 21:58:40 2020
opiodr aborting process unknown ospid (16323) as a result of ORA-1092
Tue Jul 14 21:58:40 2020
opiodr aborting process unknown ospid (11039) as a result of ORA-1092
Tue Jul 14 21:58:40 2020
opiodr aborting process unknown ospid (11037) as a result of ORA-1092
Tue Jul 14 21:58:40 2020
ORA-1092 : opitsk aborting process
Tue Jul 14 21:58:40 2020
opiodr aborting process unknown ospid (11035) as a result of ORA-1092
Instance terminated by PMON, pid = 28148

可以看到实例是被PMON杀死的，查看报错中的trc文件

Dump continued from file: /oracle/diag/rdbms/xxxxxx/xxxxx/trace/xxxxx_ora_61686.trc
ORA-07445: exception encountered: core dump [opiaba()+583] [SIGSEGV] [ADDR:0x1F72E] [PC:0x184FE23] [Address not mapped to object] []

========= Dump for incident 622249 (ORA 7445 [opiaba()+583]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1F72E] [PC:0x184FE23, opiaba()+583] [flags: 0x0, count: 1]
Registers:
%rax: 0x0000000000000000 %rbx: 0x0000001209f09db0 %rcx: 0x0000000000000000
%rdx: 0x0000000000000000 %rdi: 0x000000000000fb97 %rsi: 0x000000000000ffee
%rsp: 0x00007fff561f1820 %rbp: 0x00007fff561f1890  %r8: 0x0000001209e89e48
 %r9: 0x00000000000000ff %r10: 0x0000000000003739 %r11: 0x00000012ace37508
%r12: 0x00000012ac06adf0 %r13: 0x0000000000000000 %r14: 0x000000129607cdb0
%r15: 0x0000000000000168 %rip: 0x000000000184fe23 %efl: 0x0000000000010213
  opiaba()+571 (0x184fe17) je 0x184fe3b
  opiaba()+573 (0x184fe19) add $-1,%ecx
  opiaba()+576 (0x184fe1c) movsxdq %ecx,%rdi
  opiaba()+579 (0x184fe1f) mov -0x58(%rbp),%rcx
> opiaba()+583 (0x184fe23) movzwl (%rcx,%rdi,2),%esi
  opiaba()+587 (0x184fe27) test %esi,%esi
  opiaba()+589 (0x184fe29) jnz 0x184ff0c
  opiaba()+595 (0x184fe2f) mov -0x58(%rbp),%rsi
  opiaba()+599 (0x184fe33) movzwl 0x2a(%rbx),%ecx

*** 2020-07-14 21:57:28.804
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=9tt4rdcg36vp3) -----
begin  
                        insert into xxxx.xxxxx
                        (id,sorting_id,model_code,stage_code,
                        part_name,part_sn,del_flag,add_by,add_date,sorting_sn,sorting_config)
                        VALUES
                        (xxxx.xxxxx.NEXTVAL,:1,:2,
                        :3,:4, :5,0,:6,sysdate,:7,:8)
                 ; 
                        insert into xxxx.xxxxx
                        (id,sorting_id,model_code,stage_code,
                        part_name,part_sn,del_flag,add_by,add_date,sorting_sn,sorting_config)
                        VALUES
                        (xxxx.xxxxx.NEXTVAL,:9,:10,
                        :11,:12, :13,0,:14,sysdate,:15,:16)
                 ; 
                        insert into xxxx.xxxxx
                        (id,sorting_id,model_code,stage_code,
                        part_name,part_sn,del_flag,add_by,add_date,sorting_sn,sorting_config)
                        VALUES
                        (xxxx.xxxxx.NEXTVAL,:17,:18,
                        :19,:20, :21,0,:22,sysdate,:23,:24)
                 ; 
...................
                        insert into xxxx.xxxxx
                        (id,sorting_id,model_code,stage_code,
                        part_name,part_sn,del_flag,add_by,add_date,sorting_sn,sorting_config)
                        VALUES
                        (xxx.xxxxx.NEXTVAL,:587137,:587138,
                        :587139,:587140, :587141,0,:587142,sysdate,:587143,:587144)
                 ; 
                        insert into xxxx.xxxxx
                        (id,sorting_id,model_code,stage_code,
                        part_name,part_sn,del_flag,add_by,add_date,sorting_sn,sorting_config)
                        VALUES
                        (xxxx.xxxxx.NEXTVAL,:587145,:587146,
                        :587147,:587148, :587149,0,:587150,sysdate,:587151,:587152)
                 ;end;

可以看到大量的insert语句，并且这个语句块（begin--》end）用了587152个绑定变量。

开发的程式，用的应该是java，mybatis。

 

 经过分析，发现上述代码在执行时，MyBatis会将循环的SQL当成一个匿名块发给oracle，oracle会一次性对这个块中的所有SQL进行绑定变量并执行，而不是一条一条的执行，当变量超过65535时，就会触发oracle的bug 12578873，导致实例崩溃

参考：https://www.jianshu.com/p/f70d8bbee075

我们知道上面是一个简单的批量执行语句，但是通过分析可知，MyBatis将循环拼接成一个类似下面的匿名SQL块送给数据库做预分析。MyBatis底层机制是将mapper xml 转换成JDBC中 PreparedStatement预处理，整块代码送给Oracle数据库一次性做变量绑定。Oracle会一次性的对这些SQL顺序绑定变量（尽管block里面是很多单单条SQL组成），这样随着循环体内元素数量变大，需要的变量也会顺序增长，而Oracle只预设了一次性分配65535个变量的阈值，当需要绑定的变量个数超过这个阈值时，就报错了，甚至触发Bug，导致数据库实例Down掉。