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

某医保系统在业务高峰期间相关模块使用不了查询不出结果，生成业务高峰期间上午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

在程序的SQL语句使用了/*+ rule */提示，就会让优化器使用RBO，不使用CBO。 这样使得优化器少了许多选择CBO高效执行计划的机会。当我们去掉/*+ rule */，使用CBO时其执行结果如下：
SQL> select
  2  a.stat_type,
  3  a.his_item_code,
  4  a.his_item_name,
  5  a.item_code,
  6  max((select count(*)
  7        from mt_fee_fin aa
  8        where a.hospital_id = aa.hospital_id
  9          and a.serial_no = aa.serial_no
 10          and a.item_code = aa.item_code)) as item_sn,
 11  a.item_name,
 12  a.medi_item_type,
 13  a.price,
 14  sum(a.dosage) as dosage,
 15  a.model,
 16  replace(a.standard, '  ', '') as standard,
 17  sum(a.money) as money,
 18  sum(nvl(d.audit_money, 0)) as audit_money,
 19  d.hosp_reason_staff as hosp_reason_staff,
 20  d.hosp_reason_date as hosp_reason_date,
 21  d.hosp_reason_staffid as hosp_reason_staffid,
 22  d.hosp_reason as hosp_reason,
 23  d.center_resualt as center_resualt,
 24  d.center_flag as center_flag,
 25  d.audit_reason_id as audit_reason_id,
 26  sum(nvl(b.all_cash, 0)) as all_cash,
 27  (case
 28    when a.medi_item_type = '0' then
 29      (SELECT bo_flag
 30        FROM bs_item
 31        WHERE bs_item.item_code = a.item_code
 32          AND ROWNUM < 2)
 33    else
 34      (SELECT bo_flag
 35        FROM bs_medi
 36        WHERE bs_medi.medi_code = a.item_code
 37          AND ROWNUM < 2)
 38  end) as bo_flag,
 39  sum(nvl(b.part_cash, 0)) as part_cash,
 40  decode(nvl(d.audit_reason_id, 0),
 41          0,
 42          d.audit_reason,
 43          '%%' || to_char(d.audit_reason_id) || '%%') as audit_reason
 44    from mt_fee_fin a,
 45        pm_account_biz c,
 46        pm_fee_audit d,
 47        (select hospital_id,
 48                serial_no,
 49                policy_item_code,
 50                serial_fee,
 51                fee_batch,
 52                SUM(decode(fund_id,
 53                            '999',
 54                            decode(b.label_flag, '101', real_pay, 0),
 55                            '003',
 56                            decode(label_flag, '101', real_pay, 0),
 57                            0)) AS all_cash,
 58                SUM(decode(fund_id,
 59                            '999',
 60                            decode(b.label_flag, '102', real_pay, 0),
 61                            '003',
 62                            decode(label_flag, '102', real_pay, 0),
 63                            0)) AS part_cash
 64            from mt_pay_record_fin b
 65          where b.hospital_id = '4307210003'
 66            and b.serial_no = '25735455'
 67            and serial_fee <> 0
 68            and valid_flag = '1'
 69          group by hospital_id,
 70                    serial_no,
 71                    policy_item_code,
 72                    serial_fee,
 73                    fee_batch) b
 74  where a.hospital_id = c.hospital_id
 75    and a.serial_no = c.serial_no
 76    and a.hospital_id = '4307210003'
 77    and a.serial_no = '25735455'
 78    and a.hospital_id = b.hospital_id(+)
 79    and a.serial_fee = b.serial_fee(+)
 80    and a.serial_no = b.serial_no(+)
 81    and a.fee_batch = b.fee_batch(+)
 82    and a.valid_flag = '1'
 83    and c.valid_flag = '1'
 84    and d.audit_staff_id(+) = 2103
 85    and d.AUDIT_PHASE(+) = '1'
 86    and d.serial_fee(+) <> 0
 87    and a.serial_fee = d.serial_fee(+)
 88    and d.account_id(+) = 16905170
 89    and c.account_id = 16905170
 90  group by a.stat_type,
 91            a.item_name,
 92            a.his_item_name,
 93            a.price,
 94            a.his_item_code,
 95            a.item_code,
 96            a.medi_item_type,
 97            a.model,
 98            a.standard,
 99            d.hosp_reason,
100            d.center_resualt,
101            d.center_flag,
102            d.hosp_reason_staff,
103            d.hosp_reason_date,
104            d.hosp_reason_staffid,
105            d.audit_reason_id,
106            d.audit_reason
107  Order By a.stat_type, a.item_name, a.his_item_name
108  ;
....省略...

