当SQL出现问题，能从执行计划中快速的定位哪部分出现问题很重要，SQL文本如下（为保证客户隐私，已经将注释和文字部分去掉）：
|
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
|
SELECT
/*+
index
(i IDX_INVM_BEC)*/ RQ,JGM,BZ,CUSTOMER_TYPE,
B.CUSTOMER_NO,
B.CUSTOMER_NAME
AS
DKHM,
B.ACCT_NO DKZH,
B.STATUS,
B.LOAN_BAL,
P,
LX,
NVL((
SELECT
A.ACCT_NO_DESC || A.ACCT_NO_ALL2
FROM
INVM_ZMQ A
WHERE
A.ACCT_NO=I.ACCT_NO
AND
A.ZHLB=
'3'
),I.ACCT_NO)
AS
CKZH,
I.CURR_VAL,
(
CASE
WHEN
B.TRANSFER_ACCT=I.ACCT_NO
THEN
'嘻嘻嘻'
ELSE
''
END
)
AS
SM
FROM
(
SELECT
B.EXTDATE
AS
RQ,
B.BRANCH_NO
AS
JGM,
B.CURRENCY
AS
BZ,
C.CUSTOMER_TYPE,
B.CUSTOMER_NO,
C.CUSTOMER_NAME,
B.ACCT_NO,
'啊啊'
AS
STATUS,
B.LOAN_BAL,
B.UNPD_PRIN_BAL
AS
P,
ROUND(B.CAP_UNPD_INT,2)+
(
CASE
WHEN
B.REPAY_SCHED
IN
(
'M'
,
'G'
)
OR
L.REPAY_SCHED
IN
(
'M'
,
'G'
)
THEN
ELSE
(
CASE
WHEN
L.PIA_CAPN_FREQ=
'S'
AND
L.PAST_DUE_CAPN_FREQ=
'S'
THEN
ROUND(B.THEO_UNPD_ARR_PRN,2)
+(
CASE
WHEN
L.FINE1_COMPD_OPT=
'Y'
THEN
ROUND(B.THEO_UNPD_ARRS_INT,2)
ELSE
0
END
)
+(
CASE
WHEN
L.FINE2_COMPD_OPT=
'Y'
THEN
ROUND(B.THEO_UNPD_INT_ARR,2)
ELSE
0
END
)
ELSE
END
)
END
)
AS
LX,
B.TRF_ACCT_NO
AS
TRANSFER_ACCT
FROM
BORM PARTITION(
"BORM_2018-06-13"
) B
INNER
JOIN
LONP L
ON
L.ACCT_TYPE=B.ACCT_TYPE
AND
L.INT_CAT=B.INT_CAT
INNER
JOIN
JGDY J
ON
B.BRANCH_NO = J.JGM
AND
(J.JGM=
'1700'
OR
J.SJJGM=
'1700'
)
LEFT
JOIN
CB_ACCT C
ON
B.ACCT_NO = C.ACCT_NO
AND
C.SYS_ID =
'BOR'
WHERE
B.BAD_DEBT_IND
in
(
'02'
,
'52'
)
AND
B.STAT<>
'40'
) B
LEFT
JOIN
INVM PARTITION(
"INVM_2018-06-13"
) I
ON
B.CUSTOMER_NO=I.CUSTOMER_NO
AND
I.ACCT_DESC=
'S'
AND
I.CURR_VAL<>0
AND
I.BRANCH_NO
IN
(
SELECT
JGM
FROM
JGDY
WHERE
JGM=
'1700'
OR
SJJGM=
'1700'
)
AND
I.EXTDATE =
DATE
'2018-06-13'
AND
I.CURR_STATUS=
'00'
WHERE
(B.P>0
OR
B.LX>0.01);
|
执行计划如下：
可以一眼定位到view部分导致整个执行缓慢，那么我们仔细分析下view部分是怎么执行的。INVM TABLE ACCESS BY LOCAL INDEX ROWID执行11分钟，总计13分钟执行完。
寻找view部分执行计划的入口，ID18和ID19做nested loop，返回结果17与21做NESTED LOOP，可以得知最先执行的是ID18，ID18走的iffs，且A-rows返回记录6256行数据，查看ID18谓词信息
18 - filter(("SJJGM"='1700' OR "JGM"='1700')) 从这部分再回到SQL文本寻找SQL代码是AND I.BRANCH_NO IN (SELECT JGM FROM JGDY WHERE JGM='1700' OR SJJGM='1700')也就是【SELECT JGM FROM JGDY WHERE JGM='1700' OR SJJGM='1700'】，难道这部分返回结果真的是6256行数据吗？带着疑问我查询了一下。
居然只返回了34行数据，为什么会这样子？
肯定是此处的JGDY_IDX3有什么问题，那么会有什么问题呢？往上看ID为8的JGDY_IDX3正确的返回了34行数据，又仔细看了下ID为18的JGDY_IDX3，starts184次，正好6256/184=34，那么原因找到了，正是因为ID4和ID15做NESTED LOOP，导致视图里面所有的部分都要多执行184次。按照上面的分析思路看ID4里面的执行计划都很正确，但是返回结果184行记录且ID4和ID15做nested loop，导致整个view部分缓慢。
那么就很好办了，ID4和ID15应该走hash join，查看outline data信息，还没办法使用db_name信息引导执行计划走hash join，那么只能改写SQL。
改写SQL如下：
|
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
|
SELECT
RQ,JGM,BZ,CUSTOMER_TYPE,
B.CUSTOMER_NO,
B.CUSTOMER_NAME
AS
DKHM,
B.ACCT_NO DKZH,
B.STATUS,
B.LOAN_BAL,
P,
LX,
NVL((
SELECT
A.ACCT_NO_DESC || A.ACCT_NO_ALL2
FROM
INVM_ZMQ A
WHERE
A.ACCT_NO=C.ACCT_NO
AND
A.ZHLB=
'3'
),C.ACCT_NO)
AS
CKZH,
C.CURR_VAL,
(
CASE
WHEN
B.TRANSFER_ACCT=C.ACCT_NO
THEN
'嘻嘻嘻'
ELSE
''
END
)
AS
SM
FROM
(
SELECT
B.EXTDATE
AS
RQ,
B.BRANCH_NO
AS
JGM,
B.CURRENCY
AS
BZ,
C.CUSTOMER_TYPE,
B.CUSTOMER_NO,
C.CUSTOMER_NAME,
B.ACCT_NO,
'啊啊'
AS
STATUS,
B.LOAN_BAL,
B.UNPD_PRIN_BAL
AS
P,
ROUND(B.CAP_UNPD_INT,2)+
(
CASE
WHEN
B.REPAY_SCHED
IN
(
'M'
,
'G'
)
OR
L.REPAY_SCHED
IN
(
'M'
,
'G'
)
THEN
ELSE
(
CASE
WHEN
L.PIA_CAPN_FREQ=
'S'
AND
L.PAST_DUE_CAPN_FREQ=
'S'
THEN
ROUND(B.THEO_UNPD_ARR_PRN,2)
+(
CASE
WHEN
L.FINE1_COMPD_OPT=
'Y'
THEN
ROUND(B.THEO_UNPD_ARRS_INT,2)
ELSE
0
END
)
+(
CASE
WHEN
L.FINE2_COMPD_OPT=
'Y'
THEN
ROUND(B.THEO_UNPD_INT_ARR,2)
ELSE
0
END
)
ELSE
END
)
END
)
AS
LX,
B.TRF_ACCT_NO
AS
TRANSFER_ACCT
FROM
BORM PARTITION(
"BORM_2018-06-13"
) B
INNER
JOIN
LONP L
ON
L.ACCT_TYPE=B.ACCT_TYPE
AND
L.INT_CAT=B.INT_CAT
INNER
JOIN
JGDY J
ON
B.BRANCH_NO = J.JGM
AND
(J.JGM=
'1700'
OR
J.SJJGM=
'1700'
)
LEFT
JOIN
CB_ACCT C
ON
B.ACCT_NO = C.ACCT_NO
AND
C.SYS_ID =
'BOR'
WHERE
B.BAD_DEBT_IND
in
(
'02'
,
'52'
)
AND
B.STAT<>
'40'
) B
LEFT
JOIN
(
SELECT
/*+
index
(I IDX_INVM_BEC) */ CUSTOMER_NO,ACCT_NO,CURR_VAL
FROM
INVM PARTITION(
"INVM_2018-06-13"
) I
INNER
JOIN
JGDY
ON
JGDY.JGM=I.BRANCH_NO
AND
(JGM=
'1700'
OR
SJJGM=
'1700'
)
AND
I.ACCT_DESC=
'S'
AND
I.CURR_VAL<>0
AND
I.EXTDATE =
DATE
'2018-06-13'
AND
I.CURR_STATUS=
'00'
) C
ON
B.CUSTOMER_NO=C.CUSTOMER_NO
WHERE
(B.P>0
OR
B.LX>0.01);
|
改写完之后SQL由13分钟变为5秒钟执行完，看ID19还要执行999k次，查询ID18谓词信息对应SQL如下，确实是要返回999k行数据。
|
1
2
3
4
|
10:59:23 report.QData>
SELECT
COUNT
(*)
FROM
INVM PARTITION(
"INVM_2018-06-13"
) I
WHERE
I.BRANCH_NO
IN
(
SELECT
JGM
FROM
JGDY
WHERE
JGM=
'1700'
or
SJJGM=
'1700'
)
AND
I.EXTDATE=TO_DATE(
' 2018-06-13 00:00:00'
,
'syyyy-mm-dd hh24:mi:ss'
)
AND
I.CURR_STATUS=
'00'
;
COUNT
(*)
----------
999424
|
