20210607-Oracle某行系统SQL优化案例(二)

本文链接：https://blog.csdn.net/chenoracle/article/details/146451928

在这里插入图片描述

问题说明：

数据库迁移到新服务器和升级后，某一条insert缓慢，原库执行不到1秒，新库执行3小时以上也无法完成。

环境说明：

新库：
DB:Oracle 19.3.0.0.0 RAC
OS:Redhat 7.6

原库：
DB:Oracle 11.2.0.4.0 单机
OS:AIX 5.1

问题分析：

处理此类问题，常见思路如下：

1 分别对比原库和新库该SQL对应的执行计划。
2 对比两个执行计划，找到差异部分或最耗时的部分。
3 分析执行计划差异原因，根据原因尝试解决问题。

SQL文本如下：

insert /*+append*/ into T_XX_CJC01
select distinct 'R001258',
                SYSDATE,
                'Y',
                '1',
                'D',
                'F_XX_CJC_INFO',
                'SECURITY_NUMBER',
                'DEAL_NUMBER = ' || t.DEAL_NUMBER || ' and ' || 'SECURITY_NUMBER = 
' || t.SECURITY_NUMBER,
                t.SECURITY_NUMBER,
                'xxxxxxcjcxxxxxx',
                999,
                20210608
  from F_XX_CJC_INFO t
 where not (EXISTS (SELECT 1
                      FROM (SELECT *
                              FROM F_XX_CJC_INFO T
                             WHERE NOT EXISTS
                             (SELECT 1
                                      FROM (SELECT T.*
                                              FROM F_XX_CJC_INFO T
                                             WHERE NOT EXISTS
                                             (SELECT 1
                                                      FROM F_AA_CJC_INFO T1
                                                     WHERE T1.REJECT_FLG IS NULL
                                                       AND T.SECURITY_NUMBER =
                                                           T1.STP_BOND_ID)) COL
                                     WHERE T.DEAL_NUMBER = COL.DEAL_NUMBER
                                       AND T.BRANCH_CODE = COL.BRANCH_CODE
                                       AND T.DEAL_SEQUENCE = COL.DEAL_SEQUENCE)) COL2
                     WHERE T.DEAL_NUMBER = COL2.DEAL_NUMBER
                       AND T.BRANCH_CODE = COL2.BRANCH_CODE
                       AND T.DEAL_SEQUENCE = COL2.DEAL_SEQUENCE))

原因：

其中查询部分执行速度很快，不到1秒，结果集也只有100多条，理论上插入也会很快的，插入慢理论上有如下几个原因：
1 插入的表上有触发器(实际上并没有)。
2 插入的表上有多个索引(实际上索引不多)。
3 查询和插入时的执行计划不一致。

经检查发现：
在19C数据库里，查询和插入时的执行计划不一致，插入时执行计划生成的效率较低。
在11g数据库里，查询和插入时的执行计划一致，执行计划效率高。

11g执行计划如下,效率较高，不到1秒执行完成：