277 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1363767461

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |                        |    1 |  338 |    7  (43)| 00:00:01 |
|  1 |  SORT AGGREGATE                |                        |    1 |    36 |            |          |
|*  2 |  TABLE ACCESS BY INDEX ROWID  | MT_FEE_FIN              |    1 |    36 |    1  (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN            | PK_MT_FEE_FIN          |    1 |      |    1  (0)| 00:00:01 |
|*  4 |  COUNT STOPKEY                  |                        |      |      |            |          |
|  5 |  TABLE ACCESS BY INDEX ROWID  | BS_ITEM                |    12 |  192 |    3  (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | IDX_ITEM_CODE          |    12 |      |    1  (0)| 00:00:01 |
|*  7 |  COUNT STOPKEY                |                        |      |      |            |          |
|  8 |    TABLE ACCESS BY INDEX ROWID  | BS_MEDI                |    12 |  216 |    3  (0)| 00:00:01 |
|*  9 |    INDEX RANGE SCAN            | IDX_MEDI_CODE          |    12 |      |    1  (0)| 00:00:01 |
|  10 |  SORT GROUP BY                  |                        |    1 |  338 |    7  (43)| 00:00:01 |
|* 11 |  HASH JOIN OUTER              |                        |    1 |  338 |    6  (34)| 00:00:01 |
|  12 |    NESTED LOOPS OUTER          |                        |    1 |  283 |    3  (0)| 00:00:01 |
|  13 |    NESTED LOOPS                |                        |    1 |  139 |    2  (0)| 00:00:01 |
|* 14 |      TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ          |    1 |    29 |    1  (0)| 00:00:01 |
|* 15 |      INDEX UNIQUE SCAN        | PK_PM_ACCOUNT_BIZ      |    1 |      |    1  (0)| 00:00:01 |
|* 16 |      TABLE ACCESS BY INDEX ROWID| MT_FEE_FIN              |    1 |  110 |    1  (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN          | PK_MT_FEE_FIN          |    1 |      |    1  (0)| 00:00:01 |
|  18 |    TABLE ACCESS BY INDEX ROWID | PM_FEE_AUDIT            |    1 |  144 |    1  (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN          | PK_PM_FEE_AUDIT        |    1 |      |    1  (0)| 00:00:01 |
|  20 |    VIEW                        |                        |    1 |    55 |    2  (50)| 00:00:01 |
|  21 |    HASH GROUP BY              |                        |    1 |    57 |    2  (50)| 00:00:01 |
|* 22 |      TABLE ACCESS BY INDEX ROWID| MT_PAY_RECORD_FIN      |    1 |    57 |    1  (0)| 00:00:01 |
|* 23 |      INDEX RANGE SCAN          | IDX_MT_PAY_RECORD_FIN_2 |    1 |      |    1  (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("AA"."ITEM_CODE"=:B1)
  3 - access("AA"."HOSPITAL_ID"=:B1 AND "AA"."SERIAL_NO"=:B2)
  4 - filter(ROWNUM<2)
  6 - access("BS_ITEM"."ITEM_CODE"=:B1)
  7 - filter(ROWNUM<2)
  9 - access("BS_MEDI"."MEDI_CODE"=:B1)
  11 - access("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"(+))
  14 - filter("C"."SERIAL_NO"='25735455' AND "C"."HOSPITAL_ID"='4307210003' AND
              "C"."VALID_FLAG"='1')
  15 - access("C"."ACCOUNT_ID"=16905170)
  16 - filter("A"."VALID_FLAG"='1')
  17 - access("A"."HOSPITAL_ID"='4307210003' AND "A"."SERIAL_NO"='25735455')
  19 - access("D"."ACCOUNT_ID"(+)=16905170 AND "D"."AUDIT_PHASE"(+)='1' AND
              "A"."SERIAL_FEE"="D"."SERIAL_FEE"(+))
      filter(TO_NUMBER("D"."AUDIT_STAFF_ID"(+))=2103 AND "D"."SERIAL_FEE"(+)<>0 AND
              "D"."AUDIT_PHASE"(+)='1' AND "A"."SERIAL_FEE"="D"."SERIAL_FEE"(+))
  22 - filter("SERIAL_FEE"<>0 AND "VALID_FLAG"='1')
  23 - access("B"."HOSPITAL_ID"='4307210003' AND "B"."SERIAL_NO"='25735455')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      1247  consistent gets
        12  physical reads
          0  redo size
      9160  bytes sent via SQL*Net to client
      2220  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        227  rows processed

其中逻辑读为1247，物理读12,比起几千万上亿次，提高了上万倍。

后面经检查发现在这套系统中,有大理SQL使用了/*+ rule */ Hint，最好的处理方法是修改SQL代码，这里为了应急我使用了_optimizer_ignore_hints参数让优化器忽略所有的hint。