业务反馈一套测试库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;
最终处理,应用反馈测试可以走通了