执行计划顺序：8--9--7--6--10--5--4--11--2--1--0
20-----------------------------------------------------------------------------------------------------------
21| Id  | Operation                  | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
22-----------------------------------------------------------------------------------------------------------
23|   0 | INSERT STATEMENT           |                      |       |       |       |  9268 (100)|          |
24|   1 |  LOAD AS SELECT            |                      |       |       |       |            |          |
25|   2 |   HASH UNIQUE              |                      |   254K|  9193K|    11M|  9268   (1)| 00:01:52 |
26|*  3 |    HASH JOIN RIGHT ANTI    |                      |   254K|  9193K|  6280K|  6798   (1)| 00:01:22 |
27|   4 |     VIEW                   | VW_SQ_2              |   247K|  3381K|       |  5070   (1)| 00:01:01 |
28|*  5 |      HASH JOIN RIGHT ANTI  |                      |   247K|  6521K|       |  5070   (1)| 00:01:01 |
29|   6 |       VIEW                 | VW_SQ_1              |  2544 | 33072 |       |  4073   (1)| 00:00:49 |
30|*  7 |        HASH JOIN RIGHT ANTI|                      |  2544 | 89040 |  3496K|  4073   (1)| 00:00:49 |
31|*  8 |         TABLE ACCESS FULL  | F_AA_CJC_INFO        |   148K|  1744K|       |  2481   (1)| 00:00:30 |
32|   9 |         TABLE ACCESS FULL  | F_XX_CJC_INFO        |   254K|  5715K|       |   995   (1)| 00:00:12 |
33|  10 |       TABLE ACCESS FULL    | F_XX_CJC_INFO        |   254K|  3478K|       |   995   (1)| 00:00:12 |
34|  11 |     TABLE ACCESS FULL      | F_XX_CJC_INFO        |   254K|  5715K|       |   995   (1)| 00:00:12 |
35-----------------------------------------------------------------------------------------------------------
36 
37Query Block Name / Object Alias (identified by operation id):
38-------------------------------------------------------------
39 
40   1 - SEL$6BCB5BA5
41   4 - SEL$7BAAFEA6 / VW_SQ_2@SEL$F68621D3
42   5 - SEL$7BAAFEA6
43   6 - SEL$73285923 / VW_SQ_1@SEL$045A7DB7
44   7 - SEL$73285923
45   8 - SEL$73285923 / T1@SEL$6
46   9 - SEL$73285923 / T@SEL$5
47  10 - SEL$7BAAFEA6 / T@SEL$3
48  11 - SEL$6BCB5BA5 / T@SEL$1
49 
50Outline Data
51-------------
52 
53  /*+
54      BEGIN_OUTLINE_DATA
55      IGNORE_OPTIM_EMBEDDED_HINTS
56      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
57      DB_VERSION('11.2.0.4')
58      OPT_PARAM('_optimizer_use_feedback' 'false')
59      ALL_ROWS
60      OUTLINE_LEAF(@"SEL$73285923")
61      OUTLINE_LEAF(@"SEL$7BAAFEA6")
62      OUTLINE_LEAF(@"SEL$6BCB5BA5")
63      UNNEST(@"SEL$DAB909CF")
64      OUTLINE_LEAF(@"INS$1")
65      OUTLINE(@"SEL$F3BB68E7")
66      UNNEST(@"SEL$6")
67      OUTLINE(@"SEL$DAB909CF")
68      UNNEST(@"SEL$F3BB68E7")
69      OUTLINE(@"SEL$F68621D3")
70      OUTLINE(@"SEL$7286615E")
71      MERGE(@"SEL$5")
72      OUTLINE(@"SEL$6")
73      OUTLINE(@"SEL$045A7DB7")
74      OUTLINE(@"SEL$1")
75      OUTLINE(@"SEL$4")
76      OUTLINE(@"SEL$5")
77      OUTLINE(@"SEL$335DD26A")
78      MERGE(@"SEL$3")
79      OUTLINE(@"SEL$2")
80      OUTLINE(@"SEL$3")
81      FULL(@"INS$1" "T_XX_CJC01"@"INS$1")
82      FULL(@"SEL$6BCB5BA5" "T"@"SEL$1")
83      NO_ACCESS(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3")
84      LEADING(@"SEL$6BCB5BA5" "T"@"SEL$1" "VW_SQ_2"@"SEL$F68621D3")
85      USE_HASH(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3")
86      SWAP_JOIN_INPUTS(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3")
87      USE_HASH_AGGREGATION(@"SEL$6BCB5BA5")
88      FULL(@"SEL$7BAAFEA6" "T"@"SEL$3")
89      NO_ACCESS(@"SEL$7BAAFEA6" "VW_SQ_1"@"SEL$045A7DB7")
90      LEADING(@"SEL$7BAAFEA6" "T"@"SEL$3" "VW_SQ_1"@"SEL$045A7DB7")
91      USE_HASH(@"SEL$7BAAFEA6" "VW_SQ_1"@"SEL$045A7DB7")
92      SWAP_JOIN_INPUTS(@"SEL$7BAAFEA6" "VW_SQ_1"@"SEL$045A7DB7")
93      FULL(@"SEL$73285923" "T"@"SEL$5")
94      FULL(@"SEL$73285923" "T1"@"SEL$6")
95      LEADING(@"SEL$73285923" "T"@"SEL$5" "T1"@"SEL$6")
96      USE_HASH(@"SEL$73285923" "T1"@"SEL$6")
97      SWAP_JOIN_INPUTS(@"SEL$73285923" "T1"@"SEL$6")
98      END_OUTLINE_DATA
99  */
100 
101Predicate Information (identified by operation id):
102---------------------------------------------------
103 
104   3 - access("T"."DEAL_NUMBER"="ITEM_4" AND "T"."BRANCH_CODE"="ITEM_5" AND 
105              "T"."DEAL_SEQUENCE"="ITEM_6")
106   5 - access("T"."DEAL_NUMBER"="ITEM_1" AND "T"."BRANCH_CODE"="ITEM_2" AND 
107              "T"."DEAL_SEQUENCE"="ITEM_3")
108   7 - access("T"."SECURITY_NUMBER"="T1"."STP_BOND_ID")
109   8 - filter("T1"."REJECT_FLG" IS NULL)
110 
111Column Projection Information (identified by operation id):
112-----------------------------------------------------------
113 
114   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
115   2 - 'R001258'[7], SYSDATE@![8], 'Y'[1], '1'[1], 'D'[1], 'F_XX_CJC_INFO'[20], 
116       'SECURITY_NUMBER'[15], 'DEAL_NUMBER = '||"T"."DEAL_NUMBER"||' and '||'SECURITY_NUMBER = 
117       '||"T"."SECURITY_NUMBER"[67], "T"."SECURITY_NUMBER"[VARCHAR2,20], 
118       'xxxxxxcjcxxxxxx'[66], 999[3], 20210608[5]
119   3 - (#keys=3) "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."BRANCH_CODE"[VARCHAR2,2], 
120       "T"."DEAL_SEQUENCE"[VARCHAR2,3], "T"."SECURITY_NUMBER"[VARCHAR2,20]
121   4 - "ITEM_4"[VARCHAR2,10], "ITEM_5"[VARCHAR2,2], "ITEM_6"[VARCHAR2,3]
122   5 - (#keys=3) "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."BRANCH_CODE"[VARCHAR2,2], 
123       "T"."DEAL_SEQUENCE"[VARCHAR2,3]
124   6 - "ITEM_1"[VARCHAR2,10], "ITEM_2"[VARCHAR2,2], "ITEM_3"[VARCHAR2,3]
125   7 - (#keys=1) "T"."SECURITY_NUMBER"[VARCHAR2,20], "T"."DEAL_NUMBER"[VARCHAR2,10], 
126       "T"."DEAL_SEQUENCE"[VARCHAR2,3], "T"."BRANCH_CODE"[VARCHAR2,2]
127   8 - "T1"."STP_BOND_ID"[VARCHAR2,20], "T1"."REJECT_FLG"[VARCHAR2,1]
128   9 - "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
129       "T"."BRANCH_CODE"[VARCHAR2,2], "T"."SECURITY_NUMBER"[VARCHAR2,20]
130  10 - "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
131       "T"."BRANCH_CODE"[VARCHAR2,2]
132  11 - "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
133       "T"."BRANCH_CODE"[VARCHAR2,2], "T"."SECURITY_NUMBER"[VARCHAR2,20]
134

19C执行计划如下，效率差，3小时执行不完：

执行计划顺序：13--12--14--11--10--9--8--7---6--5--4--3--2--1--0
20-------------------------------------------------------------------------------------------------------------------------------
21| Id  | Operation                                   | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
22-------------------------------------------------------------------------------------------------------------------------------
23|   0 | INSERT STATEMENT                            |                         |       |       |       |  1893M(100)|          |
24|   1 |  LOAD AS SELECT                             | T_XX_CJC01          |       |       |       |            |          |
25|   2 |   OPTIMIZER STATISTICS GATHERING            |                         |   255K|  7725K|       |  1893M  (1)| 20:32:59 |
26|   3 |    HASH UNIQUE                              |                         |   255K|  7725K|    10M|  1893M  (1)| 20:32:59 |
27|   4 |     NESTED LOOPS ANTI                       |                         |   255K|  7725K|       |  1893M  (1)| 20:32:59 |
28|   5 |      TABLE ACCESS FULL                      | F_XX_CJC_INFO    |   255K|  5731K|       |  1025   (1)| 00:00:01 |
29|*  6 |      VIEW PUSHED PREDICATE                  | VW_SQ_2                 |     1 |     8 |       |  7421   (1)| 00:00:01 |
30|   7 |       NESTED LOOPS ANTI                     |                         |     1 |    22 |       |  7421   (1)| 00:00:01 |
31|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED  | F_XX_CJC_INFO    |     3 |    42 |       |     4   (0)| 00:00:01 |
32|*  9 |         INDEX RANGE SCAN                    | PK_F_XX_CJC_INFO |     3 |       |       |     3   (0)| 00:00:01 |
33|* 10 |        VIEW PUSHED PREDICATE                | VW_SQ_1                 |     1 |     8 |       |  2472   (1)| 00:00:01 |
34|* 11 |         HASH JOIN ANTI                      |                         |     1 |    35 |       |  2472   (1)| 00:00:01 |
35|  12 |          TABLE ACCESS BY INDEX ROWID BATCHED| F_XX_CJC_INFO    |     3 |    69 |       |     4   (0)| 00:00:01 |
36|* 13 |           INDEX RANGE SCAN                  | PK_F_XX_CJC_INFO |     3 |       |       |     3   (0)| 00:00:01 |
37|* 14 |          TABLE ACCESS FULL                  | F_AA_CJC_INFO          |   149K|  1750K|       |  2468   (1)| 00:00:01 |
38-------------------------------------------------------------------------------------------------------------------------------
39 
40Query Block Name / Object Alias (identified by operation id):
41-------------------------------------------------------------
42 
43   1 - SEL$6BCB5BA5
44   5 - SEL$6BCB5BA5 / T@SEL$1
45   6 - SEL$B4965BFE / VW_SQ_2@SEL$F68621D3
46   7 - SEL$B4965BFE
47   8 - SEL$B4965BFE / T@SEL$3
48   9 - SEL$B4965BFE / T@SEL$3
49  10 - SEL$064103CD / VW_SQ_1@SEL$045A7DB7
50  11 - SEL$064103CD
51  12 - SEL$064103CD / T@SEL$5
52  13 - SEL$064103CD / T@SEL$5
53  14 - SEL$064103CD / T1@SEL$6
54 
55Outline Data
56-------------
57 
58  /*+
59      BEGIN_OUTLINE_DATA
60      IGNORE_OPTIM_EMBEDDED_HINTS
61      OPTIMIZER_FEATURES_ENABLE('19.1.0')
62      DB_VERSION('19.1.0')
63      OPT_PARAM('_b_tree_bitmap_plans' 'false')
64      OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
65      OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
66      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
67      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
68      OPT_PARAM('_optimizer_use_feedback' 'false')
69      ALL_ROWS
70      OUTLINE_LEAF(@"SEL$064103CD")
71      PUSH_PRED(@"SEL$7BAAFEA6" "VW_SQ_1"@"SEL$045A7DB7" 1)
72      OUTLINE_LEAF(@"SEL$B4965BFE")
73      PUSH_PRED(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3" 1)
74      OUTLINE_LEAF(@"SEL$6BCB5BA5")
75      UNNEST(@"SEL$DAB909CF")
76      OUTLINE_LEAF(@"INS$1")
77      OUTLINE(@"SEL$73285923")
78      OUTLINE(@"SEL$7BAAFEA6")
79      OUTLINE(@"SEL$F68621D3")
80      OUTLINE(@"SEL$DAB909CF")
81      UNNEST(@"SEL$F3BB68E7")
82      OUTLINE(@"SEL$F3BB68E7")
83      UNNEST(@"SEL$6")
84      OUTLINE(@"SEL$1")
85      OUTLINE(@"SEL$045A7DB7")
86      OUTLINE(@"SEL$7286615E")
87      MERGE(@"SEL$5" >"SEL$4")
88      OUTLINE(@"SEL$6")
89      OUTLINE(@"SEL$335DD26A")
90      MERGE(@"SEL$3" >"SEL$2")
91      OUTLINE(@"SEL$4")
92      OUTLINE(@"SEL$5")
93      OUTLINE(@"SEL$2")
94      OUTLINE(@"SEL$3")
95      FULL(@"INS$1" "T_XX_CJC01"@"INS$1")
96      FULL(@"SEL$6BCB5BA5" "T"@"SEL$1")
97      NO_ACCESS(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3")
98      LEADING(@"SEL$6BCB5BA5" "T"@"SEL$1" "VW_SQ_2"@"SEL$F68621D3")
99      USE_NL(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3")
100      USE_HASH_AGGREGATION(@"SEL$6BCB5BA5")
101      INDEX_RS_ASC(@"SEL$B4965BFE" "T"@"SEL$3" ("F_XX_CJC_INFO"."DEAL_NUMBER" 
102              "F_XX_CJC_INFO"."SECURITY_NUMBER"))
103      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B4965BFE" "T"@"SEL$3")
104      NO_ACCESS(@"SEL$B4965BFE" "VW_SQ_1"@"SEL$045A7DB7")
105      LEADING(@"SEL$B4965BFE" "T"@"SEL$3" "VW_SQ_1"@"SEL$045A7DB7")
106      USE_NL(@"SEL$B4965BFE" "VW_SQ_1"@"SEL$045A7DB7")
107      INDEX_RS_ASC(@"SEL$064103CD" "T"@"SEL$5" ("F_XX_CJC_INFO"."DEAL_NUMBER" 
108              "F_XX_CJC_INFO"."SECURITY_NUMBER"))
109      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$064103CD" "T"@"SEL$5")
110      FULL(@"SEL$064103CD" "T1"@"SEL$6")
111      LEADING(@"SEL$064103CD" "T"@"SEL$5" "T1"@"SEL$6")
112      USE_HASH(@"SEL$064103CD" "T1"@"SEL$6")
113      END_OUTLINE_DATA
114  */
115 
116Predicate Information (identified by operation id):
117---------------------------------------------------
118 
119   6 - filter(("T"."BRANCH_CODE"="ITEM_5" AND "T"."DEAL_SEQUENCE"="ITEM_6"))
120   9 - access("T"."DEAL_NUMBER"="T"."DEAL_NUMBER")
121  10 - filter(("T"."BRANCH_CODE"="ITEM_2" AND "T"."DEAL_SEQUENCE"="ITEM_3"))
122  11 - access("T"."SECURITY_NUMBER"="T1"."STP_BOND_ID")
123  13 - access("T"."DEAL_NUMBER"="T"."DEAL_NUMBER")
124  14 - filter("T1"."REJECT_FLG" IS NULL)
125 
126Column Projection Information (identified by operation id):
127-----------------------------------------------------------
128 
129   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
130   2 - 'R001258'[7], SYSDATE@![8], 'Y'[1], '1'[1], 'D'[1], 'F_XX_CJC_INFO'[20], 'SECURITY_NUMBER'[15], 
131       'DEAL_NUMBER = '||"T"."DEAL_NUMBER"||' and '||'SECURITY_NUMBER = '||"T"."SECURITY_NUMBER"[67], 
132       "T"."SECURITY_NUMBER"[VARCHAR2,20], 'xxxxxxcjcxxxxxx'[66], 999[3], 20210608[5]
133   3 - 'R001258'[7], SYSDATE@![8], 'Y'[1], '1'[1], 'D'[1], 'F_XX_CJC_INFO'[20], 'SECURITY_NUMBER'[15], 
134       'DEAL_NUMBER = '||"T"."DEAL_NUMBER"||' and '||'SECURITY_NUMBER = '||"T"."SECURITY_NUMBER"[67], 
135       "T"."SECURITY_NUMBER"[VARCHAR2,20], 'xxxxxxcjcxxxxxx'[66], 999[3], 20210608[5]
136   4 - "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], "T"."BRANCH_CODE"[VARCHAR2,2], 
137       "T"."SECURITY_NUMBER"[VARCHAR2,20]
138   5 - "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], "T"."BRANCH_CODE"[VARCHAR2,2], 
139       "T"."SECURITY_NUMBER"[VARCHAR2,20]
140   6 - "ITEM_5"[VARCHAR2,2], "ITEM_6"[VARCHAR2,3]
141   7 - "T".ROWID[ROWID,10], "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
142       "T"."BRANCH_CODE"[VARCHAR2,2]
143   8 - "T".ROWID[ROWID,10], "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
144       "T"."BRANCH_CODE"[VARCHAR2,2]
145   9 - "T".ROWID[ROWID,10], "T"."DEAL_NUMBER"[VARCHAR2,10]
146  10 - "ITEM_2"[VARCHAR2,2], "ITEM_3"[VARCHAR2,3]
147  11 - (#keys=1) "T"."SECURITY_NUMBER"[VARCHAR2,20], "T".ROWID[ROWID,10], "T"."DEAL_NUMBER"[VARCHAR2,10], 
148       "T"."DEAL_SEQUENCE"[VARCHAR2,3], "T"."BRANCH_CODE"[VARCHAR2,2]
149  12 - "T".ROWID[ROWID,10], "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
150       "T"."BRANCH_CODE"[VARCHAR2,2], "T"."SECURITY_NUMBER"[VARCHAR2,20]
151  13 - "T".ROWID[ROWID,10], "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."SECURITY_NUMBER"[VARCHAR2,20]
152  14 - "T1"."STP_BOND_ID"[VARCHAR2,20], "T1"."REJECT_FLG"[VARCHAR2,1]
153 
154Hint Report (identified by operation id / Query Block Name / Object Alias):
155Total hints for statement: 1 (E - Syntax error (1))
156---------------------------------------------------------------------------
157 
158   0 -  INS$1
159         E -  xxxsssxxx
160

优化方式：

对比两个执行计划，发现执行差的执行计划发生了VIEW PUSHED PREDICATE，进而导致使用NESTED LOOPS ANTI方式进行表关联。
检查统计信息没有问题。
1 加hint，关联强制走hash join
试图通过强制走索引使执行计划和11g的一样，实际上没有效果。
2 加hint，禁用VIEW PUSHED PREDICATE
试图通过禁用谓词推入使执行计划和11g的一样，实际上没有效果。
3 通过10053查看SQL改写情况
执行10053没有自动生成trace，可能需要清空共享池，生产环境，不能操作。
4 sql tuning advisor
也可以看看oracle的建议。
5 降级OPTIMIZER_FEATURES_ENABLE
突然想到几年前还在某友时参与过11g升级到12C后，也是有部分SQL效率变慢，当时怀疑是12C数据库优化器有BUG，临时解决方案是把compatible降级后解决的问题。
本次案例也比较类似，查看19C的compatible参数。

SQL> show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0
noncdb_compatible                    boolean     FALSE

将compatible在语句级别由19.0.0降级到11.2.0.4.0，再次执行，SQL可以在1秒内执行完成，执行计划也和11g执行计划一致了。

优化后SQL如下：

insert /*+append OPTIMIZER_FEATURES_ENABLE('11.2.0.4')*/  into T_XX_CJC01
select distinct 'R001258',
                SYSDATE,
                'Y',
                '1',
                'D',
                'F_XX_CJC_INFO',
                'SECURITY_NUMBER',
                'DEAL_NUMBER = ' || t.DEAL_NUMBER || ' and ' || 'SECURITY_NUMBER = 
' || t.SECURITY_NUMBER,
                t.SECURITY_NUMBER,
                'xxxxxxcjcxxxxxx',
                999,
                20210608
  from F_XX_CJC_INFO t
 where not (EXISTS (SELECT 1
                      FROM (SELECT *
                              FROM F_XX_CJC_INFO T
                             WHERE NOT EXISTS
                             (SELECT 1
                                      FROM (SELECT T.*
                                              FROM F_XX_CJC_INFO T
                                             WHERE NOT EXISTS
                                             (SELECT 1
                                                      FROM F_AA_CJC_INFO T1
                                                     WHERE T1.REJECT_FLG IS NULL
                                                       AND T.SECURITY_NUMBER =
                                                           T1.STP_BOND_ID)) COL
                                     WHERE T.DEAL_NUMBER = COL.DEAL_NUMBER
                                       AND T.BRANCH_CODE = COL.BRANCH_CODE
                                       AND T.DEAL_SEQUENCE = COL.DEAL_SEQUENCE)) COL2
                     WHERE T.DEAL_NUMBER = COL2.DEAL_NUMBER
                       AND T.BRANCH_CODE = COL2.BRANCH_CODE
                       AND T.DEAL_SEQUENCE = COL2.DEAL_SEQUENCE))

优化后SQL执行计划如下：

20-----------------------------------------------------------------------------------------------------------
21| Id  | Operation                  | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
22-----------------------------------------------------------------------------------------------------------
23|   0 | INSERT STATEMENT           |                      |       |       |       |  9336 (100)|          |
24|   1 |  LOAD AS SELECT            | T_XX_CJC01       |       |       |       |            |          |
25|   2 |   HASH UNIQUE              |                      |   255K|  9220K|    11M|  9336   (1)| 00:00:01 |
26|*  3 |    HASH JOIN RIGHT ANTI    |                      |   255K|  9220K|  6304K|  6871   (1)| 00:00:01 |
27|   4 |     VIEW                   | VW_SQ_2              |   248K|  3392K|       |  5114   (1)| 00:00:01 |
28|*  5 |      HASH JOIN RIGHT ANTI  |                      |   248K|  6541K|       |  5114   (1)| 00:00:01 |
29|   6 |       VIEW                 | VW_SQ_1              |  2552 | 33176 |       |  4089   (1)| 00:00:01 |
30|*  7 |        HASH JOIN RIGHT ANTI|                      |  2552 | 89320 |  3504K|  4089   (1)| 00:00:01 |
31|*  8 |         TABLE ACCESS FULL  | F_AA_CJC_INFO       |   149K|  1750K|       |  2468   (1)| 00:00:01 |
32|   9 |         TABLE ACCESS FULL  | F_XX_CJC_INFO |   255K|  5731K|       |  1025   (1)| 00:00:01 |
33|  10 |       TABLE ACCESS FULL    | F_XX_CJC_INFO |   255K|  3488K|       |  1025   (1)| 00:00:01 |
34|  11 |     TABLE ACCESS FULL      | F_XX_CJC_INFO |   255K|  5731K|       |  1025   (1)| 00:00:01 |
35-----------------------------------------------------------------------------------------------------------
36 
37Query Block Name / Object Alias (identified by operation id):
38-------------------------------------------------------------
39 
40   1 - SEL$6BCB5BA5
41   4 - SEL$7BAAFEA6 / VW_SQ_2@SEL$F68621D3
42   5 - SEL$7BAAFEA6
43   6 - SEL$73285923 / VW_SQ_1@SEL$045A7DB7
44   7 - SEL$73285923
45   8 - SEL$73285923 / T1@SEL$6
46   9 - SEL$73285923 / T@SEL$5
47  10 - SEL$7BAAFEA6 / T@SEL$3
48  11 - SEL$6BCB5BA5 / T@SEL$1
49 
50Outline Data
51-------------
52 
53  /*+
54      BEGIN_OUTLINE_DATA
55      IGNORE_OPTIM_EMBEDDED_HINTS
56      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
57      DB_VERSION('19.1.0')
58      ALL_ROWS
59      OUTLINE_LEAF(@"SEL$73285923")
60      OUTLINE_LEAF(@"SEL$7BAAFEA6")
61      OUTLINE_LEAF(@"SEL$6BCB5BA5")
62      UNNEST(@"SEL$DAB909CF")
63      OUTLINE_LEAF(@"INS$1")
64      OUTLINE(@"SEL$F3BB68E7")
65      UNNEST(@"SEL$6")
66      OUTLINE(@"SEL$DAB909CF")
67      UNNEST(@"SEL$F3BB68E7")
68      OUTLINE(@"SEL$F68621D3")
69      OUTLINE(@"SEL$7286615E")
70      MERGE(@"SEL$5" >"SEL$4")
71      OUTLINE(@"SEL$6")
72      OUTLINE(@"SEL$045A7DB7")
73      OUTLINE(@"SEL$1")
74      OUTLINE(@"SEL$4")
75      OUTLINE(@"SEL$5")
76      OUTLINE(@"SEL$335DD26A")
77      MERGE(@"SEL$3" >"SEL$2")
78      OUTLINE(@"SEL$2")
79      OUTLINE(@"SEL$3")
80      FULL(@"INS$1" "T_XX_CJC01"@"INS$1")
81      FULL(@"SEL$6BCB5BA5" "T"@"SEL$1")
82      NO_ACCESS(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3")
83      LEADING(@"SEL$6BCB5BA5" "T"@"SEL$1" "VW_SQ_2"@"SEL$F68621D3")
84      USE_HASH(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3")
85      SWAP_JOIN_INPUTS(@"SEL$6BCB5BA5" "VW_SQ_2"@"SEL$F68621D3")
86      USE_HASH_AGGREGATION(@"SEL$6BCB5BA5")
87      FULL(@"SEL$7BAAFEA6" "T"@"SEL$3")
88      NO_ACCESS(@"SEL$7BAAFEA6" "VW_SQ_1"@"SEL$045A7DB7")
89      LEADING(@"SEL$7BAAFEA6" "T"@"SEL$3" "VW_SQ_1"@"SEL$045A7DB7")
90      USE_HASH(@"SEL$7BAAFEA6" "VW_SQ_1"@"SEL$045A7DB7")
91      SWAP_JOIN_INPUTS(@"SEL$7BAAFEA6" "VW_SQ_1"@"SEL$045A7DB7")
92      FULL(@"SEL$73285923" "T"@"SEL$5")
93      FULL(@"SEL$73285923" "T1"@"SEL$6")
94      LEADING(@"SEL$73285923" "T"@"SEL$5" "T1"@"SEL$6")
95      USE_HASH(@"SEL$73285923" "T1"@"SEL$6")
96      SWAP_JOIN_INPUTS(@"SEL$73285923" "T1"@"SEL$6")
97      END_OUTLINE_DATA
98  */
99 
100Predicate Information (identified by operation id):
101---------------------------------------------------
102 
103   3 - access("T"."DEAL_NUMBER"="ITEM_4" AND "T"."BRANCH_CODE"="ITEM_5" AND 
104              "T"."DEAL_SEQUENCE"="ITEM_6")
105   5 - access("T"."DEAL_NUMBER"="ITEM_1" AND "T"."BRANCH_CODE"="ITEM_2" AND 
106              "T"."DEAL_SEQUENCE"="ITEM_3")
107   7 - access("T"."SECURITY_NUMBER"="T1"."STP_BOND_ID")
108   8 - filter("T1"."REJECT_FLG" IS NULL)
109 
110Column Projection Information (identified by operation id):
111-----------------------------------------------------------
112 
113   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
114   2 - 'R001258'[7], SYSDATE@![8], 'Y'[1], '1'[1], 'D'[1], 'F_XX_CJC_INFO'[20], 
115       'SECURITY_NUMBER'[15], 'DEAL_NUMBER = '||"T"."DEAL_NUMBER"||' and '||'SECURITY_NUMBER = 
116       '||"T"."SECURITY_NUMBER"[67], "T"."SECURITY_NUMBER"[VARCHAR2,20], 
117       'xxxxxxcjcxxxxxx'[66], 999[3], 20210608[5]
118   3 - (#keys=3; rowset=256) "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."BRANCH_CODE"[VARCHAR2,2], 
119       "T"."DEAL_SEQUENCE"[VARCHAR2,3], "T"."SECURITY_NUMBER"[VARCHAR2,20]
120   4 - (rowset=256) "ITEM_4"[VARCHAR2,10], "ITEM_5"[VARCHAR2,2], "ITEM_6"[VARCHAR2,3]
121   5 - (#keys=3; rowset=256) "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."BRANCH_CODE"[VARCHAR2,2], 
122       "T"."DEAL_SEQUENCE"[VARCHAR2,3]
123   6 - (rowset=256) "ITEM_1"[VARCHAR2,10], "ITEM_2"[VARCHAR2,2], "ITEM_3"[VARCHAR2,3]
124   7 - (#keys=1; rowset=256) "T"."SECURITY_NUMBER"[VARCHAR2,20], "T"."DEAL_NUMBER"[VARCHAR2,10], 
125       "T"."DEAL_SEQUENCE"[VARCHAR2,3], "T"."BRANCH_CODE"[VARCHAR2,2]
126   8 - (rowset=256) "T1"."STP_BOND_ID"[VARCHAR2,20], "T1"."REJECT_FLG"[VARCHAR2,1]
127   9 - (rowset=256) "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
128       "T"."BRANCH_CODE"[VARCHAR2,2], "T"."SECURITY_NUMBER"[VARCHAR2,20]
129  10 - (rowset=256) "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
130       "T"."BRANCH_CODE"[VARCHAR2,2]
131  11 - (rowset=256) "T"."DEAL_NUMBER"[VARCHAR2,10], "T"."DEAL_SEQUENCE"[VARCHAR2,3], 
132       "T"."BRANCH_CODE"[VARCHAR2,2], "T"."SECURITY_NUMBER"[VARCHAR2,20]
133 
134Hint Report (identified by operation id / Query Block Name / Object Alias):
135Total hints for statement: 1
136---------------------------------------------------------------------------
137 
138   0 -  STATEMENT
139           -  OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

#####chenjuchao 20210607 20:08#####
欢迎关注我的公众号《IT小Chen》