同事说，某某报表跑的很慢，让我调查一下
优化前：该报表整体需要跑4小时以上。
sql代码如下
SELECT /*省略多数查询字段*/
REP_FUN_REFCODEVALUE /*自定义函数*/
(P.POLICYTYPE, '3') AS POLICYTYPE,
REP_FUN_REFCODEVALUE /*自定义函数*/
(P.POLICYSTATUS, '3') AS POLICY_STATUS,
REP_FUN_BP_EXCHANGERATE_BOM /*自定义函数*/
(NVL(T.POLICYCURRENCYCODE, '$$100001000001'), FLAG) * T.AMOUNT_BP AS RMB_AMOUNT_BP,
REP_FUN_BP_EXCHANGERATE_BOM /*自定义函数*/
(NVL(T.POLICYCURRENCYCODE, '$$100001000001'), FLAG) * T.RI_PREM_BP AS RMB_RI_PREM_BP,
REP_FUN_BP_EXCHANGERATE_BOM /*自定义函数*/
(NVL(T3.UWCURRENCY, '$$100001000001'), '201706') * T3.SUMINSURED AS RMB_GROSS_TSI,
REP_FUN_POLICY_COMBINESTRING /*自定义函数*/
(P.POLICYNO, P.ENDORSEMENTNO, 'CODE') AS CBI_CODE,
REP_FUN_POLICY_COMBINESTRING /*自定义函数*/
(P.POLICYNO, P.ENDORSEMENTNO, 'NAME') AS CBI_NAME,
REP_FUN_POLICY_COMBINESTRING /*自定义函数*/
(P.POLICYNO, P.ENDORSEMENTNO, 'EQCODE') AS EQ_CODE,
REP_FUN_POLICY_COMBINESTRING(P.POLICYNO, P.ENDORSEMENTNO, 'EQNAME') AS EQ_NAME,
REP_FUN_BP_EXCHANGERATE_BOM /*自定义函数*/
(NVL(T.POLICYCURRENCYCODE, '$$100001000001'), FLAG) * T.AMOUNTVAT_BP,
REP_FUN_BP_EXCHANGERATE_BOM /*自定义函数*/
(NVL(T.POLICYCURRENCYCODE, '$$100001000001'), FLAG) * T.AMOUNTNET_BP,
REP_FUN_BP_EXCHANGERATE_BOM /*自定义函数*/
(NVL(T.POLICYCURRENCYCODE, '$$100001000001'), FLAG) * T.RI_PREMVAT_BP,
REP_FUN_BP_EXCHANGERATE_BOM /*自定义函数*/
(NVL(T.POLICYCURRENCYCODE, '$$100001000001'), FLAG) * T.RI_PREMNET_BP,
REP_RI_COMMISSIONVAT /*自定义函数*/(T.RIBILLNO),
REP_RI_COMMISSION /*自定义函数*/(T.RIBILLNO)
FROM BIZ_BP_CHECK_BP_BASED_CEDOUT T
LEFT JOIN (SELECT T.RIBILLNO,
T.OPTTYPE,
T.CONTRACTTYPE,
T.QSTYPE,
DECODE(T.OPTTYPE || '-' || T.CONTRACTTYPE || '-' ||
T.QSTYPE,
'1-2-Q',
'分入-合约-成数',
'1-2-C',
'分入-合约-超赔',
'1-2-S',
'分入-合约-溢额',
'1-2-Y',
'分入-合约-成数溢额',
'1-4-Q',
'分入-临分-成数',
'1-4-C',
'分入-临分-超赔',
'1-4-S',
'分入-临分-溢额',
'1-4-Y',
'分入-临分-成数溢额',
'0-2-Q',
'分出-合约-成数',
'0-2-C',
'分出-合约-超赔',
'0-2-S',
'分出-合约-溢额',
'0-2-Y',
'分出-合约-成数溢额',
'0-4-Q',
'分出-临分-成数-原保',
'2-4-Q',
'分出-临分-成数-再保',
'0-4-C',
'分出-临分-超赔-原保',
'2-4-C',
'分出-临分-超赔-再保',
'0-4-S',
'分出-临分-溢额-原保',
'2-4-S',
'分出-临分-溢额-再保',
'0-4-Y',
'分出-临分-成数溢额-原保',
'2-4-Y',
'分出-临分-成数溢额-再保',
NULL) AS RESULT_NAME
FROM RPT_ST.RI_I_BPACCOUNT T
GROUP BY T.RIBILLNO, T.OPTTYPE, T.CONTRACTTYPE, T.QSTYPE) I
ON I.RIBILLNO = T.RIBILLNO
LEFT JOIN RPT_ST.RI_POLICY RI_POLICY
ON T.POLICYNO = RI_POLICY.CED_POLICY_NO
LEFT JOIN RPT_ST.ACCOUNT_CATALOG ACCOUNT_CATALOG
ON ACCOUNT_CATALOG.BILL_NO = T.RIBILLNO
LEFT JOIN (SELECT STATEMENT_NO, REINSURER_NO, FAC_CONT_ID
FROM RPT_ST.RI_FAC_CONT_REINS_FIN
GROUP BY STATEMENT_NO, REINSURER_NO, FAC_CONT_ID) T5
ON T.RIBILLNO = T5.STATEMENT_NO
LEFT JOIN (SELECT T7.FAC_CONT_ID,
T7.REINSURER_NO,
T7.TAX_RATE,
T7.BROKER_RATE
FROM RPT_ST.RI_FAC_CONT_REINS T7) T7
ON T5.FAC_CONT_ID = T7.FAC_CONT_ID
AND T5.REINSURER_NO = T7.REINSURER_NO
LEFT JOIN TEL_CED_OUT_BROKERS OUT_BROKERS
ON T5.FAC_CONT_ID = OUT_BROKERS.FAC_CONT_ID
AND T5.REINSURER_NO = OUT_BROKERS.REINSURER_NO
LEFT JOIN TEL_CED_IN_BROKERS IN_BROKERS
ON T.POLICYNO = IN_BROKERS.CED_POLICY_NO
LEFT JOIN TEL_UNION_SUMMARY_NOS UNION_SUMMARY_NOS
ON T.POLICYNO = UNION_SUMMARY_NOS.POLICYNO
LEFT JOIN RPT_ST.RI_FAC_CONT T6
ON T5.FAC_CONT_ID = T6.FAC_CONT_ID
LEFT JOIN (SELECT DISTINCT (CONTRACTCODE) AS CONTRACTCODE, RIBILLNO
FROM RPT_ST.RI_I_BPACCOUNT) T9
ON T9.RIBILLNO = T.RIBILLNO
LEFT JOIN RPT_ST.CES_RST T8
ON ACCOUNT_CATALOG.CES_RESULTID = T8.RESULT_ID
LEFT JOIN RPT_ST.T_ORGANIZATION_VIEW OV
ON RI_POLICY.DEPT_CODE = OV.ORGANIZATIONCODE
LEFT JOIN IF_UW_POLICYBASICINFO P
ON P.POLICYNO = T.POLICYNO
AND P.ENDORSEMENTNO = T.ENDORSEMENTNO
LEFT JOIN (SELECT T.POLICYNO, MIN(T.ACCOUNTINGDATE) AS ACCOUNTINGDATE
FROM BIZ_BP_CHECK_BP_BASED T
GROUP BY T.POLICYNO) BIZ_BP_BASED
ON BIZ_BP_BASED.POLICYNO = T.POLICYNO
LEFT JOIN REP_V_POLICYPREMIUMIN_ALL T3
ON P.TOPACTUALID = T3.TOPACTUALID
WHERE T.FLAG = '201706'
AND T.COMPARE_FLAG = 'N';分析步骤
- 确认性能瓶颈
抓取到以上sql后，首先拿到测试环境运行，发现如下问题，能在20分钟内出结果，但是要刷出全部结果，却需要 >2h
分析sql，发现其查询字段中，带有很多自定义函数 rep_fun，去掉分析函数，sql能在1分内刷出全部结果。
由此可以确定该sql的性能问题，出在自定义函数上。- 分析自定义函数
函数REP_FUN_POLICY_COMBINESTRING
CREATE OR REPLACE FUNCTION REP_FUN_POLICY_COMBINESTRING( policy_no IN VARCHAR2
,endorsement_no IN VARCHAR2
,flags IN VARCHAR2) RETURN VARCHAR2 IS
V_POLICYNO_INPUT VARCHAR2(30);
V_ENDORSEMENTNO_INPUT VARCHAR2(30);
V_FLAGS_INPUT VARCHAR2(10);
V_RETURN_VALUE VARCHAR2(500); --返回值
BEGIN
V_POLICYNO_INPUT := policy_no;
V_ENDORSEMENTNO_INPUT := endorsement_no;
V_FLAGS_INPUT := flags;
If NVL(V_POLICYNO_INPUT,'')='' OR NVL(V_ENDORSEMENTNO_INPUT,'')='' OR NVL(V_FLAGS_INPUT,'')='' Then
Return Null;
Else
If V_FLAGS_INPUT = 'CODE' Then
select
MAX(decode(t.clausecode,'P-013',t.clausecode||';',NULL)) ||
MAX(decode(t.clausecode,'P-013B',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-013C',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-013A',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-013D',t.clausecode||';',NULL)) ||
MAX(decode(t.clausecode,'P-013E',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-116',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-116B',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-116C',t.clausecode||';',NULL)) ||
MAX(decode(t.clausecode,'P-116A',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-116D',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-116E',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P-074',t.clausecode||';',NULL)) ||
MAX(decode(t.clausecode,'P-075',t.clausecode||';',NULL))
INTO V_RETURN_VALUE
from if_uw_policyclauses t
where policyno=V_POLICYNO_INPUT
AND ENDORSEMENTNO=V_ENDORSEMENTNO_INPUT
GROUP BY t.policyno,t.ENDORSEMENTNO;
If length(NVL(V_RETURN_VALUE,' '))>1 Then
V_RETURN_VALUE := substr(V_RETURN_VALUE,1,length(V_RETURN_VALUE)-1);
End If;
Return V_RETURN_VALUE;
Elsif V_FLAGS_INPUT = 'NAME' Then
select
MAX(decode(t.clausecode,'P-013',t.clausename||';',NULL)) ||
MAX(decode(t.clausecode,'P-013B',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-013C',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-013A',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-013D',t.clausename||';',NULL)) ||
MAX(decode(t.clausecode,'P-013E',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-116',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-116B',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-116C',t.clausename||';',NULL)) ||
MAX(decode(t.clausecode,'P-116A',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-116D',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-116E',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P-074',t.clausename||';',NULL)) ||
MAX(decode(t.clausecode,'P-075',t.clausename||';',NULL))
INTO V_RETURN_VALUE
from if_uw_policyclauses t
where policyno=V_POLICYNO_INPUT
AND ENDORSEMENTNO=V_ENDORSEMENTNO_INPUT
GROUP BY t.policyno,t.ENDORSEMENTNO;
If length(NVL(V_RETURN_VALUE,' '))>1 Then
V_RETURN_VALUE := substr(V_RETURN_VALUE,1,length(V_RETURN_VALUE)-1);
End If;
Return V_RETURN_VALUE;
Elsif V_FLAGS_INPUT = 'EQCODE' then
select
MAX(decode(t.clausecode,'P-003',t.clausecode||';',NULL))||
MAX(decode(t.clausecode,'P021',t.clausecode||';',NULL))
INTO V_RETURN_VALUE
from if_uw_policyclauses t
where policyno=V_POLICYNO_INPUT
AND ENDORSEMENTNO=V_ENDORSEMENTNO_INPUT
GROUP BY t.policyno,t.ENDORSEMENTNO;
If length(NVL(V_RETURN_VALUE,' '))>1 Then
V_RETURN_VALUE := substr(V_RETURN_VALUE,1,length(V_RETURN_VALUE)-1);
End If;
Return V_RETURN_VALUE;
Elsif V_FLAGS_INPUT = 'EQNAME' then
select
MAX(decode(t.clausecode,'P-003',t.clausename||';',NULL))||
MAX(decode(t.clausecode,'P021',t.clausename||';',NULL))
INTO V_RETURN_VALUE
from if_uw_policyclauses t
where policyno=V_POLICYNO_INPUT
AND ENDORSEMENTNO=V_ENDORSEMENTNO_INPUT
GROUP BY t.policyno,t.ENDORSEMENTNO;
If length(NVL(V_RETURN_VALUE,' '))>1 Then
V_RETURN_VALUE := substr(V_RETURN_VALUE,1,length(V_RETURN_VALUE)-1);
End If;
Return V_RETURN_VALUE;
Else
Return Null;
End If;
End If;
End;单独运行函数中select语句，发现其的访问路径为“全表扫描”，且分组函数是MAX，所以可以在（policyno，ENDORSEMENTNO）上建立组合索引。
函数REP_RI_COMMISSIONVAT
CREATE OR REPLACE FUNCTION REP_RI_COMMISSIONVAT(RIBILLNO IN VARCHAR2)
RETURN NUMBER AS
V_RIBILLNO VARCHAR2(100); --输入的zb账单号
V_COMMISSION_IT NUMBER; --手续费增值税
--V_tax number;--税
V_COMMISSIONVAT NUMBER; --手续费增值税，返回值
BEGIN
V_RIBILLNO := RIBILLNO;
IF V_RIBILLNO IS NOT NULL THEN
SELECT NVL(SUM(VATAMOUNT), 0)
INTO V_COMMISSION_IT ---手续费增值税
FROM RI_I_BPACCOUNT_V
WHERE RIBILLNO = V_RIBILLNO
--- and datatype='08';---8285
AND DATATYPE IN ('08', '24');
V_COMMISSIONVAT := V_COMMISSION_IT;
END IF;
RETURN(V_COMMISSIONVAT);
EXCEPTION
WHEN OTHERS THEN
RETURN V_COMMISSIONVAT;
END;把自定义函数改成left join(为什么改成left join，因为IF V_RIBILLNO IS NOT NULL THEN 才会处理逻辑，而不为空的直接null。)
例如：
select b.V_COMMISSION1
from BIZ_BP_CHECK_BP_BASED_CEDOUT t
left join (SELECT ribillno, nvl(sum(netamount), 0) V_COMMISSION1
FROM ri_i_bpaccount_v
WHERE datatype in ('08', '24')
group by ribillno) b
on b.ribillno = t.ribillno函数REP_RI_COMMISSION
CREATE OR REPLACE FUNCTION REP_RI_COMMISSION (RIBILLNO IN VARCHAR2)
RETURN NUMBER
AS
V_RIBILLNO VARCHAR2(100); --输入的zb账单号
V_COMMISSION_IT number;--含税手续费
--V_tax number;--税
V_COMMISSION number;--不含税手续费，返回值
BEGIN
V_RIBILLNO := RIBILLNO ;
IF V_RIBILLNO IS NOT NULL THEN
SELECT nvl(sum(netamount),0)
INTO V_COMMISSION_IT ---含税手续费
FROM ri_i_bpaccount_v
WHERE ribillno = V_RIBILLNO
--- and datatype='08';---8285
and datatype in ('08','24')
group by ribillno;
V_COMMISSION:=V_COMMISSION_IT;
END IF ;
RETURN( V_COMMISSION );
EXCEPTION
WHEN OTHERS
THEN
RETURN V_COMMISSION;
END;把自定义函数改成left join(为什么改成left join，因为IF V_RIBILLNO IS NOT NULL THEN 才会处理逻辑，而不为空的直接null。)
例如：
SELECT C.V_COMMISSIONVAT2
FROM BIZ_BP_CHECK_BP_BASED_CEDOUT T
LEFT JOIN (SELECT NVL(SUM(VATAMOUNT), 0) V_COMMISSIONVAT2, RIBILLNO
FROM RI_I_BPACCOUNT_V
WHERE DATATYPE IN ('08', '24')
GROUP BY RIBILLNO) C
ON T.RIBILLNO = C.RIBILLNO优化后整体代码
耗时：45s能返回全部结果
select
REP_FUN_REFCODEVALUE(p.policystatus, '3') as policy_status,
REP_FUN_BP_EXCHANGERATE_BOM(NVL(T.Policycurrencycode,
'$$100001000001'),
FLAG) * t.amount_bp as RMB_amount_bp,
REP_FUN_BP_EXCHANGERATE_BOM(NVL(T.Policycurrencycode,
'$$100001000001'),
FLAG) * t.ri_prem_bp as RMB_ri_prem_bp,
REP_FUN_REFCODEVALUE(p.marinecargotype, '1'), --进出口标志
REP_FUN_BP_EXCHANGERATE_BOM(NVL(T3.Uwcurrency, '$$100001000001'),
'201706') * t3.suminsured as RMB_Gross_TSI, --保额
REP_FUN_POLICY_COMBINESTRING(p.policyno, p.endorsementno, 'CODE') as CBI_CODE, --CBI代码
REP_FUN_POLICY_COMBINESTRING(p.policyno, p.endorsementno, 'NAME') as CBI_NAME, --CBI名称
REP_FUN_POLICY_COMBINESTRING(p.policyno, p.endorsementno, 'EQCODE') as EQ_CODE, --EQ 代码
REP_FUN_POLICY_COMBINESTRING(p.policyno, p.endorsementno, 'EQNAME') as EQ_NAME, --EQ 名称
REP_FUN_BP_EXCHANGERATE_BOM(NVL(T.Policycurrencycode,
'$$100001000001'),
FLAG) * t.AMOUNTVAT_BP,
REP_FUN_BP_EXCHANGERATE_BOM(NVL(T.Policycurrencycode,
'$$100001000001'),
FLAG) * t.AMOUNTNET_BP,
REP_FUN_BP_EXCHANGERATE_BOM(NVL(T.Policycurrencycode,
'$$100001000001'),
FLAG) * t.RI_PREMVAT_BP,
REP_FUN_BP_EXCHANGERATE_BOM(NVL(T.Policycurrencycode,
'$$100001000001'),
FLAG) * t.RI_PREMNET_BP,
/* REP_RI_COMMISSIONVAT(t.riBILLNO),
REP_RI_COMMISSION(t.riBILLNO),*/
b.V_COMMISSION1,
C.V_COMMISSIONVAT2,
from BIZ_BP_CHECK_BP_BASED_CEDOUT t
left join (SELECT ribillno, nvl(sum(netamount), 0) V_COMMISSION1
FROM ri_i_bpaccount_v
WHERE datatype in ('08', '24')
group by ribillno) b
on b.ribillno = t.ribillno
LEFT JOIN (SELECT NVL(SUM(VATAMOUNT), 0) V_COMMISSIONVAT2, RIBILLNO
FROM RI_I_BPACCOUNT_V
WHERE DATATYPE IN ('08', '24')
GROUP BY RIBILLNO) C
ON T.RIBILLNO = C.RIBILLNO
left join (select t.ribillno,
t.OPTTYPE,
t.contracttype,
t.qstype,
decode(t.OPTTYPE || '-' || t.contracttype || '-' ||
t.qstype,
'1-2-Q',
'分入-合约-成数',
'1-2-C',
'分入-合约-超赔',
'1-2-S',
'分入-合约-溢额',
'1-2-Y',
'分入-合约-成数溢额',
'1-4-Q',
'分入-临分-成数',
'1-4-C',
'分入-临分-超赔',
'1-4-S',
'分入-临分-溢额',
'1-4-Y',
'分入-临分-成数溢额',
'0-2-Q',
'分出-合约-成数',
'0-2-C',
'分出-合约-超赔',
'0-2-S',
'分出-合约-溢额',
'0-2-Y',
'分出-合约-成数溢额',
'0-4-Q',
'分出-临分-成数-原保',
'2-4-Q',
'分出-临分-成数-再保',
'0-4-C',
'分出-临分-超赔-原保',
'2-4-C',
'分出-临分-超赔-再保',
'0-4-S',
'分出-临分-溢额-原保',
'2-4-S',
'分出-临分-溢额-再保',
'0-4-Y',
'分出-临分-成数溢额-原保',
'2-4-Y',
'分出-临分-成数溢额-再保',
null) AS RESULT_NAME
from rpt_st.Ri_i_Bpaccount t
group by t.ribillno, t.OPTTYPE, t.contracttype, t.qstype) i
on i.ribillno = t.ribillno
left join rpt_st.ri_policy ri_policy
on t.policyno = ri_policy.ced_policy_no
left join rpt_st.ACCOUNT_CATALOG ACCOUNT_CATALOG
on ACCOUNT_CATALOG.BILL_NO = t.ribillno
left join (select statement_no, REINSURER_NO, fac_cont_id
from rpt_st.Ri_Fac_Cont_Reins_Fin
group by statement_no, REINSURER_NO, fac_cont_id) t5
on t.ribillno = t5.statement_no
left join (select t7.fac_cont_id,
t7.REINSURER_NO,
t7.TAX_RATE,
t7.BROKER_RATE
from rpt_st.Ri_Fac_Cont_Reins t7) t7
on t5.fac_cont_id = t7.fac_cont_id
and t5.REINSURER_NO = t7.REINSURER_NO
left join TEL_CED_OUT_BROKERS OUT_BROKERS
on t5.fac_cont_id = OUT_BROKERS.Fac_Cont_Id
and t5.REINSURER_NO = OUT_BROKERS.REINSURER_NO
left join TEL_CED_IN_BROKERS IN_BROKERS
on t.policyno = IN_BROKERS.CED_POLICY_NO
left join TEL_union_summary_nos union_summary_nos
on t.policyno = union_summary_nos.policyno
left join rpt_st.Ri_Fac_Cont t6
on t5.fac_cont_id = t6.fac_cont_id
left join (select distinct (contractcode) as contractcode, ribillno
from rpt_st.ri_i_bpaccount) t9
on t9.ribillno = t.ribillno
left join rpt_st.ces_rst t8
on ACCOUNT_CATALOG.ces_resultid = t8.result_id
left join rpt_st.t_Organization_View ov
on ri_policy.dept_code = ov.organizationcode
left join if_uw_policybasicinfo P
on P.Policyno = t.policyno
and P.Endorsementno = t.Endorsementno
left join (select t.policyno, min(t.accountingdate) as accountingdate
from BIZ_BP_CHECK_BP_BASED t
group by t.policyno) BIZ_BP_BASED
on BIZ_BP_BASED.Policyno = t.policyno
left join REP_V_POLICYPREMIUMIN_all t3
ON p.topactualid = T3.topactualid
where t.flag = '201706'
and t.compare_flag = 'N'