记一次数据库的优化之隐式转换的破坏力

    今天QQ突然闪动，发现是一个地市的经支人员说他们的数据库很慢，并给我发来了一段SQL

SELECT C.BSS_ORG_ID_2,         A.BILLING_CYCLE_ID,         ...        SUM(a.charge) / 10000 charge   FROM         DCS_AGGR2013 A,         MONTH_USER B,         QUDAO_YX_DATE C  WHERE A.SERV_ID = B.SERV_ID    AND B.SERV_ID = C.SERV_ID    AND A.BILLING_CYCLE_ID = '11303'    AND B.CITY_ITEM = '1'    AND b.product_id IN (9, 10, 11, 12, 13, 14, 350000004)    AND C.BSS_ORG_ID_2 IN ('352', '10015599')  GROUP BY C.BSS_ORG_ID_2, A.BILLING_CYCLE_ID;

说是执行了很久都没有结束，我马上用sqldeveloper登上数据库看了一下执行计划，发现这个语句摆了很大的一个乌龙，所有加了引号的等式的字段实际上是number型字段，不过这个问题到不大，因为从实际的执行计划来看，这些后边加了引号的，实际上应该是内部做了转换，转成了数字型，这个对于语句几乎没有影响。可当看到b.product_id IN (9, 10, 11, 12, 13, 14, 350000004)这一句的时候问题就来了，执行计划的截图如下

所有的product_id字段都被转换成了to_number(product_id)，这样势必会造成额外的CPU计算，但是看了三个表，除了DCS_AGGR2013有1个多GB，另外两个表只有3~4百M，按说这样大小的三张表还不至于拖垮数据库吧，于是我就给地市的人员反馈了让他按照我指出来的情况，重新修改一下语句，他的第一反应是即便是对一个字段进行了to_number也不应该影响的这么严重，CPU一直在60%以上，实际上刚开始我也认同他的说法。确实觉得一个sql语句不至于对系统造成太大的问题，于是要来了服务器的用户和密码登陆到数据库上取了一份当天0点至当前的war报告下来，copy到本机后，用chrome浏览器打开，发现居然有乱码，查询了数据库的字符集设置是ZHS16GBK没有问题，百思不得其解，后来才注意到可能是浏览器的encoding的设置问题（此系后话按下不表），然后重新取了一份还是一样，不过从awr上发现执行时间最长的sql是系统的job AUTO_SPACE_ADVISOR_JOB在收集对象的使用信息造成的，查询了dba_scheduler_jobs后发现果然还在运行，于是果断的给disable掉。

begin

dbms_scheduler.disable('SYS.AUTO_SPACE_ADVISOR_JOB');

end;

然后给地市的反馈说我禁用掉了自动空间顾问的job，地市的人居然说既然已经禁用掉了为什么CPU还这么高？还这么慢？ 

我  &……%&……*& 一阵无语后还是耐心给解释了说这个原因可能有很多，不是发一个啥子命令出来 数据库就运转如飞了。

然后接着看到最消耗CPU的sql，前三条里面居然有两个就是他发给我的那个语句还包括了一些中文乱码，因为是乱码，所以我还把sql_id拿到sqldeveloper里面去查了一下才得以确认

select * from v$sql where sql_id = 'apx6wum2544pc';

接着看到第一条最消耗CPU资源的是一个存储过程，这个存储过程当中居然就是很多个他发个我的那种类似的sql的集合，于是把这个发现也提交给他，自己也仔细看了一下这个过程，还是跟那个SQL一样的语句，在看一下之前的执行计划，这一次没有用工具来看，而是打开了sqlplus，我还比较喜欢在sqlplus下看执行计划

SQL> set autot trace exp

SQL> select C.BSS_ORG_ID_2,

            A.BILLING_CYCLE_ID,

            sum(a.charge)/10000 charge

       from DCS_AGGR2013 A,

            MONTH_USER  B,

            QUDAO_YX_DATE C

      WHERE A.SERV_ID=B.SERV_ID

        AND B.SERV_ID=C.SERV_ID

        AND A.BILLING_CYCLE_ID=11303

        AND B.CITY_ITEM=1

        and b.product_id in ('9','10','11','12','13','14','350000004')

        AND C.BSS_ORG_ID_2 IN (352,10015599)

      GROUP BY C.BSS_ORG_ID_2,A.BILLING_CYCLE_ID;

Execution Plan

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

Plan hash value: 658912135

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

| Id  | Operation              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |                        |     1 |    63 |  40432  (2)| 00:08:06 |

|   1 |  HASH GROUP BY         |                        |     1 |    63 |  40432  (2)| 00:08:06 |

|*  2 |   HASH JOIN            |                        |     1 |    63 |  40431  (2)| 00:08:06 |

|*  3 |    TABLE ACCESS FULL   |    MONTH_USER          | 56887 |   888K|   7622  (2)| 00:01:32 |

|   4 |    MERGE JOIN CARTESIAN|                        |   230K|    10M|  32806  (2)| 00:06:34 |

|*  5 |     TABLE ACCESS FULL  |     DCS_AGGR2013       |     1 |    22 |  16704  (2)| 00:03:21 |

|   6 |     BUFFER SORT        |                        |   460K|    10M|  16102  (1)| 00:03:14 |

