性能调优：troubleshooting slow parse sql on 19.16
我们的文章会在微信公众号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 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的诊断也能像开源数据库的问题诊断一样深入。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号