改变CBO模式 处理ora-64203

业务反馈一套测试库sql语句执行出现ora-64203  表结构与 生产库完全一致 请求协助排查原因

 

语句如下

 Select 段落内容 From (
Select 类别,段落内容 From(
Select decode(A.段落名称,'主诉',1,2) As 类别,to_char(A.段落内容) AS 段落内容 From EMR_D住院病历段落 A
Inner Join EMR_D住院病历列表 B On A.病历序号I=B.系统序号
Where B.住院序号i='3466' And (A.段落名称='主诉' Or A.段落名称='现病史') Order By A.系统序号)
Union All
Select 类别,段落内容 From(
Select Null As  类别,to_char(症状及体征) As 段落内容 From EXP_D检验申请合并 Where 病人来源='住院' And 病人序号i='3466' And 症状及体征 Is Not Null Order By 系统序号 Desc
)
Union All
Select 类别,段落内容 From(
Select Null As  类别,to_char(症状及体征) As 段落内容 From INS_D检查申请合并 Where 病人来源='住院' And 病人序号i='3466' And 症状及体征 Is Not Null  Order By 系统序号 Desc
)) A Where A.段落内容 Is Not Null
  Order By 类别;

现场反馈 语句去掉 Where A.段落内容 Is Not Null 是可以反馈结果 

对语句进行分析

定位到to_char转换clob，

使用自定义函数 调整后语句可以正常返回结果

 Select 段落内容 From (
Select 类别,段落内容 From(
Select decode(A.段落名称,'主诉',1,2) As 类别,my_clob_substr(A.段落内容) AS 段落内容 From EMR_D住院病历段落 A
Inner Join EMR_D住院病历列表 B On A.病历序号I=B.系统序号
Where B.住院序号i='3466' And (A.段落名称='主诉' Or A.段落名称='现病史') Order By A.系统序号)
Union All
Select 类别,段落内容 From(
Select Null As  类别,my_clob_substr(症状及体征) As 段落内容 From EXP_D检验申请合并 Where 病人来源='住院' And 病人序号i='3466' And 症状及体征 Is Not Null Order By 系统序号 Desc
)
Union All
Select 类别,段落内容 From(
Select Null As  类别,my_clob_substr(症状及体征) As 段落内容 From INS_D检查申请合并 Where 病人来源='住院' And 病人序号i='3466' And 症状及体征 Is Not Null  Order By 系统序号 Desc
)) A 
Where A.段落内容 Is Not Null
  Order By 类别;

 但是生产库可以执行，调整语句改写不太有说服力

进一步分析发现生产库执行计划于测试库执行计划有差异

生产库 EMR_D住院病历段落与EMR_D住院病历列表 属于大表与小表关联，根据选择率分析应该走nest loop ，

 

 

而测试库走的是hash join 并且少走一个索引

找到关键问题了，这就好办了

那面执行计划为何走错？原因包括

sql语句运行的环境
绑定参数入值越界
相关对象执行过ddl变更
系统参数调整等

生产库配置较高 sga为40G，测试库只有4G 尝试重新收集统计信息无效，使用hint 后语句没报ora-64203，在不改变代码的情况下，尝试固定执行计划，因语句无法执行获取到sqlid，最终调整优化器模式为rule

alter system set optimizer_mode=rule；

最终处理，应用反馈测试可以走通了