首页 > 代码库 > SQL优化记录

SQL优化记录

2024-08-16 16:13:07   219人阅读

　　分公司业务部门同事一直抱怨登录系统、查询数据等操作非常缓慢，问题反馈到开发同事，再到我这里的时候已经相当严重了，而且在日常对数据库进行监控的时候，抓取出来的问题SQL与开发给我的SQL是一致的，该SQL就是频繁导致数据库服务器CPU使用率飙升到90%以上的元凶，所以最近对该SQL进行了优化。

SQL文本如下：（最原始的SQL里有多重distinct，或者类似where 1=1这样的内容，为了节省篇幅，下面是精简后的SQL）

select count(*)
from (select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code,
null,
‘保单‘,
‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
nvl(p.c_insurance_code,
lcc.INSURANCE_POLICY_NO) as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time,
‘yyyy-mm-dd‘),
to_char(p.sign_time,
‘yyyy-mm-dd‘)),
to_char(ipt.effective_date,
‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date,
‘yyyy-mm-dd‘),
to_char(p.start_date,
‘yyyy-mm-dd‘)),
to_char(ipt.start_date,
‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date,
‘yyyy-mm-dd‘),
to_char(p.end_date,
‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE =
‘0001300049.00158‘
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like
‘%‘ || ‘旅游保险网‘ || ‘%‘
order by lcc.INSURANCE_POLICY_NO desc) t1, import_pay_confirm t2
where t2.c_bath_no = 1
and t2.c_import_by = ‘dx.xie‘
and (t1.allInsuranceCode = t2.c_apply_no or
t1.insuranceCode = t2.c_policy_no or
t1.endorsenentCode = t2.C_ENDORSEMENT_CODE or
t1.insuranceId = t2.C_INSURANCE_ID));

 


在这里我不想把该SQL的执行计划贴出来，因为我根本就没有保存^-^，而且，切入点并不是从原SQL的执行计划开始。

开始分析该SQL的逻辑：

表import_pay_confirm是从前台页面导入的表，有四个字段，在SQL的最后与t1进行关联。因此我采用union all将该语句改写了，这样就能帮助优化器进行查询改写，视图合并之类的。这是第一个改写的地方

第二个地方是t1中的选择列：
decode((select count(*)
from prem_payment_detail_target
where p.c_paid_id = C_PAID_ID),
0,
‘否‘,
‘是‘) as selletStatus,

改写后的该选择列的表达式为decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus，因为只是作一个判断，完全可以直接对prem_payment_detail_target表的字段c_paid_id进行判断，这样可以减少对该表的重复访问

改写后的SQL相当长，但是逻辑要简单很多，而且速度快很多，因此也证明了，SQL写的长不一定性能不好，效率不高，SQL短不代表逻辑清晰、性能好，下面是SQL语句与执行计划：

select count(*)
from (select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.allInsuranceCode = t2.c_apply_no
and t2.c_import_by = ‘dx.xie‘
union all
select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.insuranceCode = t2.c_policy_no
and t2.c_import_by = ‘dx.xie‘
and t2.c_apply_no is null
union all
select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.endorsenentCode = t2.C_ENDORSEMENT_CODE
and t2.c_import_by = ‘dx.xie‘
and t2.c_policy_no is null
and t2.c_apply_no is null
union all
select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = 0001300049.00158
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.insuranceId = t2.C_INSURANCE_ID
and t2.c_import_by = ‘dx.xie‘
and t2.c_policy_no is null
and t2.c_apply_no is null
and t2.C_ENDORSEMENT_CODE is null);

-------------------------------------
SQL_ID brut29q5yb2j5, child number 1
-------------------------------------

