【实践、技术】解决一起索引字段经常更新造成的索引效率低下的问题

 

 

 

 

操作系统：

数据库版本：

 

 

数据库机器读盘厉害，由于没有完整的检测手段以及历史累计数据，因此只能用简单的方式先进行检查：

 

检查消耗磁盘多的SQL语句：

 

从V$SQLAREA中查询最占用资源的查询

select b.username username

        ,a.disk_reads reads

        ,a.executions exec

        ,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio

        ,a.sql_text Statement
from  v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
       and a.disk_reads > 100000
order by a.disk_reads desc;

 

发现排名前几位的，有一句不应该出现的语句：

select * from (

   select * from user_message_send

    where  SEND_FLAG =?

         and   SEND_COUNT <3

    order by id

   )

where rownum<100

 

这个sql从表A中获取，type为特定值，coutn<3，同时按照时间排序的最早100条（ID是sequence生成，等同于时间）。

进行trace，发现这句sql语句physical reads 要有700， 而取出来的数据却不多。

 

第一次trace结果

执行分析：

 

Elapsed: 00:00:12.63

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=1164)

   1    0   COUNT (STOPKEY)

   2    1     VIEW (Cost=10 Card=1 Bytes=1164)

   3    2       SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=122)

   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'USER_MESSAGE_SEND'(Cost=1 Card=1 Bytes=122)

   5    4           INDEX (RANGE SCAN) OF 'I_SENDFLAG' (NON-UNIQUE) (Cost=1 Card=1)

 

Statistics

----------------------------------------------------------

          7  recursive calls

          0  db block gets

        716  consistent gets

        683  physical reads

          0  redo size

        936  bytes sent via SQL*Net to client

        373  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

Consistent gets和physical reads 特备高。

 

而且查询过程中，使用到了索引

” INDEX (RANGE SCAN) OF 'I_SENDFLAG' (NON-UNIQUE) (Cost=1 Card=1)”

 

通过2个不同工具，进行的分析结果

 

 

 

索引 I_SENDFLAG中，只包含了  SEND_FLAG字段，没有其他的字段

 

===============================================

 

原来以为是这个原因，新建了一个索引：

create index i_flagcount on user_message_send (SEND_FLAG, SEND_COUNT)

 

再进行trace的时候，发现还是使用到了第一个索引；

 

加HINT，让SQL强行使用新索引，进行trace，结果出人意料：

 

Execution Plan

----------------------------------------------------------

   0   SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=1164 )

   1    0  COUNT (STOPKEY)

   2    1    VIEW (Cost=10 Card=1 Bytes=1164)

   3    2      SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=122)

   4    3        TABLE ACCESS (BY INDEX ROWID) OF 'USER_MESSAGE_SEND'           (Cost=2 Card=1 Bytes=122)

   5    4          INDEX (RANGE SCAN) OF 'I_FLAGCOUNT' (NON-UNIQUE) ( Cost=2 Card=1)

 

Statistics

----------------------------------------------------------

          7  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        936  bytes sent via SQL*Net to client

        373  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

发觉physical reads一下子没有了，而且consistent gets也只有个位数。

 

===============================================

 

但是感觉不对劲，又把原来的索引重建了一次，发现得到的结果也是如此。只是consistent gets多了一些

 

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=1164  )

   1    0   COUNT (STOPKEY)

   2    1     VIEW (Cost=10 Card=1 Bytes=1164)

   3    2       SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=122)

   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'USER_MESSAGE_SEND'  (Cost=1 Card=1 Bytes=122)

   5    4           INDEX (RANGE SCAN) OF 'I_SENDFLAG' (NON-UNIQUE) (Cost=1 Card=1)

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         31  consistent gets

          0  physical reads

          0  redo size

        936  bytes sent via SQL*Net to client

        373  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

 

 

===============================================

 

分析问题：

<!--[if !supportLists]-->1.  <!--[endif]-->SEND_FLAG这个字段，会根据状态进行变化的，而且几乎每条记录的FLAG都会有所变动，因此当日积月累后，索引表中，浪费的空间会原来越多（FLAG字段就几个选择项，而且最终几乎都会归到成功上去）。

<!--[if !supportLists]-->2.  <!--[endif]-->（SEND_FLAG, SEND_COUNT）索引，比（SEND_FLAG）索引效果要好，consistent gets要低，是因为SEND_COUNT放在了索引中，因此开销更省；

 

注意：

<!--[if !supportLists]-->1.  <!--[endif]-->当时手快，把索引'I_SENDFLAG'给重新编译了一次，是不是由于索引变动的缘故，无据可查了；

 

===============================================

比较２个索引，为什么选择第一个（SEND_FLAG）：

 

看一下２个索引的执行计划：

(SEND_FLAG) ：

INDEX (RANGE SCAN) OF 'I_SENDFLAG' (NON-UNIQUE) (Cost=1 Card=1)

 

(SEND_FLAG, SEND_COUNT)：

INDEX (RANGE SCAN) OF 'I_FLAGCOUNT' (NON-UNIQUE) ( Cost=2 Card=1)

 

相对而言，选择第一个索引，Cost为1，选择第二个索引Cost为2，所以选择第一个

===============================================

 

 

 

资料：

关于EXPLAIN PLAN中的COST CARD解释

<!--[if !supportLists]-->1.  <!--[endif]-->Oracle -- 用EXPLAIN PLAN 分析SQL语句（转载）: http://javabeezer.iteye.com/blog/560230

<!--[if !supportLists]-->2.  <!--[endif]-->cost[转++] : http://www.cnblogs.com/afant/archive/2008/06/02/1211968.html

<!--[if !supportLists]-->3.  <!--[endif]-->plan cost：　http://www.cnblogs.com/afant/archive/2008/06/02/1211946.html

 

 

 

COST	根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值
CARDINALITY（CARD）	根据基于开销的方法对操作所访问行数的估计值
recursive calls	循环递归次数
db block gets	请求的数据块在buffer能满足的个数
consistent gets	逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中