如何从Oracle执行计划中定位性能瓶颈及优化建议咨询
2026-7-17
Oracle SQL执行计划分析与潜在优化点探讨
各位Oracle SQL调优专家，恳请帮忙分析以下执行计划以定位性能瓶颈，提前致谢。该查询响应速度很快，但我希望通过分析执行计划找到代码的潜在优化点。就我目前的认知来看执行计划整体尚可，但仅返回一行数据却有较高的成本，对此我存在疑问。
-------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25 | 10000 | 6105 (1)| 00:00:01 | | 1 | SORT ORDER BY | | 25 | 10000 | 6105 (1)| 00:00:01 | |* 2 | VIEW | | 25 | 10000 | 6104 (1)| 00:00:01 | | 3 | WINDOW SORT | | 25 | 9625 | 6104 (1)| 00:00:01 | | 4 | WINDOW SORT | | 25 | 9625 | 6104 (1)| 00:00:01 | | 5 | WINDOW SORT | | 25 | 9625 | 6104 (1)| 00:00:01 | | 6 | NESTED LOOPS ANTI | | 25 | 9625 | 6101 (1)| 00:00:01 | | 7 | NESTED LOOPS OUTER | | 25 | 8800 | 6001 (1)| 00:00:01 | | 8 | NESTED LOOPS OUTER | | 25 | 8125 | 5950 (1)| 00:00:01 | | 9 | NESTED LOOPS OUTER | | 25 | 7675 | 5875 (1)| 00:00:01 | | 10 | NESTED LOOPS OUTER | | 7 | 1862 | 5850 (1)| 00:00:01 | | 11 | NESTED LOOPS OUTER | | 5 | 1200 | 5825 (1)| 00:00:01 | | 12 | NESTED LOOPS OUTER | | 5 | 1165 | 5821 (1)| 00:00:01 | | 13 | NESTED LOOPS OUTER | | 4 | 760 | 5807 (1)| 00:00:01 | | 14 | NESTED LOOPS OUTER | | 4 | 684 | 5797 (1)| 00:00:01 | | 15 | NESTED LOOPS OUTER | | 4 | 568 | 5793 (1)| 00:00:01 | | 16 | NESTED LOOPS OUTER | | 4 | 540 | 5793 (1)| 00:00:01 | | 17 | NESTED LOOPS OUTER | | 4 | 424 | 5789 (1)| 00:00:01 | | 18 | NESTED LOOPS | | 4 | 396 | 5789 (1)| 00:00:01 | | 19 | NESTED LOOPS | | 57 | 1995 | 5630 (1)| 00:00:01 | | 20 | INLIST ITERATOR | | | | | | |* 21 | INDEX RANGE SCAN | NU_LOV_ENTRY_1 | 3 | 36 | 3 (0)| 00:00:01 | |* 22 | TABLE ACCESS BY INDEX ROWID BATCHED| PARTY | 22 | 506 | 1876 (1)| 00:00:01 | |* 23 | INDEX RANGE SCAN | IDX_HOME_STORE_PARTY | 14838 | | 41 (0)| 00:00:01 | |* 24 | TABLE ACCESS BY INDEX ROWID | PERSON | 1 | 64 | 3 (0)| 00:00:01 | |* 25 | INDEX UNIQUE SCAN | XPKPERSON | 1 | | 2 (0)| 00:00:01 | |* 26 | INDEX UNIQUE SCAN | XPKLOV_ENTRY | 1 | 7 | 0 (0)| 00:00:01 | | 27 | TABLE ACCESS BY INDEX ROWID | TRANSLATION | 1 | 29 | 1 (0)| 00:00:01 | |* 28 | INDEX UNIQUE SCAN | XPKTRANSLATION | 1 | | 0 (0)| 00:00:01 | |* 29 | INDEX UNIQUE SCAN | XPKLOV_ENTRY | 1 | 7 | 0 (0)| 00:00:01 | | 30 | TABLE ACCESS BY INDEX ROWID | TRANSLATION | 1 | 29 | 1 (0)| 00:00:01 | |* 31 | INDEX UNIQUE SCAN | XPKTRANSLATION | 1 | | 0 (0)| 00:00:01 | |* 32 | TABLE ACCESS BY INDEX ROWID BATCHED | PARTY_LEGAL_HOLD | 1 | 19 | 4 (0)| 00:00:01 | |* 33 | INDEX RANGE SCAN | XIF3PARTY_LEGAL_HOLD | 1 | | 2 (0)| 00:00:01 | |* 34 | TABLE ACCESS BY INDEX ROWID BATCHED | ADDRESS | 1 | 43 | 4 (0)| 00:00:01 | |* 35 | INDEX RANGE SCAN | IDX$$_036C0004 | 1 | | 3 (0)| 00:00:01 | | 36 | TABLE ACCESS BY INDEX ROWID | STATE_PROVINCE | 1 | 7 | 1 (0)| 00:00:01 | |* 37 | INDEX UNIQUE SCAN | XPKSTATE_PROVINCE | 1 | | 0 (0)| 00:00:01 | |* 38 | TABLE ACCESS BY INDEX ROWID BATCHED | PHONE | 1 | 26 | 5 (0)| 00:00:01 | |* 39 | INDEX RANGE SCAN | IDX$$_036C0003_1 | 1 | | 3 (0)| 00:00:01 | | 40 | TABLE ACCESS BY INDEX ROWID BATCHED | AGREEMENT_PARTY | 4 | 164 | 7 (0)| 00:00:01 | |* 41 | INDEX RANGE SCAN | XIF2AGREEMENT_PARTY | 4 | | 3 (0)| 00:00:01 | | 42 | TABLE ACCESS BY INDEX ROWID | AGREEMENT | 1 | 18 | 3 (0)| 00:00:01 | |* 43 | INDEX UNIQUE SCAN | XPKAGREEMENT | 1 | | 2 (0)| 00:00:01 | | 44 | TABLE ACCESS BY INDEX ROWID BATCHED | INSTALLMENT_NOTE | 1 | 27 | 3 (0)| 00:00:01 | |* 45 | INDEX RANGE SCAN | IDX_INSTALLMENT_NOTE_AGR_ID | 1 | | 2 (0)| 00:00:01 | |* 46 | TABLE ACCESS BY INDEX ROWID BATCHED | AGREEMENT_PARTY | 1 | 33 | 4 (0)| 00:00:01 | |* 47 | INDEX RANGE SCAN | UQ_AGREEMENT_PARTY_1 | 1 | | 3 (0)| 00:00:01 |
执行计划关键分析点
1. 预估成本与实际返回行数的偏差
执行计划预估返回25行，但实际仅返回1行，整体成本达6105，核心原因是统计信息不准确：
PARTY表的IDX_HOME_STORE_PARTY索引预估扫描14838行，最终仅匹配22行数据，说明该索引对应的统计信息过时，CBO对数据分布判断严重偏差，高估了中间结果集规模，推高了整体成本。- 多层嵌套外连接的累加效应，每一层外连接都会让CBO默认保留更多结果行，进一步放大了预估偏差。
2. 排序操作的开销占比
执行计划包含3次WINDOW SORT和1次SORT ORDER BY，这部分是成本的主要构成。如果查询中使用了窗口函数（如ROW_NUMBER、RANK），需要注意：
- 窗口函数的分区/排序字段是否缺少对应索引，导致每次查询都要全量排序。
- 是否存在不必要的窗口函数嵌套，可通过简化业务逻辑减少排序次数。
3. 多层嵌套连接的潜在风险
当前查询用了10层以上的嵌套外连接，虽然响应快，但数据量增长后可能成为瓶颈：
- 检查所有外连接是否都是业务必需的，部分非必需的外连接可改为内连接，或通过子查询提前过滤数据，缩小中间结果集。
NESTED LOOPS ANTI反连接的效率依赖索引支持，若原查询使用NOT IN，建议替换为NOT EXISTS，并确保关联字段有索引，提升过滤效率。
4. 临时索引的问题
执行计划中出现IDX$$_036C0004和IDX$$_036C0003_1两个临时索引，说明原表缺少匹配查询过滤条件的永久索引。临时索引每次查询都需要重新生成，会增加额外开销，且性能不稳定。
具体优化建议
- 更新统计信息：对涉及的所有表和索引执行
DBMS_STATS.GATHER_TABLE_STATS，确保CBO获取准确的数据分布，缩小预估与实际的行数偏差。 - 优化窗口函数：为窗口函数的分区/排序字段创建复合索引，避免全量排序，降低排序操作的成本。
- 精简连接逻辑：梳理业务需求，移除不必要的外连接，或通过子查询提前过滤数据，减少中间结果集的大小。
- 替换临时索引：根据临时索引的字段组合，创建对应的永久索引，提升查询的稳定性和执行效率。
- 调整反连接逻辑：将
NOT IN替换为NOT EXISTS，并确保关联字段有索引支持，提升反连接的过滤效率。
内容的提问来源于stack exchange，提问作者user1402648
相关产品推荐
相关产品推荐