Plan hash value: 3456365982

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.79 | 142K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.79 | 142K| | | |
| 2 | VIEW | | 1 | 4 | 165 |00:00:00.79 | 142K| | | |
| 3 | UNION-ALL | | 1 | | 165 |00:00:00.79 | 142K| | | |
| 4 | HASH UNIQUE | | 1 | 1 | 165 |00:00:00.02 | 3274 | 698K| 698K| 636K (0)|
| 5 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.02 | 2942 | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 2445 | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 2441 | | | |
| 8 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 1954 | | | |
| 9 | NESTED LOOPS | | 1 | 1 | 165 |00:00:00.01 | 1622 | | | |
|* 10 | TABLE ACCESS FULL | IMPORT_PAY_CONFIRM | 1 | 181 | 164 |00:00:00.01 | 374 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | PAID_INFO_TARGET | 164 | 1 | 165 |00:00:00.01 | 1248 | | | |
|* 12 | INDEX RANGE SCAN | INDEX_PAID_5 | 164 | 1 | 990 |00:00:00.01 | 496 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 165 | 1 | 165 |00:00:00.01 | 332 | | | |
|* 14 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 165 | 1 | 165 |00:00:00.01 | 167 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 165 | 1 | 153 |00:00:00.01 | 487 | | | |
|* 16 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 165 | 1 | 153 |00:00:00.01 | 334 | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 165 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 18 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 165 | 1 | 1 |00:00:00.01 | 3 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 165 | 1 | 165 |00:00:00.01 | 497 | | | |
|* 20 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 165 | 1 | 165 |00:00:00.01 | 332 | | | |
| 21 | HASH UNIQUE | | 1 | 1 | 0 |00:00:00.59 | 102K| 687K| 687K| |
|* 22 | TABLE ACCESS BY INDEX ROWID | IMPORT_PAY_CONFIRM | 1 | 1 | 0 |00:00:00.59 | 102K| | | |
| 23 | NESTED LOOPS | | 1 | 1 | 11013 |00:00:00.58 | 102K| | | |
| 24 | NESTED LOOPS OUTER | | 1 | 1 | 11012 |00:00:00.53 | 90797 | | | |
| 25 | NESTED LOOPS OUTER | | 1 | 1 | 11012 |00:00:00.43 | 66037 | | | |
| 26 | NESTED LOOPS OUTER | | 1 | 1 | 11012 |00:00:00.39 | 66031 | | | |
| 27 | NESTED LOOPS OUTER | | 1 | 1 | 11012 |00:00:00.23 | 32825 | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | PAID_INFO_TARGET | 1 | 1 | 11012 |00:00:00.09 | 10800 | | | |
|* 29 | INDEX SKIP SCAN | INDEX_PAID_6 | 1 | 1 | 11012 |00:00:00.02 | 233 | | | |
| 30 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 11012 | 1 | 11011 |00:00:00.13 | 22025 | | | |
|* 31 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 11012 | 1 | 11011 |00:00:00.06 | 11014 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 11012 | 1 | 10996 |00:00:00.15 | 33206 | | | |
|* 33 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 11012 | 1 | 10996 |00:00:00.09 | 22210 | | | |
|* 34 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 11012 | 1 | 1 |00:00:00.03 | 6 | | | |
|* 35 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 11012 | 1 | 1 |00:00:00.01 | 5 | | | |
| 36 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 11012 | 1 | 179 |00:00:00.09 | 24760 | | | |
|* 37 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 11012 | 1 | 179 |00:00:00.07 | 24581 | | | |
|* 38 | INDEX RANGE SCAN | INDEX_NAME3 | 11012 | 2 | 0 |00:00:00.05 | 11259 | | | |
| 39 | HASH UNIQUE | | 1 | 1 | 0 |00:00:00.01 | 3 | 687K| 687K| |
| 40 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 41 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 42 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 43 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 44 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 45 | TABLE ACCESS BY INDEX ROWID | IMPORT_PAY_CONFIRM | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 46 | INDEX FULL SCAN | INDEX_NAME2 | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
|* 47 | TABLE ACCESS BY INDEX ROWID | PAID_INFO_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 48 | BITMAP CONVERSION TO ROWIDS | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 49 | BITMAP AND | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 50 | BITMAP CONVERSION FROM ROWIDS| | 0 | | 0 |00:00:00.01 | 0 | | | |
|* 51 | INDEX RANGE SCAN | INDEX_PAID_2 | 0 | 655 | 0 |00:00:00.01 | 0 | | | |
| 52 | BITMAP CONVERSION FROM ROWIDS| | 0 | | 0 |00:00:00.01 | 0 | | | |
|* 53 | INDEX RANGE SCAN | INDEX_PAID_4 | 0 | 655 | 0 |00:00:00.01 | 0 | | | |
| 54 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 55 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 56 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 57 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 58 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 59 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 60 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 61 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 62 | HASH UNIQUE | | 1 | 1 | 0 |00:00:00.18 | 37509 | 687K| 687K| |
| 63 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.18 | 37509 | | | |
| 64 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.18 | 37509 | | | |
| 65 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.18 | 37509 | | | |
| 66 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.18 | 37509 | | | |
| 67 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.18 | 37509 | | | |
|* 68 | TABLE ACCESS BY INDEX ROWID | PAID_INFO_TARGET | 1 | 1 | 11012 |00:00:00.10 | 15483 | | | |
|* 69 | INDEX RANGE SCAN | INDEX_PAID_4 | 1 | 17956 | 17480 |00:00:00.01 | 104 | | | |
|* 70 | TABLE ACCESS BY INDEX ROWID | IMPORT_PAY_CONFIRM | 11012 | 1 | 0 |00:00:00.06 | 22026 | | | |
|* 71 | INDEX RANGE SCAN | INDEX_NAME4 | 11012 | 1 | 0 |00:00:00.05 | 22026 | | | |
| 72 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 73 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 74 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 75 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 76 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 77 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 78 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 79 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

