我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
本文转自朋友的真实案例分享。
案例：troubleshooting slow parse sql on 19.16
该案例来自东区某客户，该客户使用的数据库版本为19.16，在从11g升级到19c之后，不止一个sql出现解析异常慢的情况。并且解析过程中并未出现异常的等待。
选取一个sql做explain测试：
SQL> alter session set current_schema=cifcar;Session altered.Elapsed: 00:00:00.00
SQL> explain plan for2 SELECT /*+ testparse */DISTINCT applyCust.ASQBH3 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AKHXM WHEN '2' THEN applyCust.AQYMC2 END AS aKhxm4 , applyCust.AXJZDSF5 , applyCust.AXJZDCS6 , applyCust.AXJZDQX7 , applyCust.AXJZDDZ8 , applyCust.AXJZDDH9 , ec.ASSSF10 , ec.ASSCS11 , ec.AZLSS12 , ec.ASSGS13 , applyCust.AKHLX14 , ma.AKKFS15 , ec.IZXYQTS16 , ec.ILSZGYQTS17 , ec.IZXYQQS18 , ec.FZXYQJE19 , NVL(ec.IZXHKQS, plan.AFQXH)20 , ec.AZXYQLX21 , ma.ARZQX22 , ec.IHMTS23 , ec.ASFYQ24 , ec.AYQZT25 , ec.AFXDJ26 , applyContract.AHTHM27 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AZJLX WHEN '2' THEN '组织机构代码' ELSE '' END AS azjlx28 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AZJHM WHEN '2' THEN applyCust.AZZJGDM ELSE '' END AS azjhm29 , st.APQMC30 , st.ATBDMC31 , applyCust.ASJHM32 , applyCust.APOXM33 , applyCust.APOSJHM34 , dbr.axm AS adbrxm35 , dbr.asj AS adbrsj36 , lxr.axm AS alxrxm37 , lxr.asj AS alxrsj38 , applyCust.ASQRZY39 , TRUNC(MONTHS_BETWEEN(SYSDATE, applyCust.DCSRQ) / 12) AS nl40 , co.FSFBL41 , gps.LINE_SIM AS asimkh142 , ma.DSJHKR43 , ma.AKHRKHH44 , ma.AHKRKHM45 , ma.AHKRJJKZH46 , car.ACX47 , car.ACXI48 , car.APP49 , car.ACLPZ50 , car.ACPHM51 , st.AZLGSMC52 , ma.ALLLX53 , CASE WHEN ec.ASCZT IN ( 'sc04', 'sc05' ) THEN 1 ELSE 0 END AS aclsfsh54 , NVL(plantemp.allRent, 0) AS allRent55 , NVL(plantemp.payRent1, 0) - NVL(plantemp.baddebtamt, 0) AS payRent56 , DECODE(SIGN(NVL(plantemp.allRent, 0) - NVL(plantemp.payRent1, 0) + NVL(plantemp.baddebtamt, 0)), 1,57 NVL(plantemp.allRent, 0) - NVL(plantemp.payRent1, 0) + NVL(plantemp.baddebtamt, 0), 0) AS oddRent58 , NVL(plantemp.fje, 0) + NVL(plantemp.wsje, 0) + NVL(plantemp.fwszj, 0) AS oddCouple59 , applyContract.AF_ABT_CNTRT_DT60 , CASE61 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL62 ELSE (CASE WHEN back.ID IS NULL THEN NVL(plantemp.fyzyg, 0) ELSE can.FYSJYK END) END AS fyzyg63 , CASE64 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL65 ELSE (CASE66 WHEN back.ID IS NULL THEN (NVL(c.FSYBJ - d.FSKBJ, 0) + (SELECT NVL(SUM(FYZYG -67 NVL(FBZJCDJE, 0) -68 NVL(FLPKCDJE, 0) -69 NVL(FSKJE, 0) -70 NVL(FGJYE, 0)), 0)71 FROM LB_REPAY_PLAN72 WHERE AZT IN ( '1', '2' )73 AND ASQBH = applyContract.ASQBH74 AND DZZRQ < applyContract.AF_ABT_CNTRT_DT))75 ELSE can.FBJYE END) END AS fbjye76 , CASE77 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL78 ELSE (CASE79 WHEN back.ID IS NULL THEN NVL(c.FWSXLYYE - d.FSKLX, 0)80 ELSE can.FWSXLXYE END) END AS fwsxlyye81 , CASE82 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL83 ELSE (CASE WHEN back.ID IS NULL THEN 0 ELSE can.FYSWSZJ END) END AS fyswszj84 , can.SQYWY85 , deptid.DEPT_NME86 FROM LB_APPLY_LESSEE_INFO applyCust87 LEFT JOIN LB_APPLY_CONTRACT_INFO applyContract ON applyCust.ASQBH = applyContract.ASQBH88 LEFT JOIN TASSET_EXPIRE_CUST_DATA ec ON ec.ASQBH = applyCust.ASQBH89 INNER JOIN LB_APPLY_DEALER st ON st.ASQBH = applyCust.ASQBH90 INNER JOIN LB_APPLY_COST co ON co.ASQBH = applyCust.ASQBH91 INNER JOIN LB_LOAN_GPS_INFO gps ON gps.ASQBH = applyCust.ASQBH92 INNER JOIN LB_APPLY_MAIN ma ON ma.ASQBH = applyCust.ASQBH93 INNER JOIN LB_APPLY_CAR car ON car.ASQBH = applyCust.ASQBH94 LEFT JOIN (SELECT MAX(DZZRQ) AS DZZRQ, ASQBH FROM LB_REPAY_PLAN WHERE AZT = '3' GROUP BY ASQBH) t95 ON t.ASQBH = applyCust.ASQBH96 LEFT JOIN LB_REPAY_PLAN plan ON plan.DZZRQ = t.DZZRQ AND t.ASQBH = plan.ASQBH97 LEFT JOIN(SELECT LISTAGG(tab.AXM, ',') WITHIN GROUP (ORDER BY tab.ID) AS axm98 , LISTAGG(DECODE(tab.ADBRLX, '1', tab.ASJ, '2', tab.AQYLXSJ), ',') WITHIN GROUP (ORDER BY tab.ID) AS asj99 , ASQBH
100 FROM LB_APPLY_LESSEE_BONDSMAN tab
101 GROUP BY tab.ASQBH) dbr ON dbr.ASQBH = ec.ASQBH
102 LEFT JOIN(SELECT LISTAGG(tac.ALXRXM, ',') WITHIN GROUP (ORDER BY tac.ID) AS axm
103 , LISTAGG(tac.ASJHM, '/') WITHIN GROUP (ORDER BY tac.ID) AS asj
104 , ASQBH
105 FROM LB_APPLY_LESSEE_CONTACTS tac
106 GROUP BY tac.ASQBH) lxr ON lxr.ASQBH = ec.ASQBH
107 LEFT JOIN (SELECT ASQBH
108 , SUM(CASE
109 WHEN AR_TYPE_ID IN ( '112201', '112206', '112229', '112232' ) THEN FYZYG
110 ELSE 0 END) AS allRent
111 , SUM(CASE
112 WHEN AR_TYPE_ID IN ( '112201', '112206', '112229', '112232', '630107', '220510' )
113 THEN CASE AZT
114 WHEN '3' THEN FYZYG
115 ELSE NVL(FGJYE, 0) + NVL(FBZJCDJE, 0) + NVL(FLPKCDJE, 0) + NVL(FSKJE, 0) END
116 ELSE 0 END) AS payRent1
117 , SUM(CASE
118 WHEN AR_TYPE_ID IN ( '112201', '112229', '112232' ) THEN NVL(BADDEBT_AMT, 0)
119 ELSE 0 END) AS baddebtamt
120 , SUM(CASE WHEN AR_TYPE_ID = '112201' AND AZT = '1' THEN NVL(FJE, 0) ELSE 0 END) AS fje
121 , SUM(CASE
122 WHEN AR_TYPE_ID = '112201' AND AZT = '2' THEN NVL(FYZYG, 0) - NVL(FGJYE, 0) -
123 NVL(FBZJCDJE, 0) - NVL(FLPKCDJE, 0) -
124 NVL(FSKJE, 0)
125 ELSE 0 END) AS wsje
126 , SUM(CASE
127 WHEN AR_TYPE_ID IN ( '112229', '630107', '112206' ) AND AZT IN ( '1', '2' ) THEN
128 NVL(FYZYG, 0) - NVL(FGJYE, 0) - NVL(FBZJCDJE, 0) - NVL(FLPKCDJE, 0) - NVL(FSKJE, 0)
129 ELSE 0 END) AS fwszj
130 , SUM(CASE WHEN AR_TYPE_ID = '112206' THEN FYZYG ELSE 0 END) AS fyzyg
131 FROM LB_REPAY_PLAN
132 GROUP BY ASQBH) plantemp ON applyCust.ASQBH = plantemp.ASQBH
133 LEFT JOIN (SELECT ASQBH, FSYBJ, FWSXLYYE
134 FROM TFINANCIAL_REPAYMENT_SCHEDULE
135 WHERE ( ASQBH, IFQXH ) IN (SELECT ASQBH, MAX(IFQXH) AS ifqxh
136 FROM TFINANCIAL_REPAYMENT_SCHEDULE
137 WHERE FSYBJ > 0
138 GROUP BY ASQBH)) c ON applyCust.ASQBH = c.ASQBH
139 LEFT JOIN (SELECT ASQBH, FSKBJ, FSKLX
140 FROM TFINANCIAL_REPAYMENT_SCHEDULE
141 WHERE ( ASQBH, IFQXH ) IN
142 (SELECT ASQBH, MAX(IFQXH) AS ifqxh FROM TFINANCIAL_REPAYMENT_SCHEDULE GROUP BY ASQBH)) d
143 ON applyCust.ASQBH = d.ASQBH
144 LEFT JOIN TASSET_BEFORE_CHARGEBACK back ON back.ASQBH = applyCust.ASQBH AND ATQHKZT IN ( 'T01', 'T05' )
145 LEFT JOIN CANCELLATION_INFORMATION can ON can.FYWID = back.ID
146 LEFT JOIN (SELECT dept.DEPT_NME, prel.USR_ID
147 FROM EU_DEPT dept
148 LEFT JOIN CIFFAS.EU_USR_PRFL prel ON dept.DEPT_ID = prel.DEPT_ID) deptid ON deptid.USR_ID = can.SQYWY
149 LEFT JOIN CIFFAS.LAW_DTL dtl ON dtl.CNTRT_NO = applyContract.AHTHM
150 WHERE 1 = 1
151 AND applyContract.AHTZT = '2'
152 ORDER BY applyCust.ASQBH;Explained.Elapsed: 00:00:31.60可以看到该sql非常复杂，表关联也非常多，光是解析就用了31秒，这肯定是无法忍受的。
因为没有异常等待事件，31秒都是on cpu的情况，所以分析方法一般都会去看看解析时候的short_stack。因为客户反映这个库升级19c之前在11g解析是非常快的，所以当时有想过遍历修改优化器参数和fix_control的方法来进行分析，不过仔细一想解析一次就要30s，遍历那么多优化器参数和fix_control不得跑到啥时候。
还是先做short_stack吧，通过short_stack可以看到堆栈信息为：
ksedsts <- ksdxfstk <- ksdxcb <- sspuser <- __sighandler<- ldxsti <- ldxnbeg <- qkesEval_Int <- qkesEvalPred2 <-kkeutlEvalOpn<- kkeutlCompHistActVals <- kkeTrimHist <- kkejeq <- kkeEqJoinSel <-kkepslCompCtl<- kkeIdxSelectivity <- kkeidc <- kketac <- kkonxc <- kkotap<- kkojnp <- kkocnp <- kkooqb <- kkoqbc <- apakkoqb<- apaqbdDescendents <- apaqbd <- kkqctCostTransfQB <- kkqctdrvSU<- kkqudrv<- kkqctdrvTD <- kkqdrv <- kkqctdrvIT <- apadrv <- opitca<- kksFullTypeCheck <- rpiswu2 <- kksSetBindType <- kksfbc <-opiexe<- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino<- opiodr <- opidrv <- sou2o <- opimai_real <- ssthrdmain
多执行几次short_stack，发现会在qkesEval_Int之后循环的去执行ldx*的函数。
另外short_stack似乎不太直观能反映在哪个函数上循环消耗了大量时间的情况。这种时候可以通过perf去跟踪解析函数在cpu上的消耗。
81.47% 1.09% oracle_28690_ci oracle [.] ldxnbeg
72.23% 9.46% oracle_28690_ci oracle [.] ldxsti
62.00% 0.00% oracle_28690_ci oracle [.] kkotap
61.68% 0.00% oracle_28690_ci oracle [.] kkoordj
59.46% 0.03% oracle_28690_ci oracle [.] kkonxc
59.00% 0.03% oracle_28690_ci oracle [.] kketac
58.84% 0.00% oracle_28690_ci oracle [.] kkeidc
57.65% 11.14% oracle_28690_ci oracle [.] ldxsnf
43.86% 0.00% oracle_28690_ci oracle [.] kkepsl
42.81% 0.00% oracle_28690_ci oracle [.] kkeIdxSelectivity
42.61% 0.00% oracle_28690_ci oracle [.] kkepslComp
40.91% 0.00% oracle_28690_ci oracle [.] kkoqbc
31.44% 29.81% oracle_28690_ci oracle [.] ldxmfn
可以看到ldxsti、ldxsnf、ldxmfn的self值最高，总共达到了50%。分析具体堆栈从ldxnbeg往上看看
87.96% 0.07% oracle_28690_ci oracle [.] kkepslCompCtl| --87.89%--kkepslCompCtl| --87.63%--kkeEqJoinSel| --87.56%--kkejeq| --85.86%--kkeutlCompHistActVals| --84.58%--kkeutlEvalOpn| --84.00%--qkesEvalPred2| --83.87%--qkesEval_Int| |--81.42%--ldxnbeg| | | |--72.15%--ldxsti| | | | | |--57.30%--ldxsnf| | | | | | | |--31.20%--ldxmfn| | | | | | | | | |--0.73%--lxhasc| | | | | | | | | --0.56%--lxmcpen| | | | | | | |--5.70%--lxhasc| | | | | | | |--2.16%--lxmalnx| | | | | | | |--1.35%--lxoCpChar| | | | | | | |--1.19%--ldxlxi| | | | | | | |--1.01%--lxmfwdx| | | | | | | |--0.86%--lxmopen| | | | | | | |--0.83%--lxmcpen| | | | | | | --0.51%--lxmalpx| | | | | --1.35%--lxmfwdx| | | |--6.93%--lxhlinfo| | | | | --6.53%--lxsCpStr| | | --0.82%--lxmfwdx| --1.32%--expepr| --0.99%--evareo
从perf record的输出可以看到，进入kkeEqJoinSel函数分析连接选择性之后，开始调用kkeutlCompHistActVals，这个函数与直方图有关系，之后就开始了循环ldxnbeg->ldxsti->ldxsnf->ldxmfn,ldx函数的循环，ldxmfn的self最高，ldx貌似与编译直方图值转换有关系。猜测是关联表个数过多，CBO评估join order的时候，会去排列组合各种连接顺序的情况，上限为”_optimizer_max_permutations“，如果这些关联条件上都存在直方图，就出现了案例中调用了大量ldx*函数的情况。
查看mos，并未发现任何有价值的文章。但是我还是猜测与关联字段上的直方图有关系。
查看sql发现关联字段都是ASQBH，且都存在混合直方图。这是一个编号类的字段，选择性都是不错的，直方图 没啥必要。
处理过程(删除掉关联字段上的直方图，并且设置收集统计信息默认不采集该字段直方图)：
SQL> exec dbms_stats.delete_column_stats(ownname=>CIFCAR, tabname=>'CREDIT_TONGDUN_INFO', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCAR', tabname=>'CREDIT_TONGDUN_INFO', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.23
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCAR', tabname=>'CREDIT_ZHONGCHENXIN_INFO', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.03
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCAR', tabname=>'LB_APPLY_CAR', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.04
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCAR', tabname=>'LB_APPLY_CONTRACT_INFO', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:11.64
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCAR', tabname=>'LB_APPLY_LESSEE_INFO', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.04
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCAR', tabname=>'LB_APPLY_MAIN', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:15.71
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCAR', tabname=>'TASSET_EXPIRE_CUST_DATA', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.03
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCONS', tabname=>'TASSET_CUST_CHARGE_BACK', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.03
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCONS', tabname=>'TASSET_EXPIRE_CUST_DATA', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.03
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCONS', tabname=>'TFINANCE_EBANK_ARRIVAL', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.03
SQL> exec dbms_stats.delete_column_stats(ownname=>'CIFCONS', tabname=>'LB_APPLY_MAIN', colname=>'ASQBH', col_stat_type=> 'HISTOGRAM');PL/SQL procedure successfully completed.Elapsed: 00:00:00.03
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCAR',tabname =>'CREDIT_TONGDUN_INFO', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.03
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCAR',tabname =>'CREDIT_ZHONGCHENXIN_INFO', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.00
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCAR',tabname =>'LB_APPLY_CAR', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.00
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCAR',tabname =>'LB_APPLY_CONTRACT_INFO', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.01
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCAR',tabname =>'LB_APPLY_LESSEE_INFO', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.01
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCAR',tabname =>'LB_APPLY_MAIN', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.01
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCAR',tabname =>'TASSET_EXPIRE_CUST_DATA', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.01
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCONS',tabname =>'TASSET_CUST_CHARGE_BACK', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.01
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCONS',tabname =>'TASSET_EXPIRE_CUST_DATA', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.01
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCONS',tabname =>'TFINANCE_EBANK_ARRIVAL', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.00
SQL> exec dbms_stats.set_table_prefs(ownname =>'CIFCONS',tabname =>'LB_APPLY_MAIN', pname=>'method_opt',pvalue=>'for columns ASQBH size 1');PL/SQL procedure successfully completed.Elapsed: 00:00:00.01
删除关联列直方图后解析时间就正常了。
SQL> explain plan for2 SELECT /*+ttttestparse*/DISTINCT applyCust.ASQBH3 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AKHXM WHEN '2' THEN applyCust.AQYMC2 END AS aKhxm4 , applyCust.AXJZDSF5 , applyCust.AXJZDCS6 , applyCust.AXJZDQX7 , applyCust.AXJZDDZ8 , applyCust.AXJZDDH9 , ec.ASSSF10 , ec.ASSCS11 , ec.AZLSS12 , ec.ASSGS13 , applyCust.AKHLX14 , ma.AKKFS15 , ec.IZXYQTS16 , ec.ILSZGYQTS17 , ec.IZXYQQS18 , ec.FZXYQJE19 , NVL(ec.IZXHKQS, plan.AFQXH)20 , ec.AZXYQLX21 , ma.ARZQX22 , ec.IHMTS23 , ec.ASFYQ24 , ec.AYQZT25 , ec.AFXDJ26 , applyContract.AHTHM27 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AZJLX WHEN '2' THEN '组织机构代码' ELSE '' END AS azjlx28 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AZJHM WHEN '2' THEN applyCust.AZZJGDM ELSE '' END AS azjhm29 , st.APQMC30 , st.ATBDMC31 , applyCust.ASJHM32 , applyCust.APOXM33 , applyCust.APOSJHM34 , dbr.axm AS adbrxm35 , dbr.asj AS adbrsj36 , lxr.axm AS alxrxm37 , lxr.asj AS alxrsj38 , applyCust.ASQRZY39 , TRUNC(MONTHS_BETWEEN(SYSDATE, applyCust.DCSRQ) / 12) AS nl40 , co.FSFBL41 , gps.LINE_SIM AS asimkh142 , ma.DSJHKR43 , ma.AKHRKHH44 , ma.AHKRKHM45 , ma.AHKRJJKZH46 , car.ACX47 , car.ACXI48 , car.APP49 , car.ACLPZ50 , car.ACPHM51 , st.AZLGSMC52 , ma.ALLLX53 , CASE WHEN ec.ASCZT IN ( 'sc04', 'sc05' ) THEN 1 ELSE 0 END AS aclsfsh54 , NVL(plantemp.allRent, 0) AS allRent55 , NVL(plantemp.payRent1, 0) - NVL(plantemp.baddebtamt, 0) AS payRent56 , DECODE(SIGN(NVL(plantemp.allRent, 0) - NVL(plantemp.payRent1, 0) + NVL(plantemp.baddebtamt, 0)), 1,57 NVL(plantemp.allRent, 0) - NVL(plantemp.payRent1, 0) + NVL(plantemp.baddebtamt, 0), 0) AS oddRent58 , NVL(plantemp.fje, 0) + NVL(plantemp.wsje, 0) + NVL(plantemp.fwszj, 0) AS oddCouple59 , applyContract.AF_ABT_CNTRT_DT60 , CASE61 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL62 ELSE (CASE WHEN back.ID IS NULL THEN NVL(plantemp.fyzyg, 0) ELSE can.FYSJYK END) END AS fyzyg63 , CASE64 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL65 ELSE (CASE66 WHEN back.ID IS NULL THEN (NVL(c.FSYBJ - d.FSKBJ, 0) + (SELECT NVL(SUM(FYZYG -67 NVL(FBZJCDJE, 0) -68 NVL(FLPKCDJE, 0) -69 NVL(FSKJE, 0) -70 NVL(FGJYE, 0)), 0)71 FROM LB_REPAY_PLAN72 WHERE AZT IN ( '1', '2' )73 AND ASQBH = applyContract.ASQBH74 AND DZZRQ < applyContract.AF_ABT_CNTRT_DT))75 ELSE can.FBJYE END) END AS fbjye76 , CASE77 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL78 ELSE (CASE79 WHEN back.ID IS NULL THEN NVL(c.FWSXLYYE - d.FSKLX, 0)80 ELSE can.FWSXLXYE END) END AS fwsxlyye81 , CASE82 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL83 ELSE (CASE WHEN back.ID IS NULL THEN 0 ELSE can.FYSWSZJ END) END AS fyswszj84 , can.SQYWY85 , deptid.DEPT_NME86 FROM LB_APPLY_LESSEE_INFO applyCust87 LEFT JOIN LB_APPLY_CONTRACT_INFO applyContract ON applyCust.ASQBH = applyContract.ASQBH88 LEFT JOIN TASSET_EXPIRE_CUST_DATA ec ON ec.ASQBH = applyCust.ASQBH89 INNER JOIN LB_APPLY_DEALER st ON st.ASQBH = applyCust.ASQBH90 INNER JOIN LB_APPLY_COST co ON co.ASQBH = applyCust.ASQBH91 INNER JOIN LB_LOAN_GPS_INFO gps ON gps.ASQBH = applyCust.ASQBH92 INNER JOIN LB_APPLY_MAIN ma ON ma.ASQBH = applyCust.ASQBH93 INNER JOIN LB_APPLY_CAR car ON car.ASQBH = applyCust.ASQBH94 LEFT JOIN (SELECT MAX(DZZRQ) AS DZZRQ, ASQBH FROM LB_REPAY_PLAN WHERE AZT = '3' GROUP BY ASQBH) t95 ON t.ASQBH = applyCust.ASQBH96 LEFT JOIN LB_REPAY_PLAN plan ON plan.DZZRQ = t.DZZRQ AND t.ASQBH = plan.ASQBH97 LEFT JOIN(SELECT LISTAGG(tab.AXM, ',') WITHIN GROUP (ORDER BY tab.ID) AS axm98 , LISTAGG(DECODE(tab.ADBRLX, '1', tab.ASJ, '2', tab.AQYLXSJ), ',') WITHIN GROUP (ORDER BY tab.ID) AS asj99 , ASQBH
100 FROM LB_APPLY_LESSEE_BONDSMAN tab
101 GROUP BY tab.ASQBH) dbr ON dbr.ASQBH = ec.ASQBH
102 LEFT JOIN(SELECT LISTAGG(tac.ALXRXM, ',') WITHIN GROUP (ORDER BY tac.ID) AS axm
103 , LISTAGG(tac.ASJHM, '/') WITHIN GROUP (ORDER BY tac.ID) AS asj
104 , ASQBH
105 FROM LB_APPLY_LESSEE_CONTACTS tac
106 GROUP BY tac.ASQBH) lxr ON lxr.ASQBH = ec.ASQBH
107 LEFT JOIN (SELECT ASQBH
108 , SUM(CASE
109 WHEN AR_TYPE_ID IN ( '112201', '112206', '112229', '112232' ) THEN FYZYG
110 ELSE 0 END) AS allRent
111 , SUM(CASE
112 WHEN AR_TYPE_ID IN ( '112201', '112206', '112229', '112232', '630107', '220510' )
113 THEN CASE AZT
114 WHEN '3' THEN FYZYG
115 ELSE NVL(FGJYE, 0) + NVL(FBZJCDJE, 0) + NVL(FLPKCDJE, 0) + NVL(FSKJE, 0) END
116 ELSE 0 END) AS payRent1
117 , SUM(CASE
118 WHEN AR_TYPE_ID IN ( '112201', '112229', '112232' ) THEN NVL(BADDEBT_AMT, 0)
119 ELSE 0 END) AS baddebtamt
120 , SUM(CASE WHEN AR_TYPE_ID = '112201' AND AZT = '1' THEN NVL(FJE, 0) ELSE 0 END) AS fje
121 , SUM(CASE
122 WHEN AR_TYPE_ID = '112201' AND AZT = '2' THEN NVL(FYZYG, 0) - NVL(FGJYE, 0) -
123 NVL(FBZJCDJE, 0) - NVL(FLPKCDJE, 0) -
124 NVL(FSKJE, 0)
125 ELSE 0 END) AS wsje
126 , SUM(CASE
127 WHEN AR_TYPE_ID IN ( '112229', '630107', '112206' ) AND AZT IN ( '1', '2' ) THEN
128 NVL(FYZYG, 0) - NVL(FGJYE, 0) - NVL(FBZJCDJE, 0) - NVL(FLPKCDJE, 0) - NVL(FSKJE, 0)
129 ELSE 0 END) AS fwszj
130 , SUM(CASE WHEN AR_TYPE_ID = '112206' THEN FYZYG ELSE 0 END) AS fyzyg
131 FROM LB_REPAY_PLAN
132 GROUP BY ASQBH) plantemp ON applyCust.ASQBH = plantemp.ASQBH
133 LEFT JOIN (SELECT ASQBH, FSYBJ, FWSXLYYE
134 FROM TFINANCIAL_REPAYMENT_SCHEDULE
135 WHERE ( ASQBH, IFQXH ) IN (SELECT ASQBH, MAX(IFQXH) AS ifqxh
136 FROM TFINANCIAL_REPAYMENT_SCHEDULE
137 WHERE FSYBJ > 0
138 GROUP BY ASQBH)) c ON applyCust.ASQBH = c.ASQBH
139 LEFT JOIN (SELECT ASQBH, FSKBJ, FSKLX
140 FROM TFINANCIAL_REPAYMENT_SCHEDULE
141 WHERE ( ASQBH, IFQXH ) IN
142 (SELECT ASQBH, MAX(IFQXH) AS ifqxh FROM TFINANCIAL_REPAYMENT_SCHEDULE GROUP BY ASQBH)) d
143 ON applyCust.ASQBH = d.ASQBH
144 LEFT JOIN TASSET_BEFORE_CHARGEBACK back ON back.ASQBH = applyCust.ASQBH AND ATQHKZT IN ( 'T01', 'T05' )
145 LEFT JOIN CANCELLATION_INFORMATION can ON can.FYWID = back.ID
146 LEFT JOIN (SELECT dept.DEPT_NME, prel.USR_ID
147 FROM EU_DEPT dept
148 LEFT JOIN CIFFAS.EU_USR_PRFL prel ON dept.DEPT_ID = prel.DEPT_ID) deptid ON deptid.USR_ID = can.SQYWY
149 LEFT JOIN CIFFAS.LAW_DTL dtl ON dtl.CNTRT_NO = applyContract.AHTHM
150 WHERE 1 = 1
151 AND applyContract.AHTZT = '2'
152 ORDER BY applyCust.ASQBH;Explained.Elapsed: 00:00:01.80
通过systemtap、perf、gdb等诊断工具，对于一些比较需要对深入底层的问题的诊断尤其好使，让闭源的oracle的诊断也能像开源数据库的问题诊断一样深入。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
