乱用Oracle Hint造成性能问题案例二

后面经检查发现在这套系统中,有大理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   else
    (SELECT bo_flag
      FROM bs_medi
      WHERE bs_medi.medi_code = a.item_code
        AND ROWNUM  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