10 - filter(("T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_APPLY_NO" IS NOT NULL AND "T2"."C_BATH_NO"=1))
11 - filter(("P"."C_INSURANCE_COM_CODE"=‘0001300049.00158‘ AND "P"."C_DEPT_ID"=‘JT015200107‘ AND "P"."C_BRANCH_CODE"=‘JT0152001‘ AND
"P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND "P"."C_SYSTEM_NAME" IS NOT NULL))
12 - access("P"."C_APPLY_CODE"="T2"."C_APPLY_NO")
14 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
16 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")
17 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
18 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
20 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")
22 - filter(("T2"."C_APPLY_NO" IS NULL AND "T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_BATH_NO"=1))
29 - access("P"."C_BRANCH_CODE"=‘JT0152001‘ AND "P"."C_DEPT_ID"=‘JT015200107‘ AND "P"."C_INSURANCE_COM_CODE"=‘0001300049.00158‘)
filter(("P"."C_INSURANCE_COM_CODE"=‘0001300049.00158‘ AND "P"."C_DEPT_ID"=‘JT015200107‘ AND "P"."C_BRANCH_CODE"=‘JT0152001‘ AND
"P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND "P"."C_SYSTEM_NAME" IS NOT NULL))
31 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
33 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")
34 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
35 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
37 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")
38 - access("T2"."C_POLICY_NO"=NVL("P"."C_INSURANCE_CODE","LCC"."INSURANCE_POLICY_NO"))
45 - filter(("T2"."C_APPLY_NO" IS NULL AND "T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_POLICY_NO" IS NULL AND "T2"."C_BATH_NO"=1))
46 - filter("T2"."C_ENDORSEMENT_CODE" IS NOT NULL)
47 - filter(("P"."C_INSURANCE_COM_CODE"=‘0001300049.00158‘ AND "P"."C_BRANCH_CODE"=‘JT0152001‘ AND "P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND
"P"."C_SYSTEM_NAME" IS NOT NULL))
51 - access("P"."C_ENDORSEMENT_CODE"="T2"."C_ENDORSEMENT_CODE")
filter("P"."C_ENDORSEMENT_CODE" IS NOT NULL)
53 - access("P"."C_DEPT_ID"=‘JT015200107‘)
55 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
57 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")
58 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
59 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
61 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")
68 - filter(("P"."C_BRANCH_CODE"=‘JT0152001‘ AND "P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND TO_NUMBER("P"."C_INSURANCE_COM_CODE")=1300049.00158 AND
"P"."C_SYSTEM_NAME" IS NOT NULL))
69 - access("P"."C_DEPT_ID"=‘JT015200107‘)
70 - filter(("T2"."C_APPLY_NO" IS NULL AND "T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_POLICY_NO" IS NULL AND "T2"."C_ENDORSEMENT_CODE" IS NULL AND
"T2"."C_BATH_NO"=1))
71 - access("P"."C_INSURANCE_ID"="T2"."C_INSURANCE_ID")
filter("T2"."C_INSURANCE_ID" IS NOT NULL)
73 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
75 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")
76 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
77 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
79 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")

144 rows selected.

 

经过近三天的分析与改写，该SQL第二次执行消耗时间从五分钟降至0.5秒左右，实际返回100多行数据，其消耗的buffer高达142K，假设并发访问很高，必然导致大量的latch：Cache buffer Chain，而且由于服务器物理内存较少，因此如果需要访问的block被踢出SGA

那么第一次访问将会非常缓慢，同时伴随着大量的latch：Cache buffer Chain，随后出现的问题，证明了我的观点，这里我不打算把相关等待事件贴出来，因为我根本就没有保存，我们并不需要去研究这个等待事件的内部机制，只要知道其产生的原因是什么。优化后的第二天早上对数据库进行巡检发现RAC节点1的CPU使用率高达90%，证实了我的猜测，中途找SQL的过程就不细说了，下面继续优化该SQL。

经过对上述执行计划进行分析。发现第二和第四个环节消耗的buffer较大，首先对第二段进行优化与分析。SQL语句进行到这个阶段，优化器同样对视图T1进行了视图合并，通过第38步可以看到：

access("T2"."C_POLICY_NO"=NVL("P"."C_INSURANCE_CODE","LCC"."INSURANCE_POLICY_NO"))，NVL函数的使用抑制了对表paid_info_target和表lccont_target上索引的访问。因此我对这一段进行了改写，同时，对第四段进行优化的方法是添加了相关索引，这里就不进行叙述了，改写后的SQL和执行计划如下：

select count(*)
from (select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.allInsuranceCode = t2.c_apply_no
and t2.c_import_by = ‘dx.xie‘
union all
select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
p.c_insurance_code as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
and p.c_insurance_code is not null
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.insuranceCode = t2.c_policy_no
and t2.c_import_by = ‘dx.xie‘
and t2.c_apply_no is null
union all
select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
lcc.INSURANCE_POLICY_NO as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
and p.c_insurance_code is null
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.insuranceCode = t2.c_policy_no
and t2.c_import_by = ‘dx.xie‘
and t2.c_apply_no is null
union all
select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.endorsenentCode = t2.C_ENDORSEMENT_CODE
and t2.c_import_by = ‘dx.xie‘
and t2.c_policy_no is null
and t2.c_apply_no is null
union all
select distinct t1.*
from (select p.c_paid_id as id,
p.c_branch_name as organName,
p.c_dept_name as daptName,
d.project_name as itemName,
p.c_Insurance_Com_Name as pryerName,
nvl(p.n_Current_Premium, 0) as commission,
decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType,
p.c_Apply_Code as allInsuranceCode,
nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode,
p.c_Endorsement_Code as endorsenentCode,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘),
to_char(p.sign_time, ‘yyyy-mm-dd‘)),
to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘),
to_char(p.start_date, ‘yyyy-mm-dd‘)),
to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate,
decode(p.c_endorsement_code,
null,
nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘),
to_char(p.end_date, ‘yyyy-mm-dd‘)),
to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate,
to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate,
decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus,
se.C_PAYMENT_CODE as settlementCode,
p.c_is_paid as isSettled,
p.C_PERIOD as periodno,
p.C_INSURANCE_ID as insuranceId,
nvl(p.c_insurant_name, lcc.customer_name) as insuredName
from paid_info_target p,
lccont_target lcc,
d_projects d,
prem_payment_detail_target se,
insurance_policy_target ipt
where p.c_unite_policy = lcc.contno(+)
and p.c_project_code = d.project_code(+)
and p.C_PAID_ID = se.C_PAID_ID(+)
and (p.c_unite_policy = ipt.contno(+) and
p.c_endorsement_code =
ipt.mli_insurance_endorsement_no(+))
and p.C_INSURANCE_COM_CODE = 0001300049.00158
and p.c_dept_id = ‘JT015200107‘
and p.c_branch_code = ‘JT0152001‘
and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘
order by lcc.INSURANCE_POLICY_NO desc) t1,
import_pay_confirm t2
where t2.c_bath_no = 1
and t1.insuranceId = t2.C_INSURANCE_ID
and t2.c_import_by = ‘dx.xie‘
and t2.c_policy_no is null
and t2.c_apply_no is null
and t2.C_ENDORSEMENT_CODE is null);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8stbf7dskgqcy, child number 0
-------------------------------------


