索引位图转换导致的SQL性能问题

1.      序

       同样的SQL，在这个测试数据库运行正常，在那个生产数据库运行却很慢，甚至在同一个数据库中，PLSQL中运行正常，但是在我们的产品中运行却很慢，相信很多技术人员都遇到过这种情况。这究竟是什么原因呢？本文介绍了如何解决一个常见的SQL执行计划问题: 误用索引位图连接,让我们一起来解开解决这类问题的谜团，一起探讨解决类似“奇怪”问题的方法和思路。

2.      现象

    某日，某市级综合医院，一线技术人员反馈，在产品中打了一个SP之后，发现一个产品功能执行非常缓慢，严重影响正常业务的开展，具体情况为，新增药品采购计划，条件选择为“自定义区间参照法”，执行后操作被挂起，等了很久才有响应。

    从跟踪反馈信息来看，其中一条SQL执行花费了4分多钟，把这条SQL跟踪出来在PLSQL中执行却很快,几乎在一秒内就执行完成了。

3.      分析

遇到这类SQL性能问题，首先应从执行计划的分析入手。而要获取正确的执行计划，不是在PLSQL中通过F5来查看，那是估算的，通常因为没有使用绑定变量的原因与实际的不一致，我们需要的是产品功能运行时产生的真实执行计划。为什么会有这种差异呢？我们先来分析这个普遍的问题，解答很多人的疑问。

    很多同学可能都遇到过：把产品中执行慢的SQL跟踪出来在PLSQL中运行却很快，这是为什么呢？

    那是因为你在PLSQL中没有使用绑定变量参数，是直接在SQL中拼入的条件值，例如：日期，这样，Oracle能够根据参数的具体值来产生更准确的执行计划，产品中用了绑定变量后，优化器会根据第一次执行时传入的参数值来评估成本，有可能就不准确，例如：第一次执行时查询了半年，优化器决定不用时间索引，后面再次查询或其他人查询即使只查一天，由于采用了绑定变量，是相同的SQL，所以会共享同一个执行计划，它仍然用之前生成好的执行计划，不使用时间索引。为了解决这个问题，在Oracle 11G中，新增了一个特性自适应游标共享ACS(adaptiver cursor sharing)，有兴趣的同学可以进一步去了解。

    那既然存在这样的问题，为什么我们要使用绑定变量呢？这是因为，我们是OLTP应用类型的系统，如果产品中不使用绑定变量，每个用户每次执行的参数值都可能不一样，就会产生文本不同的SQL，这些SQL就无法重用，每次执行都需要硬解析，对服务器的CPU资源消耗就比较高。

在我们产品中，要获取产品功能运行时的SQL执行计划，可以在客户端的Tnsnams中创建一个名为“SQLTrace12”服务器名，登录时选择这个服务器名，然后会看到提示，将会在Oracle服务器的udump目录下产生一个.trc文件。

下面，我们来看看跟踪文件中获取的SQL及其执行计划。

Select Sum(上期数量) As 上期数量 

From

 (SELECT ABS(SUM(NVL(数量, 0))) AS 上期数量  FROM 药品收发汇总 a,

  药品入出类别 b  Where a.类别id = b.id   AND b.系数 = -1   and a.库房id+0=

  :V001   AND 药品id+0= :V002   AND 日期 BETWEEN :V003 and :V004  Union All 

  Select Abs(Sum(A.入出系数 * Nvl(A.实际数量, 0) * Nvl(A.付数, 1))) As

  上期数量  From 药品收发记录 A, 药品入出类别 B  Where A.入出类别id = B.ID

  And B.系数 = -1 And A.库房id + 0 = :V005 And 药品id + 0 = :V006 And 

  审核日期 >= :V007  And 审核日期 Between :V008 And :V009)

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse       41      0.01       0.00          0          0          0           0

Execute     41      0.00       0.01          0          0          0           0

Fetch       41    274.34     275.39          0    3186894          0          40

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

total      123    274.35     275.42          0    3186894          0          40

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: FIRST_ROWS

