Oracle查询性能优化求助：SQL调优建议索引未生效如何处理？

遇到这种明明建了SQL Tuning Advisory推荐的索引，但执行计划还是死守主键索引的情况，我给你分享几个实战过的排查和解决步骤：

1. 先确认索引是否正常“上岗” #

先别着急找优化器的麻烦，先确认新索引是不是真的建好且可用。执行这条语句检查：

SELECT index_name, status, column_name
FROM user_ind_columns 
WHERE table_name = 'TACID_MST' AND index_name = 'IDX$$_5E37B0001';

要确保status是VALID，而且列顺序和推荐的完全一致：STATUS, USEDAS, MACIDSTATUS, SITEREFNO, ISCODESIGNED。

2. 更新表和索引的统计信息 #

Oracle优化器是个“数据控”，完全依赖最新的统计信息判断索引性价比。如果统计信息过时，它可能会错误地认为主键索引更高效。执行这条命令强制收集最新统计：

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'TESTUSER', 
    tabname => 'TACID_MST', 
    cascade => TRUE, -- 顺便收集索引的统计信息
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

收集完后再重新看执行计划，大概率优化器会重新评估并选择新索引。

3. 检查复合索引的匹配逻辑 #

你的查询都是等值过滤条件，推荐的复合索引包含了所有过滤列，理论上是完美匹配的。虽然等值条件下索引列顺序影响不大，但如果某列的基数（distinct值数量）特别低，优化器可能会有奇怪的判断——不过这一步通常在统计信息更新后就会自动修正。

4. 用索引提示“强迫”优化器选新索引 #

如果前面几步都没用，可以试试用索引提示直接给优化器“下命令”，修改后的查询语句如下：

SELECT /*+ INDEX(TACID_MST IDX$$_5E37B0001) */ 
       NVL(Min(MACIDREFNO), 0) 
FROM TACID_MST 
WHERE MacIdStatus = 2 
  AND Status = 0 
  AND UsedAs = 1 
  AND IsCodeSigned = 0 
  AND SiteRefNo = 0;

执行这个修改后的查询，再看执行计划是否切换到新索引。如果切换后性能明显提升，说明是优化器的统计信息或成本计算出了问题，这时候可以考虑把提示固化到语句里，或者进一步排查统计信息的准确性。

5. 深挖优化器的成本计算逻辑 #

如果还是不行，查看详细的执行计划（比如用EXPLAIN PLAN FOR或者SQL Developer的执行计划视图），对比主键索引和新索引的基数（Rows）、成本（Cost）：

• 如果优化器认为符合过滤条件的行数很多，可能会觉得全表扫描或主键索引扫描更快，但实际行数很少的话，就是统计信息不准。
• 另外，主键索引如果是唯一索引，优化器可能觉得直接扫描主键索引找最小值更高效，但实际上你的过滤条件会过滤掉大部分数据，新索引应该更优。

附：你的原始查询与调优建议 #

原始查询：

Select NVL(Min(MACIDREFNO),0) from TACID_MST Where MacIdStatus = 2 And Status = 0 And UsedAs = 1 And IsCodeSigned = 0 And SiteRefNo = 0;

SQL Tuning Advisory结果：

GENERAL INFORMATION SECTION Result of Tuning Advisory #

Tuning Task Name : staName75534
Tuning Task Owner : TESTUSER
Tuning Task ID : 385915
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 01/24/2018 15:46:15
Completed at : 01/24/2018 15:46:17
Number of Index Findings : 1 #

FINDINGS SECTION (1 finding) #

1- Index Finding (see explain plans section below) #

The execution plan of this statement can be improved by creating one or more indices.
Recommendation (estimated benefit: 100%) #

• Consider running the Access Advisor to improve the physical schema design or creating the recommended index.
  create index TESTUSER.IDX$$_5E37B0001 on TESTUSER.TACID_MST('STATUS','USEDAS','MACIDSTATUS','SITEREFNO','ISCODESIGNED');
  Rationale

---

Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.

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