Plan hash value: 3629687165

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 3697 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 3697 | | | |
| 2 | VIEW | | 1 | 5 | 165 |00:00:00.03 | 3697 | | | |
| 3 | UNION-ALL | | 1 | | 165 |00:00:00.03 | 3697 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 165 |00:00:00.02 | 2942 | 698K| 698K| 1207K (0)|
| 5 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.02 | 2942 | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 2445 | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 2441 | | | |
| 8 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 1954 | | | |
| 9 | NESTED LOOPS | | 1 | 1 | 165 |00:00:00.01 | 1622 | | | |
|* 10 | TABLE ACCESS FULL | IMPORT_PAY_CONFIRM | 1 | 145 | 164 |00:00:00.01 | 374 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| PAID_INFO_TARGET | 164 | 1 | 165 |00:00:00.01 | 1248 | | | |
|* 12 | INDEX RANGE SCAN | INDEX_PAID_5 | 164 | 1 | 990 |00:00:00.01 | 496 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 165 | 1 | 165 |00:00:00.01 | 332 | | | |
|* 14 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 165 | 1 | 165 |00:00:00.01 | 167 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 165 | 1 | 153 |00:00:00.01 | 487 | | | |
|* 16 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 165 | 1 | 153 |00:00:00.01 | 334 | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 165 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 18 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 165 | 1 | 1 |00:00:00.01 | 3 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 165 | 1 | 165 |00:00:00.01 | 497 | | | |
|* 20 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 165 | 1 | 165 |00:00:00.01 | 332 | | | |
| 21 | HASH UNIQUE | | 1 | 1 | 0 |00:00:00.01 | 374 | 687K| 687K| |
| 22 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
| 23 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
| 24 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
| 25 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
| 26 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
|* 27 | TABLE ACCESS FULL | IMPORT_PAY_CONFIRM | 1 | 7 | 0 |00:00:00.01 | 374 | | | |
|* 28 | TABLE ACCESS BY INDEX ROWID| PAID_INFO_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 29 | INDEX RANGE SCAN | INDEX_POLICYNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 30 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 31 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 33 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 34 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 35 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 36 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 37 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 38 | HASH UNIQUE | | 1 | 1 | 0 |00:00:00.01 | 374 | 687K| 687K| |
| 39 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
| 40 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
| 41 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
| 42 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 374 | | | |
| 43 | NESTED LOOPS | | 1 | 8 | 0 |00:00:00.01 | 374 | | | |
|* 44 | TABLE ACCESS FULL | IMPORT_PAY_CONFIRM | 1 | 7 | 0 |00:00:00.01 | 374 | | | |
| 45 | TABLE ACCESS BY INDEX ROWID| LCCONT_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 46 | INDEX RANGE SCAN | INDEX_LCCONT_INSURANCE_PNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 47 | TABLE ACCESS BY INDEX ROWID | PAID_INFO_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 48 | INDEX RANGE SCAN | INDEX_PAID_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 49 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 50 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 51 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 52 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 53 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 54 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 55 | HASH UNIQUE | | 1 | 1 | 0 |00:00:00.01 | 3 | 687K| 687K| |
| 56 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 57 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 58 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 59 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 60 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 61 | TABLE ACCESS BY INDEX ROWID| IMPORT_PAY_CONFIRM | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 62 | INDEX FULL SCAN | INDEX_NAME2 | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
|* 63 | TABLE ACCESS BY INDEX ROWID| PAID_INFO_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 64 | INDEX RANGE SCAN | INDEX_PAID_2 | 0 | 655 | 0 |00:00:00.01 | 0 | | | |
| 65 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 66 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 67 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 68 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 69 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 70 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 71 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 72 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 73 | HASH UNIQUE | | 1 | 1 | 0 |00:00:00.01 | 4 | 687K| 687K| |
| 74 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
| 75 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
| 76 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
| 77 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
| 78 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 79 | TABLE ACCESS BY INDEX ROWID| IMPORT_PAY_CONFIRM | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 80 | INDEX FULL SCAN | INDEX_NAME4 | 1 | 60 | 60 |00:00:00.01 | 3 | | | |
|* 81 | TABLE ACCESS BY INDEX ROWID| PAID_INFO_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 82 | INDEX RANGE SCAN | INDEX_PAID_4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 83 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 84 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 85 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 86 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 87 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 88 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 89 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 90 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

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