Parsing user id: 83 

 

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=78959 pr=0 pw=0 time=6840886 us)

      2   VIEW  (cr=78959 pr=0 pw=0 time=6840840 us cost=2180 size=26 card=2)

      2    UNION-ALL  (cr=78959 pr=0 pw=0 time=6840833 us)

      1     SORT AGGREGATE (cr=157 pr=0 pw=0 time=4229 us)

      2      FILTER  (cr=157 pr=0 pw=0 time=1499 us)

      2       NESTED LOOPS  (cr=157 pr=0 pw=0 time=1492 us cost=13 size=34 card=1)

      3        NESTED LOOPS  (cr=154 pr=0 pw=0 time=2236 us cost=13 size=34 card=1)

      3         TABLE ACCESS BY INDEX ROWID 药品收发汇总 (cr=152 pr=0 pw=0 time=2195 us cost=12 size=26 card=1)

      3          INDEX RANGE SCAN 药品收发汇总_UQ (cr=149 pr=0 pw=0 time=2156 us cost=11 size=0 card=1)(object id 72378)

      3         INDEX UNIQUE SCAN 药品入出类别_PK (cr=2 pr=0 pw=0 time=24 us cost=1 size=0 card=1)(object id 72371)

      2        TABLE ACCESS BY INDEX ROWID 药品入出类别 (cr=3 pr=0 pw=0 time=19 us cost=1 size=8 card=1)

      1     SORT AGGREGATE (cr=78802 pr=0 pw=0 time=6836570 us)

      1      FILTER  (cr=78802 pr=0 pw=0 time=6836549 us)

      1       NESTED LOOPS  (cr=78802 pr=0 pw=0 time=6836541 us cost=2168 size=35 card=1)

   9738        NESTED LOOPS  (cr=77245 pr=0 pw=0 time=291813 us cost=2168 size=35 card=32640000)

     30         TABLE ACCESS FULL 药品入出类别 (cr=6 pr=0 pw=0 time=161 us cost=3 size=240 card=30)

   9738         BITMAP CONVERSION TO ROWIDS (cr=77239 pr=0 pw=0 time=6788430 us)

      6          BITMAP AND  (cr=77239 pr=0 pw=0 time=6777932 us)

     27           BITMAP CONVERSION FROM ROWIDS (cr=76025 pr=0 pw=0 time=11913749 us)

38084220            INDEX RANGE SCAN 药品收发记录_IX_入出类别ID (cr=76025 pr=0 pw=0 time=47035466 us cost=2 size=0 card=1088000)(object id 72386)

     18           BITMAP CONVERSION FROM ROWIDS (cr=1214 pr=0 pw=0 time=98025 us)

 179424            SORT ORDER BY (cr=1214 pr=0 pw=0 time=277059 us)

 179424             INDEX RANGE SCAN 药品收发记录_IX_审核日期 (cr=1214 pr=0 pw=0 time=246369 us cost=21 size=0 card=1088000)(object id 203153)

      1        TABLE ACCESS BY INDEX ROWID 药品收发记录 (cr=1557 pr=0 pw=0 time=33031 us cost=2168 size=27 card=1)

   

    从上面的执行计划红色部分可以看到，慢的主要原因是用到了索引位图转换，对两个索引“药品收发记录_IX_入出类别ID”和“药品收发记录_IX_审核日期”进行了位图转换再连接，前者是一个非常低效的索引，它仅仅是一个为外键而创建的索引，其选择率非常低（唯一值少），所以会非常耗时，执行计划中的“time=47035466 us”就是证明。

       为什么Oracle会选择这样明显不合理的执行计划呢？

 

       出现这种现象的原因是：Oracle的cbo优化器是根据cost的大小估算来选择合适的执行计划，当它认为通过bitmap CONVERSION的方式执行的时候，cost会更小，它就会选择使用这样的执行计划。一般出现这样的情况，都是因为表上存在不适当的索引，或者索引的统计信息收集有误（例如：采样百分比太小等）导致，特别是对选择率不高的列建立了索引，Oracle就有可能选择这样的索引来进行位图转换。

       这个问题，比较典型，至少我遇到过3次了。第一次时我不在场，某医院升级10.32，电子病历的一个SQL因为这种索引位图转换导致住院业务停了半天，虽然当时有Oracle第三方支持商在，查到了具体的SQL但没有分析出原因，也没有找到从Oracle方面调整的解决办法，只好让开发人员修改SQL来解决。

       第二次时，也是升级后出现的，当时我在医院升级现场，试了重新收集统计信息等各种方式，仍然没有解决，但那是一个次要业务功能的SQL，并且虽然慢，仍然在可接受的范围内，当时有其他更重要的任务 ，所以没有去管它。

      

       严格来说，我认为这种执行计划的偏差，应该算Oracle优化器的BUG，虽然Oralce没有承认过这一点。

更多信息可参考：

惜分飞：http://www.xifenfei.com/1531.html

盖大师的文章：http://www.eygle.com/archives/2011/12/bitmap_conversion_cpu.html

 

       为什么这类问题通常在升级后出现？因为升级后，产品中的很多SQL变化了（例如：加了条件），执行计划要重新产生。即使SQL没有变，但是可能大量的对象发生了变化，原有SQL执行计划就会失效，在Oracle重新产生执行计划时，会根据当前的数据情况、对象、环境等信息重新评估来选择一个它认为最优的执行计划，由于各种原因，可能就选中了一个低效的执行计划，导致SQL运行缓慢。

