该案例来自东区某客户,该客户使用的数据库版本为19.16,在从11g升级到19c之后,不止一个sql出现解析异常慢的情况。并且解析过程中并未出现异常的等待。
选取一个sql做explain测试:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
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可以看到堆栈信息为:
1 2 3 4 |
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上的消耗。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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往上看看
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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,且都存在混合直方图。这是一个编号类的字段,选择性都是不错的,直方图 没啥必要。
处理过程(删除掉关联字段上的直方图,并且设置收集统计信息默认不采集该字段直方图):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
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 |
删除关联列直方图后解析时间就正常了。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 |
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!