Oracle 12g慢SQL优化：仅查询层面可调的10小时长耗时SQL调优指导

以下所有调整均仅涉及查询逻辑修改，无需修改表结构：

1. 修正并行提示配置 #

当前的/*+ PARALLEL (PRD, 36) */提示仅对单表PRD生效，其余表和关联操作仍为串行执行，完全无法发挥并行执行的性能优势。

• 直接替换为语句级并行提示：/*+ PARALLEL(32) */，Oracle会自动为全语句所有操作分配并行资源
• 并行度建议根据数据库实际CPU核数调整，不要超过CPU核数*2，避免抢占其他业务的数据库资源

2. 修正隐性内连接错误 #

你当前用的是Oracle旧语法的左连接(+)，但后续WHERE条件的写法直接把左连接变成了内连接，会导致优化器选错关联顺序、浪费IO：

• SRV_AD是左连接，但WHERE中加了UPPER(SRV_AD.STATE) IN ( 'AB', 'CD', 'EF')，SRV_AD为NULL时该条件不成立，等价于内连接
• SERV_ACCT是左连接，但后续关联内连接表BU时用了SERV_ACCT.BU_ID = BU.ROW_ID，SERV_ACCT为NULL时该条件不成立，也等价于内连接
• 修正方式：如果业务上确实需要左连接，就把对应过滤条件移到关联条件里，比如把UPPER(SRV_AD.STATE) IN ( 'AB', 'CD', 'EF')移到SRV_AD的关联行后面；如果业务上本来就是内连接，直接删掉(+)明确写INNER JOIN，让优化器生成更准确的执行计划

3. 提升过滤条件的索引利用率 #

• 避免过滤列上的函数导致索引失效：如果业务上SRV_AD.STATE字段存储的值本身都是大写，直接删掉UPPER()函数，改成SRV_AD.STATE IN ( 'AB', 'CD', 'EF')，就能用上STATE字段上的现有普通索引
• 强制高过滤性小结果集优先关联：PRD.part_num IN ('R', 'B', 'R_D', 'B_D', 'ND')、PR_ATTR.ATTR_NAME = 'NUMBER'、U_ATTR.ATTR_NAME = 'UNIVERSE'、BU.NAME <> 'WS'这几个条件过滤性通常很高，加提示/*+ LEADING(PRD PR_ATTR U_ATTR BU AST) */让优化器先扫描这几个小结果集，再关联大表S_ASSET，可以大幅减少后续关联的数据量

4. 减少大表重复扫描 #

当前查询对A.S_ASSET_XA表关联了两次，分别取ATTR_NAME为NUMBER和UNIVERSE的CHAR_VAL，完全可以改成一次扫描后行转列，减少一次大表的IO开销：

-- 替换原来两次关联S_ASSET_XA的逻辑，改成这个子查询
(SELECT ASSET_ID,
       MAX(CASE WHEN ATTR_NAME='NUMBER' THEN CHAR_VAL END) AS PR_CHAR_VAL,
       MAX(CASE WHEN ATTR_NAME='UNIVERSE' THEN CHAR_VAL END) AS U_CHAR_VAL
  FROM A.S_ASSET_XA
 WHERE ATTR_NAME IN ('NUMBER','UNIVERSE')
   AND CHAR_VAL IS NOT NULL
 GROUP BY ASSET_ID) ASSET_ATTR

关联条件改成AST.ROW_ID = ASSET_ATTR.ASSET_ID即可，同时原来的PR_ATTR.CHAR_VAL IS NOT NULL条件也已经放到子查询里提前过滤。

5. 其他细节优化 #

• 把老旧的(+)左连接写法替换为标准ANSI JOIN写法（LEFT JOIN/INNER JOIN），连接条件和过滤条件拆分更清晰，减少优化器生成错误执行计划的概率
• 如果查询返回的结果集非常大，可以加/*+ USE_NL(AST CA) */这类关联提示，优先用嵌套循环关联小表，避免哈希关联占用大量临时表空间

内容的提问来源于stack exchange，提问作者JCBA