比较常见的原因就是，收集统计信息时，使用了采样自动百分比(Oracle通常会选择0.0X%)，或者指定了一个较低的百分比（例如：常见的3%）,这种采样得到的数据用于成本的评估可能就会出现偏差，从而导致优化器选择了一个它认为最优的执行计划。所以，我们会发现，同样的SQL，在有的数据库运行速度正常，有的数据库运行却很慢，很多时候就是因为对象的统计信息收集方面的原因。（还有数据差异、数据库参数等其他原因）

4.      解决

       好了，我们明白了问题产生的原因及原理，解决这个问题，方法有多种。

       最初，开发人员给我看到这个SQL时，其实我已经知道，最简单、最快的解决办法就是在SQL中的这个地方：a.类别id = b.id，等号的左边添加一个”+0”即可，这样将屏蔽“药品收发记录_IX_入出类别ID”这个低效的索引，从而避免索引位图连接。

       但是，如果每次都让开发人员这样改SQL，我们程序中还有大量这类写法，以后再次遇到这种情况就还需要不停的改，这不是解决问题的根本，所以，我要求远程连接到数据库，以便执行一些诊断和优化调整。

 

       先后试了以下方法：

1)        加大采样百分比至30%来重新收集”药品收发记录”表的统计信息；

2)        删除索引”药品收发记录_IX_入出类别ID”的统计信息后，重新收集；

3)        重建索引”药品收发记录_IX_入出类别ID”；

4)        指定索引的统计信息，将numdist调 低为1，将numrows，clstfct指定为原值的100倍，目的是加大该索引的成本，让Oracle不要选择它。

 

       但是这些方法都没有效果，最后，解决的方法还是第4种，只把原值加大了1万倍，也就是让该索引的成本足够大，然后欣喜的看到执行计划变了，没有再使用那个低效的索引，也没有出现索引位图转换。

       最后，验证了一下产品功能，问题得到解决。

 

       相关脚本参考：

SQL> exec dbms_stats.set_index_stats(ownname => 'ZLHIS',indname => '药品收发记录_IX_入出类别ID',
                   no_invalidate => false,numdist => 10,numrows => 1234567890000,clstfct =>1234567890000 );

SQL> exec dbms_stats.lock_table_stats(ownname => 'ZLHIS',tabname => '药品收发记录');

      

参数说明：当clstfct与numrows的值接近时，表明索引指向的数据行分布比较离散，通过索引访问的成本较高。

之所以锁定统计信息，是为了避免Oracle自动收集统计信息作业执行后将上面指定的值覆盖。

 

       除了重新收集或调整统计信息(有时可能是缺省采样导致的统计信息偏差，重新收集时注意加大采样百分比)和指定索引的统计信息之外，解决这个问题的办法还有下面几种：

1)      修改SQL，包括加提示字指定索引，+0等方式来避免使用某个索引。

2)      修改隐含参数_b_tree_bitmap_plans

之前在XX医院试过一次，没有效果，当时的版本是Windows平台的10.2.0.5，后来，有其他同事在另外的环境用过一次，证实了可以解决这类问题。

下面把方法贴出来，将来遇到时，大家可以在其他平台或其他版本上再试试。

SQL> alter system set "_b_tree_bitmap_plans"=false;

查询隐含参数：

Select x.Ksppinm Name, y.Ksppstvl Value, y.Ksppstdf Isdefault,

       Decode(Bitand(y.Ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') Ismod,

       Decode(Bitand(y.Ksppstvf, 2), 2, 'TRUE', 'FALSE') Isadj

From Sys.X$ksppi X, Sys.X$ksppcv Y

Where x.Inst_Id = Userenv('Instance') And y.Inst_Id = Userenv('Instance') And x.Indx = y.Indx And

      x.Ksppinm Like '%_b_tree_bitmap_plans%'

Order By Translate(x.Ksppinm, ' _', ' ')

 

       如果可以改SQL，也可以在SQL中加提示字：
       /*+ opt_param('_b_tree_bitmap_plans', 'false') */
       （可以改的话，也没有必要这么麻烦，直接用第1种方法）

 

3)      重建索引为复合索引

原理跟指定索引统计信息差不多，就是加大某个索引的成本，让Oracle不要选择该索引。

参考：杭州美创http://blog.mchz.com.cn/?p=2205

5.      小结

后来，还有其他渠道的同事也遇到了类似的问题，说明这个问题相对比较容易遇到，主要还是统计信息收集导致优化器对成本的误解。这种看似“奇怪”的问题，只要我们把原理搞清楚了，其实解决的办法不止一种，SQL的优化，重要的就是明白优化器估算成本的基本原理。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/117319/viewspace-1431126/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/117319/viewspace-1431126/