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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了乱用OracleHint造成性能问题案例二，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3668字，纯文字阅读大概需要6分钟。

内容图文

后面经检查发现在这套系统中,有大理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

内容总结

以上是互联网集市为您收集整理的乱用OracleHint造成性能问题案例二全部内容，希望文章能够帮你解决乱用OracleHint造成性能问题案例二所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---