案例：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

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可以看到堆栈信息为：

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

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

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

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.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

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');