该案例来自东区某客户，该客户使用的数据库版本为19.16，在从11g升级到19c之后，不止一个sql出现解析异常慢的情况。并且解析过程中并未出现异常的等待。
选取一个sql做explain测试：
SQL> alter session set current_schema=cifcar; Session altered. Elapsed: 00:00:00.00 SQL> explain plan for 2 SELECT /*+ testparse */DISTINCT applyCust.ASQBH 3 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AKHXM WHEN '2' THEN applyCust.AQYMC2 END AS aKhxm 4 , applyCust.AXJZDSF 5 , applyCust.AXJZDCS 6 , applyCust.AXJZDQX 7 , applyCust.AXJZDDZ 8 , applyCust.AXJZDDH 9 , ec.ASSSF 10 , ec.ASSCS 11 , ec.AZLSS 12 , ec.ASSGS 13 , applyCust.AKHLX 14 , ma.AKKFS 15 , ec.IZXYQTS 16 , ec.ILSZGYQTS 17 , ec.IZXYQQS 18 , ec.FZXYQJE 19 , NVL(ec.IZXHKQS, plan.AFQXH) 20 , ec.AZXYQLX 21 , ma.ARZQX 22 , ec.IHMTS 23 , ec.ASFYQ 24 , ec.AYQZT 25 , ec.AFXDJ 26 , applyContract.AHTHM 27 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AZJLX WHEN '2' THEN '组织机构代码' ELSE '' END AS azjlx 28 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AZJHM WHEN '2' THEN applyCust.AZZJGDM ELSE '' END AS azjhm 29 , st.APQMC 30 , st.ATBDMC 31 , applyCust.ASJHM 32 , applyCust.APOXM 33 , applyCust.APOSJHM 34 , dbr.axm AS adbrxm 35 , dbr.asj AS adbrsj 36 , lxr.axm AS alxrxm 37 , lxr.asj AS alxrsj 38 , applyCust.ASQRZY 39 , TRUNC(MONTHS_BETWEEN(SYSDATE, applyCust.DCSRQ) / 12) AS nl 40 , co.FSFBL 41 , gps.LINE_SIM AS asimkh1 42 , ma.DSJHKR 43 , ma.AKHRKHH 44 , ma.AHKRKHM 45 , ma.AHKRJJKZH 46 , car.ACX 47 , car.ACXI 48 , car.APP 49 , car.ACLPZ 50 , car.ACPHM 51 , st.AZLGSMC 52 , ma.ALLLX 53 , CASE WHEN ec.ASCZT IN ( 'sc04', 'sc05' ) THEN 1 ELSE 0 END AS aclsfsh 54 , NVL(plantemp.allRent, 0) AS allRent 55 , NVL(plantemp.payRent1, 0) - NVL(plantemp.baddebtamt, 0) AS payRent 56 , 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 oddRent 58 , NVL(plantemp.fje, 0) + NVL(plantemp.wsje, 0) + NVL(plantemp.fwszj, 0) AS oddCouple 59 , applyContract.AF_ABT_CNTRT_DT 60 , CASE 61 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL 62 ELSE (CASE WHEN back.ID IS NULL THEN NVL(plantemp.fyzyg, 0) ELSE can.FYSJYK END) END AS fyzyg 63 , CASE 64 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL 65 ELSE (CASE 66 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_PLAN 72 WHERE AZT IN ( '1', '2' ) 73 AND ASQBH = applyContract.ASQBH 74 AND DZZRQ < applyContract.AF_ABT_CNTRT_DT)) 75 ELSE can.FBJYE END) END AS fbjye 76 , CASE 77 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL 78 ELSE (CASE 79 WHEN back.ID IS NULL THEN NVL(c.FWSXLYYE - d.FSKLX, 0) 80 ELSE can.FWSXLXYE END) END AS fwsxlyye 81 , CASE 82 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL 83 ELSE (CASE WHEN back.ID IS NULL THEN 0 ELSE can.FYSWSZJ END) END AS fyswszj 84 , can.SQYWY 85 , deptid.DEPT_NME 86 FROM LB_APPLY_LESSEE_INFO applyCust 87 LEFT JOIN LB_APPLY_CONTRACT_INFO applyContract ON applyCust.ASQBH = applyContract.ASQBH 88 LEFT JOIN TASSET_EXPIRE_CUST_DATA ec ON ec.ASQBH = applyCust.ASQBH 89 INNER JOIN LB_APPLY_DEALER st ON st.ASQBH = applyCust.ASQBH 90 INNER JOIN LB_APPLY_COST co ON co.ASQBH = applyCust.ASQBH 91 INNER JOIN LB_LOAN_GPS_INFO gps ON gps.ASQBH = applyCust.ASQBH 92 INNER JOIN LB_APPLY_MAIN ma ON ma.ASQBH = applyCust.ASQBH 93 INNER JOIN LB_APPLY_CAR car ON car.ASQBH = applyCust.ASQBH 94 LEFT JOIN (SELECT MAX(DZZRQ) AS DZZRQ, ASQBH FROM LB_REPAY_PLAN WHERE AZT = '3' GROUP BY ASQBH) t 95 ON t.ASQBH = applyCust.ASQBH 96 LEFT JOIN LB_REPAY_PLAN plan ON plan.DZZRQ = t.DZZRQ AND t.ASQBH = plan.ASQBH 97 LEFT JOIN(SELECT LISTAGG(tab.AXM, ',') WITHIN GROUP (ORDER BY tab.ID) AS axm 98 , LISTAGG(DECODE(tab.ADBRLX, '1', tab.ASJ, '2', tab.AQYLXSJ), ',') WITHIN GROUP (ORDER BY tab.ID) AS asj 99 , 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 for 2 SELECT /*+ttttestparse*/DISTINCT applyCust.ASQBH 3 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AKHXM WHEN '2' THEN applyCust.AQYMC2 END AS aKhxm 4 , applyCust.AXJZDSF 5 , applyCust.AXJZDCS 6 , applyCust.AXJZDQX 7 , applyCust.AXJZDDZ 8 , applyCust.AXJZDDH 9 , ec.ASSSF 10 , ec.ASSCS 11 , ec.AZLSS 12 , ec.ASSGS 13 , applyCust.AKHLX 14 , ma.AKKFS 15 , ec.IZXYQTS 16 , ec.ILSZGYQTS 17 , ec.IZXYQQS 18 , ec.FZXYQJE 19 , NVL(ec.IZXHKQS, plan.AFQXH) 20 , ec.AZXYQLX 21 , ma.ARZQX 22 , ec.IHMTS 23 , ec.ASFYQ 24 , ec.AYQZT 25 , ec.AFXDJ 26 , applyContract.AHTHM 27 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AZJLX WHEN '2' THEN '组织机构代码' ELSE '' END AS azjlx 28 , CASE applyCust.AKHLX WHEN '1' THEN applyCust.AZJHM WHEN '2' THEN applyCust.AZZJGDM ELSE '' END AS azjhm 29 , st.APQMC 30 , st.ATBDMC 31 , applyCust.ASJHM 32 , applyCust.APOXM 33 , applyCust.APOSJHM 34 , dbr.axm AS adbrxm 35 , dbr.asj AS adbrsj 36 , lxr.axm AS alxrxm 37 , lxr.asj AS alxrsj 38 , applyCust.ASQRZY 39 , TRUNC(MONTHS_BETWEEN(SYSDATE, applyCust.DCSRQ) / 12) AS nl 40 , co.FSFBL 41 , gps.LINE_SIM AS asimkh1 42 , ma.DSJHKR 43 , ma.AKHRKHH 44 , ma.AHKRKHM 45 , ma.AHKRJJKZH 46 , car.ACX 47 , car.ACXI 48 , car.APP 49 , car.ACLPZ 50 , car.ACPHM 51 , st.AZLGSMC 52 , ma.ALLLX 53 , CASE WHEN ec.ASCZT IN ( 'sc04', 'sc05' ) THEN 1 ELSE 0 END AS aclsfsh 54 , NVL(plantemp.allRent, 0) AS allRent 55 , NVL(plantemp.payRent1, 0) - NVL(plantemp.baddebtamt, 0) AS payRent 56 , 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 oddRent 58 , NVL(plantemp.fje, 0) + NVL(plantemp.wsje, 0) + NVL(plantemp.fwszj, 0) AS oddCouple 59 , applyContract.AF_ABT_CNTRT_DT 60 , CASE 61 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL 62 ELSE (CASE WHEN back.ID IS NULL THEN NVL(plantemp.fyzyg, 0) ELSE can.FYSJYK END) END AS fyzyg 63 , CASE 64 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL 65 ELSE (CASE 66 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_PLAN 72 WHERE AZT IN ( '1', '2' ) 73 AND ASQBH = applyContract.ASQBH 74 AND DZZRQ < applyContract.AF_ABT_CNTRT_DT)) 75 ELSE can.FBJYE END) END AS fbjye 76 , CASE 77 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL 78 ELSE (CASE 79 WHEN back.ID IS NULL THEN NVL(c.FWSXLYYE - d.FSKLX, 0) 80 ELSE can.FWSXLXYE END) END AS fwsxlyye 81 , CASE 82 WHEN AF_ABT_CNTRT_DT IS NULL THEN NULL 83 ELSE (CASE WHEN back.ID IS NULL THEN 0 ELSE can.FYSWSZJ END) END AS fyswszj 84 , can.SQYWY 85 , deptid.DEPT_NME 86 FROM LB_APPLY_LESSEE_INFO applyCust 87 LEFT JOIN LB_APPLY_CONTRACT_INFO applyContract ON applyCust.ASQBH = applyContract.ASQBH 88 LEFT JOIN TASSET_EXPIRE_CUST_DATA ec ON ec.ASQBH = applyCust.ASQBH 89 INNER JOIN LB_APPLY_DEALER st ON st.ASQBH = applyCust.ASQBH 90 INNER JOIN LB_APPLY_COST co ON co.ASQBH = applyCust.ASQBH 91 INNER JOIN LB_LOAN_GPS_INFO gps ON gps.ASQBH = applyCust.ASQBH 92 INNER JOIN LB_APPLY_MAIN ma ON ma.ASQBH = applyCust.ASQBH 93 INNER JOIN LB_APPLY_CAR car ON car.ASQBH = applyCust.ASQBH 94 LEFT JOIN (SELECT MAX(DZZRQ) AS DZZRQ, ASQBH FROM LB_REPAY_PLAN WHERE AZT = '3' GROUP BY ASQBH) t 95 ON t.ASQBH = applyCust.ASQBH 96 LEFT JOIN LB_REPAY_PLAN plan ON plan.DZZRQ = t.DZZRQ AND t.ASQBH = plan.ASQBH 97 LEFT JOIN(SELECT LISTAGG(tab.AXM, ',') WITHIN GROUP (ORDER BY tab.ID) AS axm 98 , LISTAGG(DECODE(tab.ADBRLX, '1', tab.ASJ, '2', tab.AQYLXSJ), ',') WITHIN GROUP (ORDER BY tab.ID) AS asj 99 , 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的诊断也能像开源数据库的问题诊断一样深入。
over！
发表回复