表设计乱用LOB字段，查询带来严重的性能影响

今天反映一个SQL查询时间大概2-3分钟：

SELECT *  FROM (SELECT ROWNUM AS ROWNUMBER__, T.*

          FROM (Select T1.CONSULTINGCODE AS "ConsultingCode",

                       T1.CATEGORY AS "Category",

                       T1.CUSCODE AS "CusCode",

                       T1.ORDERCODE AS "OrderCode",

                       T1.WARECODE AS "WareCode",

                       DECRYPTBYKEY(T1.MOBILEPHONE) AS "MobilePhone",

                       DECRYPTBYKEY(T1.EMAILENCRYPT) AS "EmailEncrypt",

                       T1.ASSIGNTIME AS "AssignTime",

                       T1.REPLIER AS "Replier",

                       T1.REPLYCODE AS "ReplyCode",

                       T1.REPLYDATE AS "ReplyDate",

                       T1.BYWAY AS "ByWay",

                       T1.CREATETIME AS "CreateTime",

                       T1.EVALUATE AS "Evaluate",

                       T1.EXPIREMAN AS "ExpireMan",

                       T1.EXPIREREASON AS "ExpireReason",

                       T1.CONSULTINGTYPEID AS "ConsultingTypeID",

                       T1.STATUS AS "Status",

                      T1.QUESTION AS "Question",

                       T1.MAILCONTENTS AS "MailContents",

                      T1.REPLYCONTENT AS "ReplyContent",

                       T1.ENCEMAIL AS "EncEmail"

                  From mbs7_crm.KH_Consulting T1

                  left Join mbs7_crm.KH_Customer T2

                    on T1.CUSCODE = T2.CUSCODE

                ORDER BY T1.STATUS ASC, T1.CREATETIME ASC) T

         WHERE "CreateTime" >= date '2013-9-1'

           AND "ReplyCode"  IN ('128')

           AND "CreateTime" <= timestamp '2013-9-30 23:59:59'

           AND ROWNUM <= 10000) TEMP

                     WHERE ROWNUMBER__ > 0

分析：

该语句从执行计划来看，走了时间索引，返回记录是1千多，如果全部查询出来进度很慢（分页的翻页操作很慢），后来发现该语句的性能主要是消耗在：

字段"Question"，“MailContents”,"Category"和"ReplyContent"上，把这4个字段注释小，查询时间在5s内，后来发现这4个字段为CLOB字段类型,因为CLOB字段这种字段类型的存储方式是比较复杂的，如果该CLOB字段内容超出一定值，会用指针指向另一个SEGMENT，把内容存放在新的SEGMENT; 这样当访问的时候，会出现IO次数增加,从而影响性能，并且CLOB类型有独立的回滚机制，当一致性读的行数较多时，响应时间很慢，而就算存储的内容较小，CLOB本身也会调用系统内部的函数进行匹配和寻址，也是很消耗CPU时间的.

解决方案：

经与开发人员沟通，该表的此四个字段其实实际存储内容远没有超出4000个字节（varchar2的最大长度），当初设计的时候没有考虑精准，于是计划把这些字段类型按照下列方法重新调整：

alter table mbs7_crm.KH_Consulting add  (QUESTION2 varchar2(2000));

update mbs7_crm.KH_Consulting set QUESTION2=dbms_lob.substr(QUESTION,4000);

alter table mbs7_crm.KH_Consulting drop column QUESTION;

alter table mbs7_crm.KH_Consulting rename column QUESTION2 to QUESTION;

修改后，重新查询，在5S内。

启发：
开发的时候别为了方便随便滥用CLOB,LOB等大字段类型，当数据访问量很大的时候，带来的性能影响将是巨大的。

天凯科技-数据库技术支持
7*24小时提供数据库运维与优化服务，十年DBA实战经验，长期服务于国内关键行业，深受好评！
服务热线/微信：13926108245
本企业定期分享DBA实战处理经验，同时分享精品视频教程，更多资源请点击访问"天凯运维资讯"获得!