Oracle 标量子查询优化实战：从 573s 到 98s 的 SQL 改写案例

前言
#

其实写SQL优化的实战很难，主要是很多情况是需要结合业务，而我们很多开发人员为了满足业务的需求随心随意的写SQL，就产生了很多质量较差的SQL，这些SQL可能在开发测试数据量不大的环境中正常运行，可是一到生产环境就直接把整个数据库服务器资源打满。所以我们平时在写SQL过程中多做简化工作，尽量让SQL写的让别人能读懂。多考虑条件的过滤包括关键字段索引等等，查询的数据量少了，减少了全表扫描了，自然效率也就高了。最后，要满足业务的需求又要写出易懂高质量的SQL。这其实是一个需要权衡的过程、需要做取舍。

问题现象
#

运维人员反馈EBS系统有一个请求运行时间很长，每次运行需要10多分钟。影响数据库整体性能和业务开展。

分析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）工具得到执行计划如下图

由于SQL复杂，对表和字段的定义无法从业务侧得知，如何取舍优化。只能从DBA角度来添加索引或者改写SQL逐步分析。

先对SELECT的23个字段部分开始：

上图执行计划里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之间，这种子查询就叫标量子查询。

标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表，嵌套循环被驱动表的连接列必须包含在索引中。同理，标量子查询中子查询的表连接列也必须包含在索引中。

建议在实际生产工作中，尽量避免使用标量子查询。原因是：假如主表返回大量数据，主表的连接列基数又很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能；如果主表数据量小，或者主表的连接列基数很低，那么这个时候也可以使用标量子查询，但要给子查询中表的连接列建立索引。

当SQL里面有标量子查询，可以将标量子查询等价改写为外连接，从而使它们可以进行HASH连接。

标量子查询=外层每一行都要去子表再查一次
外层行数一大 + 子表一大 ⇒ 执行次数爆炸

执行计划中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>

优化SQL
#

在我们通过执行计划加上表数据量和索引等信息分析后，做如下处理。

把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字段上添加索引，并重新统计信息：

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字段上添加索引，并重新统计信息：

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

优化后执行计划如下

优化前后，对比平均执行时间。573s → 98s，提升近6倍。

总结
#

在SQL优化中经常会碰到一些复杂的、而且业务逻辑无法理解的SQL。不要急，通过执行计划一部分一部分的来优化。把全表扫描、标量子查询和驱动表的关系等搞清楚。多尝试改写的方法、尽量过滤查询数据。这样再复杂的SQL也有办法进行优化。

根因：相关标量子查询在大结果集上被重复执行，反复访问大表（4551w/1185w），放大 IO/CPU。
关键动作：把 3 处重复标量子查询抽成可复用派生表并 LEFT JOIN，减少重复扫描。
配套动作：为全表扫描热点补索引并重新收集统计信息（含 VAT_HEADER_ID、PROPERTY_TYPE）。
效果：平均耗时 573s → 98s（≈6x）。

前言#

问题现象#

分析SQL#

优化SQL#

总结#

前言
#

问题现象
#

分析SQL
#

优化SQL
#

总结
#