一、概述
近期某医院客户现场反应其his系统的费用结算业务无法正常使用，联系应用厂家得知近期也未做任何业务调整，经过分析排查发现：是费用结算的SQL语句从原先的毫秒级输出到现在30分钟都无结果输出，通过查询Oracle MOS最终确认是Bug 27701460导致select hang的问题，此BUG修复程序包含在20.1.0中，可通过设置"_fix_control"='14402409:off'来临时解决这个问题。
二、故障分析
1. 环境描述
1.1 平台情况
硬件环境：Oracle Exadata x8m-2 一体机
数据库版本：Oracle 19.9.0.0.201020 RAC
操作系统版本：Oracle Linux 7.8
1.2 变更说明
数据库于2020年12月从red hat 6+oracle 11.2.0.4 单实例迁移至 Oracle Exadata x8m-2 一体机+ Oracle 19.9 RAC上。
2. 故障现象
his系统的费用结算业务无法正常打开，费用结算SQL语句不能正常毫秒级输出结果，SQL语句如下：（注意：表名已用xxx代替）
SELECT a.SEQUENCE_NO SEQUENCE_NO, a.SICK_ID SICK_ID, a.RESIDENCE_NO RESIDENCE_NO, a.ITEM_CLASS ITEM_CLASS, a.ITEM_CODE ITEM_CODE, a. ITEM_NAME ITEM_NAME, a.SPEC SPEC, a. PRICE PRICE, a. QUANTITY QUANTITY, a.UNIT UNIT, a.APPLY_NO APPLY_NO, a. APPLY_DEPT APPLY_DEPT, a. APPLY_DOCTOR APPLY_DOCTOR, a. EXEC_DEPT EXEC_DEPT, a.EXEC_MAN EXEC_MAN, a.COST COST, a.CHARGES CHARGES, a. OWE_MONEY OWE_MONEY, a.DERATE_CHARGES DERATE_CHARGES, a.OPERATOR OPERATOR, a.OPERATION_TIME OPERATION_TIME, a.RECEIPT_CLASS RECEIPT_CLASS, 'Y' COST_FLAG, *COST_FLAG*/ 'Y' SELECT_FLAG, a.DERATE_NO DERATE_NO, a. ITEM_ON_DEPT_STATS ITEM_ON_DEPT_STATS, a.ITEM_ON_CASE_OUTLAY ITEM_ON_CASE_OUTLAY, a.ITEM_ON_TREASURER_SUBJECT ITEM_ON_TREASURER_SUBJECT, a.FADE_ITEM_NO FADE_ITEM_NO, a.FADE_QUANTITY FADE_QUANTITY, a.SETTLE_NO SETTLE_NO, a.COST_MODE COST_MODE, a.ARCHIVE ARCHIVE, 0 + null APPLY_SUB_NO, a.SETTLE_NO SETTLE_NO_X, a.RATE_TYPE RATE_TYPE, a. INSUR_PRICE_FLAG INSUR_PRICE_FLAG, a.APPLY_DOCTOR_GROUPING_CODE APPLY_DOCTOR_GROUPING_CODE, a.CONTRACT_UNIT CONTRACT_UNIT, a.OPERATOR_GROUPING_CODE OPERATOR_GROUPING_CODE, '' INSUR_DISEAS, '' INSUR_PERCENT, /*decode( a.spec, null, (select INSURANCE_CLASS from AAA b where a.item_code = b.item_code), (select INSURANCE_CLASS from BBB b where a.item_code = b.physic_code)) INSURANCE_CLASS, 石油医保需要按费别取本费别的医保类型*/ /*nvl((select b.INSUR_TYPE from CCC b where b.RATE_TYPE= a.RATE_TYPE and b.ITEM_CODE = a.ITEM_CODE),'自费') INSURANCE_CLASS,*/ (f_get_item_insurance_class(a.item_code, a.item_class, a.RATE_TYPE)) INSURANCE_CLASS, /*f. CLINIC_CLASS CLINIC_CLASS*/ nvl((select f. CLINIC_CLASS from DDD f where f.sick_id = a.sick_id and f.nullah_number = a.residence_no),'') CLINIC_CLASS,
/*lxw 20130822 增加慢病类型*/ (select c.ICD_NAME from EEE b, FFF c, GGG t where b.prescription_number = t.PRESCRIBE_NUMBER and b.icd_code = c.icd_code and c.icd_class = '02' and t.apply_no = a.apply_no and b.sick_id = a.sick_id and rownum = 1) icd_name, /*慢病类型*/ (select u.unit_name from EEE b, HHH u, GGG t where u.unit_code = b.icd_code and b.prescription_number = t.PRESCRIBE_NUMBER and t.apply_no = a.apply_no and b.sick_id = a.sick_id and u.unit_type = '13' and u.valid_flag = 'Y' and rownum = 1) unit_name, /*下次取药时间->门诊慢病结算单打印需要*/ (select decode(nvl(d.day_count, -1), -1, '', to_char(sysdate + 1 + decode(a.item_class, 'C01', round(d.day_count/(select t.freq_count from JJJ t where t.FREQ_DESCRIBE_CODE = d.frequency) + 0.4, 0), d.day_count ), 'yyyy-mm-dd')) from KKK d, GGG v where a.apply_no = v.apply_no and a.sick_id = v.sick_id and v.prescribe_number = d.prescription_number and v.prescribe_sub_number = d.sequence_number and rownum = 1) next_date, a.import_price, a.import_money FROM LLL a/*, DDD f*/ WHERE ( a.SICK_ID = '0000000230') /*AND a.SICK_ID = f.SICK_ID AND (a.RESIDENCE_NO = f.NULLAH_NUMBER)*/ AND ( a.settle_no is null) AND ( a.OPERATION_TIME < date'2021-04-23') |
3. SQL语句分析
3.1 SQL语句说明
Ø 通过收集故障时间范围内AWR报告和ASH报告，可以很明显的找出问题SQL语句，SQL_ID是1g4ab3485hzyp。
Ø 此SQL语句有2个绑定变量，在SQL语句最后where后面
Ø 此SQL语句是业务程序多个子模块SQL拼接而成，从12年运行到现在都未做过调整。
Ø 此SQL语句中的表都是千万级数据量，且有个视图是3层架构：视图à包含3个视图à包含5个百万级数据量大表，且是union all方式组合。
1.1 SQL语句分析
1) 当SQL语句执行突然变慢时，一般首先怀疑的是执行计划改变了，使用dba_hist_sqlstat 和 dba_hist_snapshot视图通过SQL_ID号检查执行计划并没有变更，排除执行计划变更的问题。
2) 使用sqlhc.sql脚本检查SQL语句里面的统计信息比较新了，数据量变化不大，且执行计划未发生变更，排除表统计信息不准的问题。
3) 使用addm+sqltrpt的方式看看有什么可以优化的，手动绑定sql_profile后，故障现象依旧。
4) 此问题SQL语句在其他家医院（选定数据量大小相近的）手动测试，0.6秒输出结果，检查SQL语句的执行计划，和本地SQL语句执行计划一模一样。
5) 通过改写上述的SQL语句，也能实现毫秒级输出结果，但是应用侧需要修改程序，操作难度较大，甲方不允许修改。
6) 经过把SQL语句拆分执行，最终定位到问题点是：SQL语句最后面GGG v这个视图和LLL a表关联时一直hang在那，当这部分语句毫秒级输出时，整个SQL语句都能毫秒级输出结果。
7) 通过对SQL语句收集10046和10053进行详细的执行步骤追踪，可以发现SQL语句底层被拆分成多个小SQL语句执行，且每部分都是PGA memory operation这个等待事件占比最多。
通过使用PGA memory operation关键字在MOS中查询，最终和MOS文档SelectHangs After Upgrade From 11.2.0.4 To 12.2.0.1 Waiting on 'PGA memory operation'(Doc ID 2443753.1)中的描述一致
【问题现象】
说明：（1）数据库是从11.2.0.4升级至12.2.0.1及以后；
（2）select 查询hang住，因为PGA memory operation；
【原因】
触发BUG：Bug 27701460，此BUG修复程序包含在20.1.0中。
【解决方法】
说明：
Oracle 19.9 中已经包含Bug 28406374，无需应用。
通过使用alter session set "_fix_control"='14402409:off';或 /*+opt_param('_fix_control', '14402409:0')*/ 有问题的SQL语句秒级输出，至此问题点确认。
三、故障总结
经过上述分析可以确认： SQL select 语句执行异常缓慢的问题是因为触发了Bug 27701460，此BUG主要会发生于upgraded from 11.2.0.4 to 12.2.0.1及以后，且BUG修复程序包含在20.1.0中，可以在session级别临时的解决这个问题：
alter session set "_fix_control"='14402409:off'; or in a hint /*+opt_param('_fix_control', '14402409:0')*/ |
题外话："PGA memory operation"只是一个新的等待事件用来衡量之前版本上并未测量的一个等待。这个等待事件较高很大可能是因为底层硬件出现了CPU或者内存的短缺。在 12.2.0.1 环境中，可用物理内存大小与 TABLE ACCESS FULL 和 MERGE JOIN 操作的块大小相比非常低。因此，OS 端内存资源操作的开销花费了大量的 CPU 时间。
但是本案例中用的是Oracle一体机，硬件性能毋庸置疑，且只是其中一个PDB出现这个问题，发生故障时主机资源也很空闲，通过PDB的AWR报告也能看出PDB的PGA设置完全没问题，和Bug 27701460吻合，故建议：当出现"PGA memory operation"等待事件时不要盲目的去加大主机资源或者数据库PGA。
参考文献：
SelectHangs After Upgrade From 11.2.0.4 To 12.2.0.1 Waiting on 'PGA memory operation'(Doc ID 2443753.1)
