Oracle某行系統SQL優化案例(二)
問題說明：
資料庫遷移到新伺服器和升級後，某一條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#####
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2775858/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 某行系統SQL優化案例(一)OracleSQL優化
- Oracle某行系統SQL優化案例(三)OracleSQL優化
- Oracle某行系統SQL優化(案例五)OracleSQL優化
- Oracle某行系統SQL最佳化(案例四)OracleSQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- oracle效能優化二——作業系統優化Oracle優化作業系統
- Oracle某X系統SQL最佳化(案例六)OracleSQL
- SQL優化案例-定位系統中大量的rollback（十八）SQL優化
- 效能優化案例-SQL優化優化SQL
- SQL優化案例-正確的使用索引（二）SQL優化索引
- 生產系統pl/sql調優案例SQL
- Oracle優化案例-正確的使用索引（二）Oracle優化索引
- MySQL SQL優化案例(一)MySql優化
- SQL效能優化案例分析SQL優化
- Oracle優化案例-復現SQL ordered by Parse Calls（三十二）Oracle優化SQL
- Oracle優化案例-教你線上搞定top cpu的sql（十二）Oracle優化SQL
- oracle sql優化OracleSQL優化
- oracle sql 優化OracleSQL優化
- 使用SQL Profile進行SQL優化案例SQL優化
- SQL優化案例-union代替or（九）SQL優化
- greenplum 簡單sql優化案例SQL優化
- 記一個SQL優化案例SQL優化
- SQL優化案例-使用with as優化Subquery Unnesting（七）SQL優化
- Oracle SQL*Loader使用案例（二）OracleSQL
- Oracle優化案例-（三十四）Oracle優化
- Oracle優化案例-擴充套件統計資訊（十四）Oracle優化套件
- Oracle SQL效能優化OracleSQL優化
- oracle sql 排序優化OracleSQL排序優化
- sql語句的優化案例分析SQL優化
- Oracle優化案例-使用with as優化Subquery Unnesting（七）Oracle優化
- Oracle優化案例-union代替or（九）Oracle優化
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- Oracle優化案例-系統切換引起的enq: SQ - contention（二十八）Oracle優化ENQ
- oracle優化一例之sql優化Oracle優化SQL
- Oracle SQL優化總結OracleSQL優化