oracle 11.2.0.1告警日志报错ORA-03137与绑定变量窥探BUG9703463

    2017年12月份第二次oracle数据库巡检中，发现某一地市oracle数据库发现SQL语句触发特定版本BUG，详细信息如下：

操作系统版本：windows server 2008R2

数据库版本：oracle 11.2.0.1

问题描述：2017年12月份第二次巡检中，发现告警日志报错，报错信息如下：

19/12/2017 08:27:35 Tue Dec 19 08:27:35 2017 
ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] [] 
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5480.trc (incident=36699): 
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_36699\orcl_ora_5480_i36699.trc 
18/12/2017 17:19:56 Mon Dec 18 17:19:56 2017 
ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] [] 
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_36554\orcl_ora_4572_i36554.trc 
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4572.trc (incident=36554): 
18/12/2017 16:18:58 ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] [] 
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3968.trc (incident=36547): 
Mon Dec 18 16:18:58 2017 
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_36547\orcl_ora_3968_i36547.trc

根据orcl_ora_5480_i36699.trc 文件发现触发ORA-03137的应用SQL语句：

-----sql_id=cjx2sya2mu4zm  
select *  
from (select row_.*, rownum NumRow  
from (select *  
from (select  
sid,  
code,  
to_char(month, 'yyyyMM') as month,  
hisid,  
bill_no,  
state,  
billdate,  
hospital_id,  
patient_id,  
patient_name,  
admission_number,  
admission_disease_name,  
disease_name,  
claim_name,  
benefit_name,  
bmino,  
benefit_group_name,  
item_date,  
dept_id,  
dept_name,  
item_id,  
item_name,  
item_type,  
physician_name,  
bmi_convered_amount,  
bmi_nopay,  
reject_reson,  
remrk,  
version_no,  
hospital_backs,  
versionstate,  
rule_name,  
back_reson,  
reback_reason,  
processState,  
is_approval,  
nvl(version, 1) as version,  
nvl(trickProgress, 0) as trickProgress,  
nvl(is_retrick, 0) as is_retrick,  
PERIOD,  
billex.NUMBER01 as Number01,  
billex.NUMBER02 as Number02,  
billex.NUMBER03 as Number03,  
billex.NUMBER05 as Number05,  
billex.NUMBER06 as Number06,  
billex.NUMBER07 as Number07,  
HOSPITAL_REMARK_DETAIL,  
decode(bitand((select sum(distinct(nvl(g.rule_bit, 0)))  
from gz_list g  
where g.business_type = '0'),  
rule_bit),  
0,  
0,  
1) as BUSINESS_TYPE,  
REFEEDBACK_REASON_DETAIL,  
(select sum(a.reject_money)  
from dw_opinion_details b  
join dw_billdetail a  
on a.id = b.detailid  
where b.code = dw_opinions.code  
and b.version_no = dw_opinions.version_no  
and b.month = dw_opinions.month) as sumrejectmoney  
from dw_opinions  
left join dw_bill_ex billex  
on dw_opinions.hisid = billex.billid  
where 1 = 1  
and month = to_date(:ParamMonth0, 'yyyyMM')  
and hospital_id = :ParamHospitalId1 
and version_no = :versionno2 
and bill_no = :ParamBillNo3 
order by month desc, sid)) row_  
where rownum <= 10)  
where NumRow > 0 ；

查询Oracle官网， ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48]  与应用SQL语句使用绑定变量有关，是 非公共 Bug:9703463（ 文档 ID 1615363.1 ）：

解决办法：

1、解决Oracle 11.2.0.1 因绑定变量触发ora-03137错误的补丁已包含在PSU补丁集 Patch:10245351中，需要对数据库应用补丁集 Patch:10245351

2、关闭oracle 11.2.0.1绑定变量功能： alter system set "_optim_peek_user_binds"=false;

     风险：将导致数据库不稳定，引起应用sql语句执行计划不准确

3、将数据库版本升级到11.2.0.3以上版本可解决ORA-03137问题