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

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

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

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

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

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

其中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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/b3405948fb1ee73b5173d625b2f2bd95.html