乱用OracleHint造成性能问题案例二

后面经检查发现在这套系统中,有大理SQL使用了/*+ rule */ Hint，最好的处理方法是修改SQL代码，这里为了应急我使用了_optimizer_

某医保系统在业务高峰期间相关模块使用不了查询不出结果，生成业务高峰期间上午10点到11点期间的AWR报告

从上面的top sql部分可以看到执行时间最长的已经达到19019秒，还有几个运行时间也要执行几千秒，其中SQLID为d7bv3q1camq5x的SQL逻辑读和物理读都在几千万甚至上亿次。而每次也就返回200多行记录。

其中SQL语句为:
select /*+ rule */
a.stat_type,
a.his_item_code,
a.his_item_name,
a.item_code,
max((select count(*)
from mt_fee_fin aa
where a.hospital_id = aa.hospital_id
and a.serial_no = aa.serial_no
and a.item_code = aa.item_code)) as item_sn,
a.item_name,
a.medi_item_type,
a.price,
sum(a.dosage) as dosage,
a.model,
replace(a.standard, ' ', '') as standard,
sum(a.money) as money,
sum(nvl(d.audit_money, 0)) as audit_money,
d.hosp_reason_staff as hosp_reason_staff,
d.hosp_reason_date as hosp_reason_date,
d.hosp_reason_staffid as hosp_reason_staffid,
d.hosp_reason as hosp_reason,
d.center_resualt as center_resualt,
d.center_flag as center_flag,
d.audit_reason_id as audit_reason_id,
sum(nvl(b.all_cash, 0)) as all_cash,
(case
when a.medi_item_type = '0' then
(SELECT bo_flag
FROM bs_item
WHERE bs_item.item_code = a.item_code
AND ROWNUM <2)
else
(SELECT bo_flag
FROM bs_medi
WHERE bs_medi.medi_code = a.item_code
AND ROWNUM <2)
end) as bo_flag,
sum(nvl(b.part_cash, 0)) as part_cash,
decode(nvl(d.audit_reason_id, 0),
0,
d.audit_reason,
'%%' || to_char(d.audit_reason_id) || '%%') as audit_reason
from mt_fee_fin a,
pm_account_biz c,
pm_fee_audit d,
(select hospital_id,
serial_no,
policy_item_code,
serial_fee,
fee_batch,
SUM(decode(fund_id,
'999',
decode(b.label_flag, '101', real_pay, 0),
'003',
decode(label_flag, '101', real_pay, 0),
0)) AS all_cash,
SUM(decode(fund_id,
'999',
decode(b.label_flag, '102', real_pay, 0),
'003',
decode(label_flag, '102', real_pay, 0),
0)) AS part_cash
from mt_pay_record_fin b
where b.hospital_id = '4307210003'
and b.serial_no = '25735455'
and serial_fee <> 0
and valid_flag = '1'
group by hospital_id,
serial_no,
policy_item_code,
serial_fee,
fee_batch) b
where a.hospital_id = c.hospital_id
and a.serial_no = c.serial_no
and a.hospital_id = '4307210003'
and a.serial_no = '25735455'
and a.hospital_id = b.hospital_id(+)
and a.serial_fee = b.serial_fee(+)
and a.serial_no = b.serial_no(+)
and a.fee_batch = b.fee_batch(+)
and a.valid_flag = '1'
and c.valid_flag = '1'
and d.audit_staff_id(+) = 2103
and d.AUDIT_PHASE(+) = '1'
and d.serial_fee(+) <> 0
and a.serial_fee = d.serial_fee(+)
and d.account_id(+) = 16905170
and c.account_id = 16905170
group by a.stat_type,
a.item_name,
a.his_item_name,
a.price,
a.his_item_code,
a.item_code,
a.medi_item_type,
a.model,
a.standard,
d.hosp_reason,
d.center_resualt,
d.center_flag,
d.hosp_reason_staff,
d.hosp_reason_date,
d.hosp_reason_staffid,
d.audit_reason_id,
d.audit_reason
Order By a.stat_type, a.item_name, a.his_item_name