|*  7 |      TABLE ACCESS FULL |          QUDAO_YX_DATE |   460K|    10M|  16102  (1)| 00:03:14 |

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

Predicate Information (identified by operation id):

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

   2 - access("B"."SERV_ID"=TO_NUMBER("A"."SERV_ID") AND

              "B"."SERV_ID"=TO_NUMBER("C"."SERV_ID"))

   3 - filter("B"."CITY_ITEM"=1 AND ("B"."PRODUCT_ID"='10' OR "B"."PRODUCT_ID"='11' OR

              "B"."PRODUCT_ID"='12' OR "B"."PRODUCT_ID"='13' OR "B"."PRODUCT_ID"='14' OR

              "B"."PRODUCT_ID"='350000004' OR "B"."PRODUCT_ID"='9'))

   5 - filter("A"."BILLING_CYCLE_ID"=11303)

   7 - filter("C"."BSS_ORG_ID_2"=352 OR "C"."BSS_ORG_ID_2"=10015599)

Note

-----

   - dynamic sampling used for this statement

从执行计划当中，可以看到B表的SERV_ID和A、C表的SERV_ID做关联的时候，其他两个表的SERV_ID居然是to_number，一开始在sqldeveloper当中查看执行计划的时候给忽略了，马上把这个问题反馈了出来，然后建议让他把A、C两个表重建一下，不要把SERV_ID设置成了varchar2型了。这个时候去查看了一下服务器，发现CPU一下降到了5%，问了之后原来他把那个过程给停掉了，看来导致CPU上升的元凶果然就是这个存储过程里面的语句，最主要的就是不恰当的字段类型的隐式转换，另外还发现了一句  

dynamic sampling used for this statement

实际是用到了动态采样，这就说明表没有收集统计信息，正好地市的人又给我反馈说他把这个三个表关联的SQL改成了两个关联产生结果集后再跟第三个表做关联这样不到十分钟就出来了，于是我考虑给表收集一下统计信息

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(

    OWNNAME          => 'STAT',  

    TABNAME          => 'DCS_AGGR2013', 

    ESTIMATE_PERCENT => 50,  

    METHOD_OPT       => 'FOR ALL INDEXED COLUMNS',

    DEGREE           => 4,  

    CASCADE          => TRUE);

END;

三个表都收集之后，再看执行计划：

Execution Plan

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

Plan hash value: 3658125727

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

| Id  | Operation              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |                        |     1 |    64 |  48642  (2)| 00:09:44 |

|   1 |  HASH GROUP BY         |                        |     1 |    64 |  48642  (2)| 00:09:44 |

|*  2 |   HASH JOIN            |                        |     1 |    64 |  48641  (2)| 00:09:44 |

|   3 |    MERGE JOIN CARTESIAN|                        |  5427 |   254K|  41018  (2)| 00:08:13 |

|*  4 |     TABLE ACCESS FULL  |     DCS_AGGR2013       |     1 |    22 |  24936  (2)| 00:05:00 |

|   5 |     BUFFER SORT        |                        | 10854 |   275K|  16082  (1)| 00:03:13 |

|*  6 |      TABLE ACCESS FULL |          QUDAO_YX_DATE | 10854 |   275K|  16082  (1)| 00:03:13 |

|*  7 |    TABLE ACCESS FULL   |    MONTH_USER          | 56894 |   888K|   7622  (2)| 00:01:32 |

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

Predicate Information (identified by operation id):

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

   2 - access("B"."SERV_ID"=TO_NUMBER("A"."SERV_ID") AND

              "B"."SERV_ID"=TO_NUMBER("C"."SERV_ID"))

   4 - filter("A"."BILLING_CYCLE_ID"=11303)

   6 - filter("C"."BSS_ORG_ID_2"=352 OR "C"."BSS_ORG_ID_2"=10015599)

   7 - filter("B"."CITY_ITEM"=1 AND ("B"."PRODUCT_ID"='10' OR "B"."PRODUCT_ID"='11' OR

              "B"."PRODUCT_ID"='12' OR "B"."PRODUCT_ID"='13' OR "B"."PRODUCT_ID"='14' OR

              "B"."PRODUCT_ID"='350000004' OR "B"."PRODUCT_ID"='9'))

可以看到跟前面的比较之下Rows和Bytes列评估出来的行数都不太相同，最后根据filter，给他建议让创建索引并在创建之后重新收集统计信息，此次优化就告一段落了，完了这个老兄还一个劲的问 你今天处理完了，系统是不是就会好起来了，我说这个主要还是取决于你们对代码的编写情况了，最好在写的时候多看看执行计划有没有什么异常的尽量都处理一下。

the end.

后记：话说那个awr报告在chrome上打开中文变乱码的问题，实际上是encoding的设置问题，修改默认值需要在Customize and control Google Chrome=>Settings=>Show advanced settings...=>Web content=>Customize fonts=>Encoding选择Chinese Simplified (GBK) 

或者直接在地址栏输入 chrome://settings/fonts 然后Encoding中选择Chinese Simplified (GBK)  再次打开awr报告，乱码就解决了。

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

转载于:http://blog.itpub.net/12216142/viewspace-757920/