当前位置: 首页 > Oracle, oracle 11g > 正文

索引过滤性不好导致SQL异常缓慢

刚刚监控到一条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的新特性将这样的索引设置成不可用状态,这样索引还是会随着数据的变化而维护,需要的时候在设置回来就行,因为索引会随着数据的变化而维护,不但占用磁盘空间,还会影响数据库的性能,索引也是会有热点块的,也是会有竞争的。

本文固定链接: http://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/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2018年03月21日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 索引过滤性不好导致SQL异常缓慢 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

索引过滤性不好导致SQL异常缓慢:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter