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