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

某医保系统在业务高峰期间相关模块使用不了查询不出结果,生成业务高峰期间上午10点到11点期间的AWR报告
1

2

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

4

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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.