oracle加入并发hint卡住,乱用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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网