优化前：
具体SQL如下，该SQL执行需要1分钟的时间，对该SQL进行优化。
selecttpc.policy_id,
tcm.policy_code,
tpf.organ_id,
to_char(tpf.insert_time, 'YYYY-MM-DD')As insert_time,
tpc.change_id,
d.policy_code,
e.company_name,
f.real_name,
tpf.fee_type,
sum(tpf.pay_balance) aspay_balance,
c.actual_type,
tpc.notice_code,
d.policy_type,
g.mode_name as pay_mode
from t_policy_change tpc,
t_contract_master tcm,
t_policy_fee tpf,
t_fee_type c,
t_contract_master d,
t_company_customer e,
t_customer f,
t_pay_mode g
where tpc.change_id = tpf.change_id
and tpf.policy_id = d.policy_id
and tcm.policy_id = tpc.policy_id
and tpf.receiv_status = 1
and tpf.fee_status = 1
and tpf.payment_id is null
and tpf.fee_type = c.type_id
and tpf.pay_mode = g.mode_id
and d.company_id = e.company_id(+)
and d.applicant_id = f.customer_id(+)
and tpf.organ_id in
(select
organ_id
fromt_company_organ
start with organ_id ='101'
connect by prior organ_id =parent_id)
group by tpc.policy_id,
tpc.change_id,
tpf.fee_type,
to_char(tpf.insert_time,'YYYY-MM-DD'),
c.actual_type,
d.policy_code,
g.mode_name,
e.company_name,
f.real_name,
tpc.notice_code,
d.policy_type,
tpf.organ_id,
tcm.policy_code
order by change_id, fee_type
执行计划信息
SQL> select* from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
PredicateInformation (identified by operation id):
---------------------------------------------------
2 -access("TPF"."FEE_TYPE"="C"."TYPE_ID")
5 -access("TCM"."POLICY_ID"="TPC"."POLICY_ID")
7 -access("TPF"."ORGAN_ID"="VW_NSO_1"."$nso_col_1")
8 -access("D"."APPLICANT_ID"="F"."CUSTOMER_ID"(+))
9 -access("TPF"."POLICY_ID"="D"."POLICY_ID")
10 -access("TPF"."PAY_MODE"="G"."MODE_ID")
12 - filter("TPF"."CHANGE_ID" IS NOT NULLAND TO_NUMBER("TPF"."RECEIV_STATUS")=1
AND"TPF"."FEE_STATUS"=1 AND
"TPF"."PAYMENT_ID"IS NULL)
15 -access("indexjoin_alias_012".ROWID="indexjoin_alias_011".ROWID)
19 -filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')
21 -access("T_COMPANY_ORGAN"."ORGAN_ID"='101')
26 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)
28 -access("TPC"."CHANGE_ID"="TPF"."CHANGE_ID")
31 -access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))
55 rows selected
Statistics
----------------------------------------------------------
21 recursivecalls
0 dbblock gets
125082 consistentgets
21149 physicalreads
0 redosize
2448 bytes sent viaSQL*Net to client
656 bytes receivedvia SQL*Net from client
2 SQL*Netroundtrips to/from client
4 sorts(memory)
0 sorts(disk)
11 rowsprocessed
优化思路：
第一步：排除大表中的全表扫描情况
从执行计划中，我们可以看出，12行 T_POLICY_FEE该表有40万行记录，走了全表扫描，查看ID为12的过滤信息，发现TO_NUMBER("TPF"."RECEIV_STATUS")=1，典型的开发人员书写SQL不请注意细节导致无法走索引
第二步：过滤条件中存在in子查询
(select
organ_id
fromt_company_organ
start with organ_id ='101'
connect by prior organ_id =parent_id)
从执行计划上看，CBO对这儿子查询进行了unnest，因为通常情况下CBO认为子查询被unnest之后性能好。filter，这时我们需要确定子查询返回多少行，经过确认，该子查询返回只返回1行，对于子查询，如果它返回数据很少(这里返回1行)，那么可以让它走filter, 而且filter基本上是在SQL最后去阶段执行，这样t_policy_fee就可以走索引了，所以我给这个子查询加了个HINT，禁止子查询扩展。
优化后：
selecttpc.policy_id,
tcm.policy_code,
tpf.organ_id,
to_char(tpf.insert_time,'YYYY-MM-DD') As insert_time,
tpc.change_id,
d.policy_code,
e.company_name,
f.real_name,
tpf.fee_type,
sum(tpf.pay_balance) aspay_balance,
c.actual_type,
tpc.notice_code,
d.policy_type,
g.mode_name as pay_mode
from t_policy_change tpc,
t_contract_master tcm,
t_policy_fee tpf,
t_fee_type c,
t_contract_master d,
t_company_customer e,
t_customer f,
t_pay_mode g
where tpc.change_id = tpf.change_id
and tpf.policy_id = d.policy_id
and tcm.policy_id = tpc.policy_id
and tpf.receiv_status = '1' ---这里原来没引号，不添加上就没法用索引
and tpf.fee_status = 1
and tpf.payment_id is null
and tpf.fee_type = c.type_id
and tpf.pay_mode = g.mode_id
and d.company_id = e.company_id(+)
and d.applicant_id = f.customer_id(+)
and tpf.organ_id in
(select + no_unnest/ ---此处的HINT后加的,注意补全
organ_id
fromt_company_organ
start with organ_id ='101'
connect by prior organ_id =parent_id)
group by tpc.policy_id,
tpc.change_id,
tpf.fee_type,
to_char(tpf.insert_time,'YYYY-MM-DD'),
c.actual_type,
d.policy_code,
g.mode_name,
e.company_name,
f.real_name,
tpc.notice_code,
d.policy_type,
tpf.organ_id,
tcm.policy_code
order by change_id, fee_type
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "T_COMPANY_ORGAN""T_COMPANY_ORGAN" WHERE
"T_COMPANY_ORGAN"."PARENT_ID"=NULLAND ("T_COMPANY_ORGAN"."ORGAN_ID"=:B1)))
8 -access("SYS_ALIAS_1"."FEE_TYPE"="C"."TYPE_ID")
10 -access("SYS_ALIAS_1"."PAY_MODE"="G"."MODE_ID")
12 - filter("SYS_ALIAS_1"."CHANGE_ID" IS NOTNULL AND "SYS_ALIAS_1"."FEE_STATUS"=1
AND"SYS_ALIAS_1"."PAYMENT_ID" IS NULL)
13 -access("SYS_ALIAS_1"."RECEIV_STATUS"='1')
15 -access("SYS_ALIAS_1"."POLICY_ID"="D"."POLICY_ID")
17 -access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))
19 -access("D"."APPLICANT_ID"="F"."CUSTOMER_ID"(+))
21 -access("TPC"."CHANGE_ID"="SYS_ALIAS_1"."CHANGE_ID")
23 -access("TCM"."POLICY_ID"="TPC"."POLICY_ID")
24 -filter("T_COMPANY_ORGAN"."ORGAN_ID"=:B1)
25 -filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')
27 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')
32 -access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)
58 rows selected.
Statistics
----------------------------------------------------------
0 recursivecalls
0 dbblock gets
2817 consistentgets
0 physicalreads
0 redosize
2268 bytes sent viaSQL*Net to client
656 bytes receivedvia SQL*Net from client
2 SQL*Netroundtrips to/from client
40 sorts(memory)
0 sorts(disk)
9 rowsprocessed
最终这个SQL能在1秒以内跑完，逻辑读下降到2817。
扫描二维码关注我的微学堂
搜索刘老师微信号：Rman-2014，备注“Oracle学习与咨询”，即可添加好友；或者扫描下面二维码，关注我的“微学堂”公众号，了解最新OCP认证动态、题库及答案解析、培训机构及讲师介绍、课堂授课内容等。每天还有一篇技术文章发布哦!
