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