一个比较经典的语句优化

情况：最近有个生产系统数据库CPU忽然变得非常高，我今天看了一下，发现有13个latch free，(如何查看latch free)分析latch free的语句，主要是这个语句，咨询开发人员，这是一个报表语句

 语句如下：

SELECT COUNT(DISTINCT CUSTOMIDN)

 FROM (SELECT T1.CUSTOMIDN, COUNT(*)

         FROM RPT_EHOME_FAULTHIS T1, T_BB_XLLX_JX C, RPT_EHOME_FAULTHIS T2

        WHERE T1.DISPATCHFLAG = '1'

          AND T1.CUSTOMTYPE = 'B'

          AND T2.DISPATCHFLAG= '1'

          AND T2.CUSTOMTYPE = 'B'

          AND T1.JX = C.JX

          AND C.G_JX = :b1

          AND C.XLLX = T1.XLLX

          AND T1.CUSTOMIDN = T2.CUSTOMIDN

          AND T1.JX = T2.JX

          AND T1.RECEPTTIME >= :b2

          AND T1.RECEPTTIME < :b3

          AND ((T1.PRODUCTTYPEP = '1' AND

              '1' IN (T2.PRODUCTTYPEA, T2.PRODUCTTYPETV, T2.PRODUCTTYPEE)) OR

              (T1.PRODUCTTYPEA = '1' AND

              '1' IN (T2.PRODUCTTYPEP, T2.PRODUCTTYPETV, T2.PRODUCTTYPEE)) OR

              (T1.PRODUCTTYPETV = '1' AND

              '1' IN (T2.PRODUCTTYPEA, T2.PRODUCTTYPEP, T2.PRODUCTTYPEE)) OR

              (T1.PRODUCTTYPEE = '1' AND

              '1' IN (T2.PRODUCTTYPEA, T2.PRODUCTTYPETV, T2.PRODUCTTYPEP)))

        GROUP BY T1.CUSTOMIDN

       HAVING COUNT(*) >= 1) A

       

查看执行计划：

 

 

 

分析过程：

(1) 首先查到RPT_EHOME_FAULTHIS 　数据量比较大

(2) 我注意到这个执行计划，多次读到

IDX_RPT_EHOME_FAULTHIS_2和IDX_RPT_EHOME_FAULTHIS_5这两个索引，分别对应列DISPATCHFLAG和CUSTOMTYPE，这两个列的区分度非常低，所以我建议使用RECEPTTIME作为索引IDX_RPT_EHOME_FAULTHIS_6。

 

我先测试一下：用to_char方式屏蔽掉那两个区分度不高的索引，用RECEPTTIME为索引。

SELECTCOUNT(DISTINCTCUSTOMIDN)

 FROM(SELECTT1.CUSTOMIDN,COUNT(*)

         FROMRPT_EHOME_FAULTHIS T1, T_BB_XLLX_JX C, RPT_EHOME_FAULTHIS T2

        WHEREto_char(T1.DISPATCHFLAG) ='1'

          ANDto_char(T1.CUSTOMTYPE) ='B'

          ANDto_char(T2.DISPATCHFLAG) ='1'

          ANDto_char(T2.CUSTOMTYPE) ='B'

          ANDT1.JX = C.JX

          ANDC.G_JX = :b1

          ANDC.XLLX = T1.XLLX

          ANDT1.CUSTOMIDN = T2.CUSTOMIDN

          ANDT1.JX = T2.JX

          ANDT1.RECEPTTIME >= :b2

          ANDT1.RECEPTTIME < :b3

          AND((T1.PRODUCTTYPEP ='1'AND

              '1'IN(T2.PRODUCTTYPEA, T2.PRODUCTTYPETV, T2.PRODUCTTYPEE))OR

              (T1.PRODUCTTYPEA ='1'AND

              '1'IN(T2.PRODUCTTYPEP, T2.PRODUCTTYPETV, T2.PRODUCTTYPEE))OR

              (T1.PRODUCTTYPETV ='1'AND

              '1'IN(T2.PRODUCTTYPEA, T2.PRODUCTTYPEP, T2.PRODUCTTYPEE))OR

              (T1.PRODUCTTYPEE ='1'AND

              '1'IN(T2.PRODUCTTYPEA, T2.PRODUCTTYPETV, T2.PRODUCTTYPEP)))

        GROUPBYT1.CUSTOMIDN

       HAVINGCOUNT(*) >=1) A

 

测试结果：

测试后3秒就完成，以前的语句是非常慢的，很长时间都没数据返回。证明sql优化有效果。

 

新的执行计划：

 

 

让开发人员删除IDX_RPT_EHOME_FAULTHIS_2和IDX_RPT_EHOME_FAULTHIS_5索引。

 

最后删除了在系统等待的锁(如何删除？)， OK ， 系统 CPU 使用率降下来了。

引自: http://blog.ixpub.net/505500/viewspace-405147

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

知识点补充： latch free几大事件参考

latch free争用中的 
library cache latch 三种情况
1. 硬解析太多，查看v$sqlarea
2. 共享池设置太大
3. 语句版本数太多

cache buffers chains latch争用原因
1. 低效的sql语句--优化sql语句
在高并发系统中，latch free时间可能因此非常明显。典型的情况是，应用程序开启多个并发会话执行相同的低效sql，并且访

问同样的数据集。

2. 热点块

3. 过长的hash chain，也就是cache buffers chain

Cache buffers lru chain latch争用
这是buffer的lru规则的列表，指向其他列表比如lru，lruw和ckpt-q。
Cache buffers laru cahin latch的争用，主要表现为由于低效的sql语句导致数据缓冲区过度活跃。全表扫描和对某些选择性

较差的大索引的反复扫描是造成cache buffers laru cahin latch争用的主要原因。解决办法是，查找latch free等待事件中关

于cache buffers lru chain latch相关的sql语句（在oracle10g中，已经变成一个独立的cache buffers lru chain等待事件）

，优化这些sql，降低其物理读和逻辑读。

row cache objects latch
该latch是用来保护数据字典缓冲区。最好的办法是降低对前面的查询结果中一些热点数据字典对象的访问。

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

转载于:http://blog.itpub.net/9390331/viewspace-692802/