刚刚监控到一条SQL占用了大量的I/O，SQL如下：

SELECT ORD_ID,
       UNT_PRD_ID,
       ORD_QTY,
       STKO_WH_NO,
       ORD_WH_NO,
       ORD_MDA_CD,
       STOCK_MDA_CD
  from (SELECT ORD_ID,
               UNT_PRD_ID,
               SUM(ORD_QTY) ORD_QTY,
               STKO_WH_NO,
               ORD_WH_NO,
               ORD_MDA_CD,
               STOCK_MDA_CD
          FROM ORD_ORD_DTL_D T
         WHERE ORD_STS_CD = :1
           AND ORD_PTR_CD = '100'
           AND UNT_PRD_ID = :2
           AND ORD_ACP_DTM >= TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS')
         GROUP BY ORD_ID,
                  UNT_PRD_ID,
                  STKO_WH_NO,
                  STOCK_MDA_CD，ORD_WH_NO,
                  ORD_MDA_CD)
 ORDER BY ORD_ID

这是一张分区表，ORD_ACP_DTM就是date类型的分区字段，看这条SQL的执行计划，并没什么太大的问题。

SQL> explain plan for
  2  SELECT ORD_ID,
  3         UNT_PRD_ID,
  4         ORD_QTY,
  5         STKO_WH_NO,
  6         ORD_WH_NO,
  7         ORD_MDA_CD,
  8         STOCK_MDA_CD
  9    from (SELECT ORD_ID,
 10                 UNT_PRD_ID,
 11                 SUM(ORD_QTY) ORD_QTY,
 12                 STKO_WH_NO,
 13                 ORD_WH_NO,
 14                 ORD_MDA_CD,
 15                 STOCK_MDA_CD
 16            FROM ORD_ORD_DTL_D T
 17           WHERE ORD_STS_CD = :1
 18             AND ORD_PTR_CD = '100'
 19             AND UNT_PRD_ID = :2
 20             AND ORD_ACP_DTM >= TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS')
 21           GROUP BY ORD_ID,
 22                    UNT_PRD_ID,
 23                    STKO_WH_NO,
 24                    STOCK_MDA_CD，ORD_WH_NO,
 25                    ORD_MDA_CD)
 26   ORDER BY ORD_ID
 27  ;

Explained.

SQL> set lines 200
SQL> select * from table(dbms_xplan.display); 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 67455230
------------------------------------------------------------------------------------------------------------
|Id| Operation                           | Name              |Rows|Bytes|Cost (%CPU)|Time    |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                    |                   |   4|  224| 3696   (1)|00:00:45|      |       |
| 1|  SORT GROUP BY                      |                   |   4|  224| 3696   (1)|00:00:45|      |       |
| 2|   PARTITION RANGE ITERATOR          |                   |   4|  224| 3695   (1)|00:00:45|  KEY |1048575|
|*3|    TABLE ACCESS BY LOCAL INDEX ROWID|ORD_ORD_DTL_D      |   4|  224| 3695   (1)|00:00:45|  KEY |1048575|
|*4|     INDEX RANGE SCAN                |IX_ORD_ORD_DTL_D_16|1152|     | 3054   (1)|00:00:37|  KEY |1048575|
-------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ORD_STS_CD"=:1 AND "ORD_PTR_CD"='100' AND "ORD_ACP_DTM">=TO_DATE(:3,'YYYY-MM-DD HH24:MI:SS'))
   4 - access("UNT_PRD_ID"=:2)
17 rows selected.

这里面使用了IX_ORD_ORD_DTL_D_16这个索引，可是从监控看，这条SQL使用了IX_ORD_ORD_DTL_D_06索引，下面查看一下这两个索引的信息。

SQL> select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from user_ind_columns where index_name in ('IX_ORD_ORD_DTL_D_16','IX_ORD_ORD_DTL_D_06') order by 1,3;

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IX_ORD_ORD_DTL_D_06            ORD_ACP_DTM                                  1
IX_ORD_ORD_DTL_D_06            ORD_STS_CD                                   2
IX_ORD_ORD_DTL_D_06            ORD_DTL_STS_CD                               3
IX_ORD_ORD_DTL_D_16            UNT_PRD_ID                                   1
IX_ORD_ORD_DTL_D_16            UNT_SEQ                                      2

SQL> select segment_name,sum(bytes)/1024/1024/1024 from user_segments where segment_name in  ('IX_ORD_ORD_DTL_D_06','IX_ORD_ORD_DTL_D_16') group by segment_name;

SEGMENT_NAME                       SUM(BYTES)/1024/1024/1024
---------------------------------- -------------------------
IX_ORD_ORD_DTL_D_16                3.65283203
IX_ORD_ORD_DTL_D_06                4.8057251

可以看到这两个都是复合索引，还都不小(因为是分区表的LOCAL索引，每个分区上的索引都不大)，因为我对业务还算熟悉，除了时间字段，其他字段基本都是记录标识信息的字段，重复度相当的高，过滤性极差。

SQL> select num_rows from user_tables where table_name='ORD_ORD_DTL_D';

  NUM_ROWS
----------
  72778422

上面的查询可以看到，数据库大概有7千多万的记录，再看下这些索引列的重复度，这里看几个关键的列就可以。

SQL> select COLUMN_NAME,NUM_DISTINCT from user_tab_columns where TABLE_NAME='ORD_ORD_DTL_D' and column_name in  
('ORD_STS_CD','ORD_DTL_STS_CD','UNT_PRD_ID');

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
ORD_STS_CD                               12
ORD_DTL_STS_CD                           19
UNT_PRD_ID                            64300

是不是很坑爹，监控发现这条SQL根据ORD_ACP_DTM和ORD_STS_CD字段的过滤条件，选择了IX_ORD_ORD_DTL_D_06这个包含ORD_ACP_DTM、ORD_STS_CD、ORD_DTL_STS_CD三个字段的复合索引，关键是ORD_STS_CD只有12个不同的值，过滤性极差，而且这个SQL执行频率还很高，好在当我发现的时候，这条SQL已经选择了过滤性相对好一点的IX_ORD_ORD_DTL_D_16索引。造成这种情况的原因可能是SQL的WHERE条件的时间跨度较大或者是ORD_STS_CD字段的值过滤性相对较好导致的。

数据库存在很多垃圾索引，我们之前通过索引监控，数据库业务用户一共1400多的索引，一年多的时候，有800多一次没有用到过，除去这800多的垃圾索引，还有一些像IX_ORD_ORD_DTL_D_06这样被使用过，但是会严重降低SQL性能的不应该存在索引，这些索引都是业务上线的时候，领导要求创建的，我们向上面反映过这些问题，也遇到过这样因为选择错误的索引导致SQL性能严重下降的问题，但领导不同意，我们也不敢去删，我们只能利用11g的新特性将这样的索引设置成不可用状态，这样索引还是会随着数据的变化而维护，需要的时候在设置回来就行，因为索引会随着数据的变化而维护，不但占用磁盘空间，还会影响数据库的性能，索引也是会有热点块的，也是会有竞争的。

本文固定链接: https://www.dbdream.com.cn/2018/03/%e7%b4%a2%e5%bc%95%e8%bf%87%e6%bb%a4%e6%80%a7%e4%b8%8d%e5%a5%bd%e5%af%bc%e8%87%b4sql%e5%bc%82%e5%b8%b8%e7%bc%93%e6%85%a2/ | 信春哥，系统稳，闭眼上线不回滚！