記錄一次關于標量子查詢優(yōu)化SQL的案例
前言
其實寫SQL優(yōu)化的實戰(zhàn)很難，主要是很多情況是需要結合業(yè)務，而我們很多開發(fā)人員為了滿足業(yè)務的需求隨心隨意的寫SQL，就產生了很多質量較差的SQL，這些SQL可能在開發(fā)測試數據量不大的環(huán)境中正常運行，可是一到生產環(huán)境就直接把整個數據庫服務器資源打滿。所以我們平時在寫SQL過程中多做簡化工作，盡量讓SQL寫的讓別人能讀懂。多考慮條件的過濾包括關鍵字段索引等等，查詢的數據量少了，減少了全表掃描了，自然效率也就高了。最后，要滿足業(yè)務的需求又要寫出易懂高質量的SQL。這其實是一個需要權衡的過程、需要做取舍。
問題現象
運維人員反饋EBS系統(tǒng)有一個請求運行時間很長，每次運行需要10多分鐘。影響數據庫整體性能和業(yè)務開展。
分析SQL
通過跟蹤請求，拿到sql_id:gcppkx5y05x3k，把SQL拿出來大致看一下。 初步梳理，這一條非常復雜的插入SQL。看到一共有插入23個字段，主體SQL關聯7個表和4個子查詢。如下：
INSERT INTO CUX_TWOVOTE_SALEINVOICE_NEW (SUPPLIERID, SALINVOICENO, SALINVDATE, SALSUMVALUE, SALINVTYPE, SALCSTCODE, SALCSTNAME, GOODS, GOODNAME, VAT_HEADER_ID, VAT_LINE_ID, PO_LINE_ID, SOURCE_PO_LINE_ID, PO_HEADER_ID, SOURCE_PO_HEADER_ID, PO_NUMBER, ORG_ID, INTER_ORG_ID, ITEM_ID, LOT_NUMBER, REQUEST_ID, ATTRIBUTE1, ATTRIBUTE2)
SELECT CAVL.DEPARTMENT_CODE,
DECODE(
(SELECT COUNT(1)
FROM CUX_SC_MINHANG_CLIENT
WHERE ACCOUNT_NUMBER = CAVH.CUSTOMER_NUMBER), 0, CAVH.VAT_NUMBER, DECODE(INSTR(CAVH.INVOICE_CATEGORY, '全電'), 0, CAVH.VAT_INVOICE_CODE || CAVH.VAT_NUMBER, CAVH.VAT_NUMBER)) VAT_NUMBER,
TO_CHAR(CAVH.VAT_DATE, 'yyyy-mm-dd') VAT_DATE,
CAVH.AMOUNT_INC_TAX SALSUMVALUE,
COPL.PROPERTY7_NAME SALINVTYPE,
CAVH.CUSTOMER_NUMBER,
CAVH.CUSTOMER_NAME,
CAVL.ITEM_CODE || '|' || CAVL.PACKAGE_NUM ITEM_CODE,
CAVL.ITEM_DESC,
CAVH.VAT_HEADER_ID,
CAVL.VAT_LINE_ID,
DECODE(
(SELECT COUNT(1)
FROM CUX_OU_PROPERTY_LINES_T COUL, PO_LINES_ALL PL, PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.VENDOR_ID = COUL.ITEM_ID
AND PL.PO_LINE_ID = MLN.N_ATTRIBUTE6
AND COUL.PROPERTY_TYPE = 'V'
AND NVL(COUL.PROPERTY5_NAME, 'N') <> 'N'), 0, MLN.N_ATTRIBUTE6, NVL(COMR.PO_LINE_ID, MLN.N_ATTRIBUTE6)) PO_LINE_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE6
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.N_ATTRIBUTE6
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE6, COMR.PO_LINE_ID)
END AS SOURCE_PO_LINE_ID,
MLN.N_ATTRIBUTE5 PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE5
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.N_ATTRIBUTE5
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE5, COMR.PO_HEADER_ID)
END AS SOURCE_PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.C_ATTRIBUTE1
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.C_ATTRIBUTE1
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.C_ATTRIBUTE1, '')
END AS PO_NUMBER,
COMR.ORG_ID,
COMR.INTERCOMPANY_ORG_ID,
CAVL.ITEM_ID,
CAVL.LOT_NUMBER,
:B1,
CAVH.VAT_INVOICE_CODE,
CAVH.VAT_NUMBER
FROM CUX_AR_VAT_HEADERS CAVH,
CUX_AR_VAT_LINES CAVL,
CUX_OE_MTL_RESERVATIONS COMR,
CUX_OE_ORDER_HEADERS COOH,
MTL_LOT_NUMBERS MLN,
CUX_MST_ITEM ISI,
CUX_FND_LOOKUP_VALUES SCP,
(SELECT ITEM_ID,
PROPERTY7_NAME,
OU_ID
FROM CUX_OU_PROPERTY_LINES_T
WHERE PROPERTY_TYPE = 'C'
AND NVL(PROPERTY7_NAME, 'N') <> 'N') COPL,
(SELECT T.TRANSACTION_TYPE_ID
FROM OE_TRANSACTION_TYPES_ALL TT,
OE_TRANSACTION_TYPES_TL T,
FND_DOCUMENT_SEQUENCES A
WHERE TT.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
AND T.LANGUAGE = USERENV('LANG')
AND TT.ATTRIBUTE1 = A.DOC_SEQUENCE_ID
AND TT.TRANSACTION_TYPE_CODE = 'ORDER'
AND TT.ATTRIBUTE15 <> '01') TT_TYPE,
(SELECT HOU.ORGANIZATION_ID,
FLV.MEANING
FROM HR_ORGANIZATION_UNITS_V HOU,
CUX_FND_LOOKUP_VALUES FLV
WHERE HOU.ATTRIBUTE6 = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'MST-0031'
AND HOU.ATTRIBUTE6 IS NOT NULL) COM1,
(SELECT HOU.ORGANIZATION_ID,
FLV.MEANING
FROM HR_ORGANIZATION_UNITS_V HOU,
CUX_FND_LOOKUP_VALUES FLV
WHERE HOU.ATTRIBUTE6 = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'MST-0031'
AND HOU.ATTRIBUTE6 IS NOT NULL) COM2
WHERE CAVH.VAT_HEADER_ID = CAVL.VAT_HEADER_ID
AND CAVH.VAT_HEADER_ID = COMR.VAT_HEADER_ID
AND COMR.HEADER_ID = COOH.HEADER_ID
AND COMR.SOURCE_LINE_ID = CAVL.ORDER_LINE_ID
AND COMR.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID
AND COMR.ORGANIZATION_ID = MLN.ORGANIZATION_ID
AND COMR.LOT_NUMBER = MLN.LOT_NUMBER
AND COMR.INVENTORY_ITEM_ID = ISI.INVENTORY_ITEM_ID
AND COMR.ORGANIZATION_ID = ISI.ORGANIZATION_ID
AND ISI.OPERATE_SCOPE = SCP.LOOKUP_CODE
AND SCP.LOOKUP_TYPE = 'MST-0004'
AND SCP.SEGMENT4 = 'Y'
AND COOH.ORDER_TYPE_ID = TT_TYPE.TRANSACTION_TYPE_ID
AND NVL(COOH.ORDER_SOURCE_REFERENCE, 'N') NOT IN ('QUICK_ORDER',
'QUICK_RET')
AND CAVH.CUSTOMER_ID = COPL.ITEM_ID
AND CAVH.ORG_ID = COPL.OU_ID
AND COMR.ORG_ID = COM1.ORGANIZATION_ID
AND COMR.INTERCOMPANY_ORG_ID = COM2.ORGANIZATION_ID
AND CAVH.ENABLED_FLAG = 'Y'
AND UPPER(CAVH.VAT_NUMBER) NOT LIKE 'X%'
AND UPPER(CAVH.VAT_NUMBER) NOT LIKE 'D%'
AND CAVH.VAT_NUMBER IS NOT NULL
AND CAVH.AMOUNT_INC_TAX > 0
AND CAVL.AMOUNT_INC_TAX > 0
AND MLN.C_ATTRIBUTE4 NOT IN
(SELECT MEANING
FROM CUX_FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AP-013'
AND TAG = 'SUP'
AND SEGMENT1 = 'Y'
AND ENABLED_FLAG = 'Y')
AND TRUNC(CAVH.LAST_UPDATE_DATE) >= TRUNC(SYSDATE - 1)
AND NOT EXISTS
(SELECT 1
FROM CUX_TWOVOTE_SALEINVOICE_NEW
WHERE VAT_HEADER_ID = CAVH.VAT_HEADER_ID
AND TRUNC(IMPORT_DATE) >= TRUNC(SYSDATE - 1))
使用SQLHC（SQL Tuning Health-Check）工具得到執(zhí)行計劃如下圖
由于SQL復雜，對表和字段的定義無法從業(yè)務側得知，如何取舍優(yōu)化。只能從DBA角度來添加索引或者改寫SQL逐步分析。
先對SELECT的23個字段部分開始：
上圖執(zhí)行計劃里Id從12~26，寫了多個標量子查詢回填同幾張表（MTL_LOT_NUMBERS，CUX_OE_MTL_RESERVATIONS）：等于重復訪問同一數據源。如下部分
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE6
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.N_ATTRIBUTE6
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE6, COMR.PO_LINE_ID)
END AS SOURCE_PO_LINE_ID,
MLN.N_ATTRIBUTE5 PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE5
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.N_ATTRIBUTE5
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE5, COMR.PO_HEADER_ID)
END AS SOURCE_PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.C_ATTRIBUTE1
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.C_ATTRIBUTE1
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.C_ATTRIBUTE1, '')
END AS PO_NUMBER,
在查看標量子查詢中表的數據量非常大，會造成大量的等待消耗。
SQL> select count(1) from CUX.CUX_OE_MTL_RESERVATIONS;
COUNT(1)
----------
45514896
SQL> select count(1) from INV.MTL_LOT_NUMBERS;
COUNT(1)
----------
11859933
SQL>
解釋一下什么是標量子查詢當一個子查詢介于SELECT與FROM之間，這種子查詢就叫標量子查詢。
標量子查詢類似一個天然的嵌套循環(huán)，而且驅動表固定為主表，嵌套循環(huán)被驅動表的連接列必須包含在索引中。同理，標量子查詢中子查詢的表連接列也必須包含在索引中。
建議在實際生產工作中，盡量避免使用標量子查詢。原因是：假如主表返回大量數據，主表的連接列基數又很高，那么子查詢中的表會被多次掃描，從而嚴重影響SQL性能；如果主表數據量小，或者主表的連接列基數很低，那么這個時候也可以使用標量子查詢，但要給子查詢中表的連接列建立索引。
當SQL里面有標量子查詢，可以將標量子查詢等價改寫為外連接，從而使它們可以進行HASH連接。
- 標量子查詢=外層每一行都要去子表再查一次
- 外層行數一大 + 子表一大 ? 執(zhí)行次數爆炸
執(zhí)行計劃中Id第51行部分CUX_OU_PROPERTY_LINES_T全表掃描
再看WHERE條件部分Id在100行部分，查看CUX_TWOVOTE_SALEINVOICE_NEW的數據量也非常大。走全表掃描也非常消耗IO
SQL> select count(1) from CUX.CUX_TWOVOTE_SALEINVOICE_NEW;
COUNT(1)
----------
15734505
SQL>
優(yōu)化SQL
在我們通過執(zhí)行計劃加上表數據量和索引等信息分析后，做如下處理。
把SELECT后重復關聯標量子查詢3個字段SOURCE_PO_LINE_ID、SOURCE_PO_HEADER_ID、PO_NUMBER，通過left join方式，避免重復的掃描，改寫后重復部分的子查詢作為主表，如下：
SELECT
...
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE6
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.N_ATTRIBUTE6
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE6, COMR.PO_LINE_ID)
END AS SOURCE_PO_LINE_ID,
MLN.N_ATTRIBUTE5 PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE5
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.N_ATTRIBUTE5
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE5, COMR.PO_HEADER_ID)
END AS SOURCE_PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.C_ATTRIBUTE1
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.C_ATTRIBUTE1
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.C_ATTRIBUTE1, '')
END AS PO_NUMBER,
...
FROM
...
(SELECT COMR_SOURCE.RELATE_PO_LINE_ID,
MLN_SOURCE.N_ATTRIBUTE6,
MLN_SOURCE.N_ATTRIBUTE5,
MLN_SOURCE.C_ATTRIBUTE1
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID) MLN_SOURCE
WHERE
...
AND MLN.N_ATTRIBUTE6 = MLN_SOURCE.RELATE_PO_LINE_ID(+)
CUX_OU_PROPERTY_LINES_T全表掃描
DECODE(
(SELECT COUNT(1)
FROM CUX_OU_PROPERTY_LINES_T COUL, PO_LINES_ALL PL, PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.VENDOR_ID = COUL.ITEM_ID
AND PL.PO_LINE_ID = MLN.N_ATTRIBUTE6
AND COUL.PROPERTY_TYPE = 'V'
AND NVL(COUL.PROPERTY5_NAME, 'N') <> 'N'), 0, MLN.N_ATTRIBUTE6, NVL(COMR.PO_LINE_ID, MLN.N_ATTRIBUTE6)) PO_LINE_ID,
可以在PROPERTY_TYPE字段上添加索引，并重新統(tǒng)計信息：
SQL> create index CUX.CUX_OU_PROPERTY_LINES_T_N03 on CUX.CUX_OU_PROPERTY_LINES_T (PROPERTY_TYPE)
tablespace CUXD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'CUX',
tabname => 'CUX_OU_PROPERTY_LINES_T',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => TRUE,
no_invalidate => FALSE
);
END;
/ 2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL>
WHERE條件后的CUX_TWOVOTE_SALEINVOICE_NEW全表掃描
AND NOT EXISTS
(SELECT 1
FROM CUX_TWOVOTE_SALEINVOICE_NEW
WHERE VAT_HEADER_ID = CAVH.VAT_HEADER_ID
AND TRUNC(IMPORT_DATE) >= TRUNC(SYSDATE - 1))
可以在VAT_HEADER_ID字段上添加索引，并重新統(tǒng)計信息：
SQL> create index CUX.CUX_TWOVOTE_SALEINVOICE_NEW_N4 on CUX.CUX_TWOVOTE_SALEINVOICE_NEW (VAT_HEADER_ID)
tablespace CUXD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
); 2 3 4 5 6 7 8 9 10 11 12 13
Index created.
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'CUX',
tabname => 'CUX_TWOVOTE_SALEINVOICE_NEW',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => TRUE,
no_invalidate => FALSE
);
END;
/ 2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL>
改寫后最終sql_id:3xb8q2021ztpv。如下：
INSERT INTO CUX_TWOVOTE_SALEINVOICE_NEW (SUPPLIERID, SALINVOICENO, SALINVDATE, SALSUMVALUE, SALINVTYPE, SALCSTCODE, SALCSTNAME, GOODS, GOODNAME, VAT_HEADER_ID, VAT_LINE_ID, PO_LINE_ID, SOURCE_PO_LINE_ID, PO_HEADER_ID, SOURCE_PO_HEADER_ID, PO_NUMBER, ORG_ID, INTER_ORG_ID, ITEM_ID, LOT_NUMBER, REQUEST_ID, ATTRIBUTE1, ATTRIBUTE2)
SELECT CAVL.DEPARTMENT_CODE,
DECODE(
(SELECT COUNT(1)
FROM CUX_SC_MINHANG_CLIENT
WHERE ACCOUNT_NUMBER = CAVH.CUSTOMER_NUMBER), 0, CAVH.VAT_NUMBER, DECODE(INSTR(CAVH.INVOICE_CATEGORY, '全電'), 0, CAVH.VAT_INVOICE_CODE || CAVH.VAT_NUMBER, CAVH.VAT_NUMBER)) VAT_NUMBER,
TO_CHAR(CAVH.VAT_DATE, 'yyyy-mm-dd') VAT_DATE,
CAVH.AMOUNT_INC_TAX SALSUMVALUE,
COPL.PROPERTY7_NAME SALINVTYPE,
CAVH.CUSTOMER_NUMBER,
CAVH.CUSTOMER_NAME,
CAVL.ITEM_CODE || '|' || CAVL.PACKAGE_NUM ITEM_CODE,
CAVL.ITEM_DESC,
CAVH.VAT_HEADER_ID,
CAVL.VAT_LINE_ID,
DECODE(
(SELECT COUNT(1)
FROM CUX_OU_PROPERTY_LINES_T COUL, PO_LINES_ALL PL, PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.VENDOR_ID = COUL.ITEM_ID
AND PL.PO_LINE_ID = MLN.N_ATTRIBUTE6
AND COUL.PROPERTY_TYPE = 'V'
AND NVL(COUL.PROPERTY5_NAME, 'N') <> 'N'), 0, MLN.N_ATTRIBUTE6, NVL(COMR.PO_LINE_ID, MLN.N_ATTRIBUTE6)) PO_LINE_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE6
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.N_ATTRIBUTE6
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE6, COMR.PO_LINE_ID)
END AS SOURCE_PO_LINE_ID,
MLN.N_ATTRIBUTE5 PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE5
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.N_ATTRIBUTE5
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE5, COMR.PO_HEADER_ID)
END AS SOURCE_PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.C_ATTRIBUTE1
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.C_ATTRIBUTE1
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.C_ATTRIBUTE1, '')
END AS PO_NUMBER,
COMR.ORG_ID,
COMR.INTERCOMPANY_ORG_ID,
CAVL.ITEM_ID,
CAVL.LOT_NUMBER,
:B1,
CAVH.VAT_INVOICE_CODE,
CAVH.VAT_NUMBER
FROM CUX_AR_VAT_HEADERS CAVH,
CUX_AR_VAT_LINES CAVL,
CUX_OE_MTL_RESERVATIONS COMR,
CUX_OE_ORDER_HEADERS COOH,
MTL_LOT_NUMBERS MLN,
CUX_MST_ITEM ISI,
CUX_FND_LOOKUP_VALUES SCP,
(SELECT ITEM_ID,
PROPERTY7_NAME,
OU_ID
FROM CUX_OU_PROPERTY_LINES_T
WHERE PROPERTY_TYPE = 'C'
AND NVL(PROPERTY7_NAME, 'N') <> 'N') COPL,
(SELECT T.TRANSACTION_TYPE_ID
FROM OE_TRANSACTION_TYPES_ALL TT,
OE_TRANSACTION_TYPES_TL T,
FND_DOCUMENT_SEQUENCES A
WHERE TT.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
AND T.LANGUAGE = USERENV('LANG')
AND TT.ATTRIBUTE1 = A.DOC_SEQUENCE_ID
AND TT.TRANSACTION_TYPE_CODE = 'ORDER'
AND TT.ATTRIBUTE15 <> '01') TT_TYPE,
(SELECT HOU.ORGANIZATION_ID,
FLV.MEANING
FROM HR_ORGANIZATION_UNITS_V HOU,
CUX_FND_LOOKUP_VALUES FLV
WHERE HOU.ATTRIBUTE6 = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'MST-0031'
AND HOU.ATTRIBUTE6 IS NOT NULL) COM1,
(SELECT HOU.ORGANIZATION_ID,
FLV.MEANING
FROM HR_ORGANIZATION_UNITS_V HOU,
CUX_FND_LOOKUP_VALUES FLV
WHERE HOU.ATTRIBUTE6 = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'MST-0031'
AND HOU.ATTRIBUTE6 IS NOT NULL) COM2,
(SELECT COMR_SOURCE.RELATE_PO_LINE_ID,
MLN_SOURCE.N_ATTRIBUTE6,
MLN_SOURCE.N_ATTRIBUTE5,
MLN_SOURCE.C_ATTRIBUTE1
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID) MLN_SOURCE
WHERE CAVH.VAT_HEADER_ID = CAVL.VAT_HEADER_ID
AND CAVH.VAT_HEADER_ID = COMR.VAT_HEADER_ID
AND COMR.HEADER_ID = COOH.HEADER_ID
AND COMR.SOURCE_LINE_ID = CAVL.ORDER_LINE_ID
AND COMR.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID
AND COMR.ORGANIZATION_ID = MLN.ORGANIZATION_ID
AND COMR.LOT_NUMBER = MLN.LOT_NUMBER
AND COMR.INVENTORY_ITEM_ID = ISI.INVENTORY_ITEM_ID
AND COMR.ORGANIZATION_ID = ISI.ORGANIZATION_ID
AND ISI.OPERATE_SCOPE = SCP.LOOKUP_CODE
AND SCP.LOOKUP_TYPE = 'MST-0004'
AND SCP.SEGMENT4 = 'Y'
AND COOH.ORDER_TYPE_ID = TT_TYPE.TRANSACTION_TYPE_ID
AND NVL(COOH.ORDER_SOURCE_REFERENCE, 'N') NOT IN ('QUICK_ORDER',
'QUICK_RET')
AND CAVH.CUSTOMER_ID = COPL.ITEM_ID
AND CAVH.ORG_ID = COPL.OU_ID
AND COMR.ORG_ID = COM1.ORGANIZATION_ID
AND COMR.INTERCOMPANY_ORG_ID = COM2.ORGANIZATION_ID
AND MLN.N_ATTRIBUTE6 = MLN_SOURCE.RELATE_PO_LINE_ID(+)
AND CAVH.ENABLED_FLAG = 'Y'
AND UPPER(CAVH.VAT_NUMBER) NOT LIKE 'X%'
AND UPPER(CAVH.VAT_NUMBER) NOT LIKE 'D%'
AND CAVH.VAT_NUMBER IS NOT NULL
AND CAVH.AMOUNT_INC_TAX > 0
AND CAVL.AMOUNT_INC_TAX > 0
AND MLN.C_ATTRIBUTE4 NOT IN
(SELECT MEANING
FROM CUX_FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AP-013'
AND TAG = 'SUP'
AND SEGMENT1 = 'Y'
AND ENABLED_FLAG = 'Y')
AND TRUNC(CAVH.LAST_UPDATE_DATE) >= TRUNC(SYSDATE - 1)
AND NOT EXISTS
(SELECT 1
FROM CUX_TWOVOTE_SALEINVOICE_NEW
WHERE VAT_HEADER_ID = CAVH.VAT_HEADER_ID
AND TRUNC(IMPORT_DATE) >= TRUNC(SYSDATE - 1))
優(yōu)化后執(zhí)行計劃如下
優(yōu)化前后，對比平均執(zhí)行時間。573s → 98s，提升近6倍。
總結
在SQL優(yōu)化中經常會碰到一些復雜的、而且業(yè)務邏輯無法理解的SQL。不要急，通過執(zhí)行計劃一部分一部分的來優(yōu)化。把全表掃描、標量子查詢和驅動表的關系等搞清楚。多嘗試改寫的方法、盡量過濾查詢數據。這樣再復雜的SQL也有辦法進行優(yōu)化。
根因：相關標量子查詢在大結果集上被重復執(zhí)行，反復訪問大表（4551w/1185w），放大 IO/CPU。
關鍵動作：把 3 處重復標量子查詢抽成可復用派生表并
LEFT JOIN，減少重復掃描。配套動作：為全表掃描熱點補索引并重新收集統(tǒng)計信息（含
VAT_HEADER_ID、PROPERTY_TYPE）。效果：平均耗時 573s → 98s（≈6x）。
最后修改時間：2025-12-09 09:40:58
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創(chuàng)內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發(fā)現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發(fā)送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