10 - filter(("T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_APPLY_NO" IS NOT NULL AND "T2"."C_BATH_NO"=1))
11 - filter(("P"."C_INSURANCE_COM_CODE"=‘0001300049.00158‘ AND "P"."C_DEPT_ID"=‘JT015200107‘ AND "P"."C_BRANCH_CODE"=‘JT0152001‘ AND
"P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND "P"."C_SYSTEM_NAME" IS NOT NULL))
12 - access("P"."C_APPLY_CODE"="T2"."C_APPLY_NO")
14 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
16 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")
17 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
18 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
20 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")
27 - filter(("T2"."C_APPLY_NO" IS NULL AND "T2"."C_POLICY_NO" IS NOT NULL AND "T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_BATH_NO"=1))
28 - filter(("P"."C_INSURANCE_COM_CODE"=‘0001300049.00158‘ AND "P"."C_DEPT_ID"=‘JT015200107‘ AND "P"."C_BRANCH_CODE"=‘JT0152001‘ AND
"P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND "P"."C_SYSTEM_NAME" IS NOT NULL))
29 - access("P"."C_INSURANCE_CODE"="T2"."C_POLICY_NO")
filter("P"."C_INSURANCE_CODE" IS NOT NULL)
31 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
32 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
33 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
35 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")
37 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")
44 - filter(("T2"."C_APPLY_NO" IS NULL AND "T2"."C_POLICY_NO" IS NOT NULL AND "T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_BATH_NO"=1))
46 - access("LCC"."INSURANCE_POLICY_NO"="T2"."C_POLICY_NO")
47 - filter(("P"."C_INSURANCE_CODE" IS NULL AND "P"."C_INSURANCE_COM_CODE"=‘0001300049.00158‘ AND "P"."C_DEPT_ID"=‘JT015200107‘ AND
"P"."C_BRANCH_CODE"=‘JT0152001‘ AND "P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND "P"."C_SYSTEM_NAME" IS NOT NULL))
48 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")
50 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
51 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
52 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
54 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")
61 - filter(("T2"."C_APPLY_NO" IS NULL AND "T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_POLICY_NO" IS NULL AND "T2"."C_BATH_NO"=1))
62 - filter("T2"."C_ENDORSEMENT_CODE" IS NOT NULL)
63 - filter(("P"."C_INSURANCE_COM_CODE"=‘0001300049.00158‘ AND "P"."C_DEPT_ID"=‘JT015200107‘ AND "P"."C_BRANCH_CODE"=‘JT0152001‘ AND
"P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND "P"."C_SYSTEM_NAME" IS NOT NULL))
64 - access("P"."C_ENDORSEMENT_CODE"="T2"."C_ENDORSEMENT_CODE")
filter("P"."C_ENDORSEMENT_CODE" IS NOT NULL)
66 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
68 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")
69 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
70 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
72 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")
79 - filter(("T2"."C_APPLY_NO" IS NULL AND "T2"."C_IMPORT_BY"=‘dx.xie‘ AND "T2"."C_POLICY_NO" IS NULL AND "T2"."C_ENDORSEMENT_CODE" IS NULL
AND "T2"."C_BATH_NO"=1))
80 - filter("T2"."C_INSURANCE_ID" IS NOT NULL)
81 - filter(("P"."C_DEPT_ID"=‘JT015200107‘ AND "P"."C_BRANCH_CODE"=‘JT0152001‘ AND "P"."C_SYSTEM_NAME" LIKE ‘%旅游保险网%‘ AND
TO_NUMBER("P"."C_INSURANCE_COM_CODE")=1300049.00158 AND "P"."C_SYSTEM_NAME" IS NOT NULL))
82 - access("P"."C_INSURANCE_ID"="T2"."C_INSURANCE_ID")
84 - access("P"."C_PROJECT_CODE"="D"."PROJECT_CODE")
85 - filter("P"."C_UNITE_POLICY"="IPT"."CONTNO")
86 - access("P"."C_ENDORSEMENT_CODE"="IPT"."MLI_INSURANCE_ENDORSEMENT_NO")
88 - access("P"."C_PAID_ID"="SE"."C_PAID_ID")
90 - access("P"."C_UNITE_POLICY"="LCC"."CONTNO")

163 rows selected.

 

对buffer的消耗从之前的142K降到现在的3697，下降了97%多。我没有对优化的逻辑进行详细描述，虽然最后处理不太复杂，但是分析问题的过程事实上相当繁琐与复杂，曾经一度陷入中断，但是改写后的SQL的逻辑其实很简单，执行时间大概在0.1秒左右。

这段大的SQL其实还有优化空间，但是手头上的事情太多了，我准备继续对其进行优化，但不是现在，我相信这也是很多同行们的共同处境。

SQL优化记录


联系
我们