在平衡中追求性能极限【ACHIEVE MAX PERFORMANCE IN BALANCE VIEW】

推荐原创

Linkyoyo 2007-12-19 18:51:06 博主文章分类：云足迹 ©著作权

文章标签 ORACLE 数据库休闲 PERFORMANCE BALANCE 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者Linkyoyo的原创作品，请联系作者获取转载授权，否则将追究法律责任

本文旨在通过一次对某个SQL优化的全过程，来说明在追求性能的同时要用更为全局的眼光来看待某些具体的优化操作。虽然BAD SQL是造成数据库性能低下的“元凶”之一，但是对于要处理成千上万个SQL的数据库来讲，“大数据库”观念还是非常必要的。
    同时本文还综合介绍了优化SQL的一些思想和ORACLE提供的一些实用优化功能。
    1.背景说明：
    数据库版本：ORACLE 10.2.0.1
    操作系统版本：HP-UX 11.23 IA64
    在数据库例行的检查过程中，发现一个SQL语句进行了大量的全表扫描！其中DISKREAD过十亿次。赶快找到这句SQL：

SQL> SELECT COUNT(DISTINCT(TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4)))
  2    FROM XXX_REPORT
  3   WHERE RECEIVER = '4444444444'
  4     AND STATUS_DETAIL = '6666'
  5     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) <
  6         TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4)
  7     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) >=
  8         TO_CHAR((TO_DATE(TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4),'yymmdd') - 2),'yymmdd');

     注：部分变量为绑定变量，这里填写了一个伪装值。表名也进行了适当处理。-;)
         XXX_REPORT是一张PARTION表，其数据量在15000000条左右。全表超过1.2G。
     2.

SQL> SELECT COUNT(DISTINCT(TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4)))
  2    FROM XXX_REPORT
  3   WHERE RECEIVER = '44444444444'
  4     AND STATUS_DETAIL = '6666'
  5     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) <
  6         TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4)
  7     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) >=
  8         TO_CHAR((TO_DATE(TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4),'yymmdd') - 2),'yymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 1107940823

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |    39 |  6198   (2)| 00:01:15 |       |       |        |      |            |
|   1 |  SORT GROUP BY             |               |     1 |    39 |            |          |       |       |        |      |            |
|*  2 |   PX COORDINATOR           |               |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001      |     1 |    39 |            |          |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT GROUP BY          |               |     1 |    39 |            |          |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE            |               |     1 |    39 |            |          |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH         | :TQ10000      |     1 |    39 |            |          |       |       |  Q1,00 | P->P | HASH       |
|   7 |        SORT GROUP BY       |               |     1 |    39 |            |          |       |       |  Q1,00 | PCWP |            |
|*  8 |         FILTER             |               |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|   9 |          PX BLOCK ITERATOR |               |     1 |    39 |  6198   (2)| 00:01:15 |     1 |     4 |  Q1,00 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| XXX_REPORT |     1 |    39 |  6198   (2)| 00:01:15 |     1 |     4 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
   8 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
  10 - filter("RECEIVER"='44444444444' AND "STATUS_DETAIL"='6666' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)<TO_CHAR(SYSDATE@!,'yy')||'1105' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)>=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))

    从执行计划上来看，即使使用了PARALLEL，但是全表的数据量太大。所以执行时间还是很长，应该超过1分15秒！
    寻找使用索引的可能。如果直接建立索引，尤其在生产库上其影响是非常巨大的。还好ORACLE提供了创建“虚拟索引”（VIRTUAL INDEX）的功能。(即使是这样也要慎重使用)
     建立VIRTUAL INDEX:

SQL> create index test_virtual on XXX_REPORT( SUBSTR(MESSAGEID, 1, 4)) nosegment;

Index created.

     注：该索引还是个基于函数的索引。
     接下来就是让ORACLE的优化器“知道”这个SESSION中可以考虑VIRTUAL INDEX了：

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

OK!再来看看我的执行计划：

Execution Plan
----------------------------------------------------------
Plan hash value: 378868617

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |     1 |    39 |    11  (73)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                       |               |     1 |    39 |            |          |       |       |
|*  2 |   FILTER                             |               |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| XXX_REPORT |     1 |    39 |    11  (73)| 00:00:01 |     4 |     4 |
|*  4 |     INDEX RANGE SCAN                 | TEST_VIRTUAL  |   310 |       |    10  (80)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yym
              mdd'))
   3 - filter("RECEIVER"='44444444444' AND "MESSAGEID">='1217000000' AND "STATUS_DETAIL"='6666')
   4 - access(SUBSTR("MESSAGEID",1,4)>='1217')
       filter(TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)<TO_CHAR(SYSDATE@!,'yy')||'1105' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)>=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,
              'yymmdd'))

    预计的执行时间1秒！执行成本从6198下降到11！
    Great!
    完成？再想想，和研发同事确认，这个查询有个时限性即不能早于当天-1 ！别忘记这是个PARTITION的表，PARTITION的关键字段就是要求时间的字段（下面显示的SQL语句最后增加了一个AND MESSAGEID>='1217000000'）。改写这个SQL语句并查看执行计划：

SQL> SELECT /*test1*/ COUNT(DISTINCT(TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4)))
  2    FROM XXX_REPORT
  3   WHERE RECEIVER = '44444444444'
  4     AND STATUS_DETAIL = '6666'
  5     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) <
  6         TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4)
  7     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) >=
  8         TO_CHAR((TO_DATE(TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4),'yymmdd') - 2),'yymmdd')
  9     AND MESSAGEID>='1217000000';

Execution Plan
----------------------------------------------------------
Plan hash value: 378868617

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |     1 |    39 |    11  (73)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                       |               |     1 |    39 |            |          |       |       |
|*  2 |   FILTER                             |               |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| XXX_REPORT |     1 |    39 |    11  (73)| 00:00:01 |     4 |     4 |
|*  4 |     INDEX RANGE SCAN                 | TEST_VIRTUAL  |   310 |       |    10  (80)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------

看！ORACLE经过再三思考还是决定用这个VIRTUAL INDEX(TEST_VIRTUAL)，成本低呗！不行去掉这个VIRTUAL INDEX再试试：

SQL> drop index test_virtual;

Index dropped.

再看看执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 2704931136

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |    39 |    95   (2)| 00:00:02 |       |       |        |      |            |
|   1 |  SORT GROUP BY             |               |     1 |    39 |            |          |       |       |        |      |            |
|*  2 |   PX COORDINATOR           |               |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001      |     1 |    39 |            |          |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT GROUP BY          |               |     1 |    39 |            |          |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE            |               |     1 |    39 |            |          |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH         | :TQ10000      |     1 |    39 |            |          |       |       |  Q1,00 | P->P | HASH       |
|   7 |        SORT GROUP BY       |               |     1 |    39 |            |          |       |       |  Q1,00 | PCWP |            |
|*  8 |         FILTER             |               |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|   9 |          PX BLOCK ITERATOR |               |     1 |    39 |    95   (2)| 00:00:02 |     4 |     4 |  Q1,00 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| XXX_REPORT |     1 |    39 |    95   (2)| 00:00:02 |     4 |     4 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------

   2 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
   8 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
  10 - filter("STATUS_DETAIL"='4442' AND "RECEIVER"='44444444444' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)<TO_CHAR(SYSDATE@!,'yy')||'1105' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)>=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))

    看！执行该计划虽然不如执行使用索引的好，但是95的执行成本不比11的高多少，执行的预计时间两秒！谁又会去感觉1秒和2秒的差别呢？除非我执行了几百上千次。好就是用这个方法。在实际的执行中看，确实得到了很好的效果。
    这里，为什么我在看到索引的“优秀”执行计划时还要再想想其他办法呢？原因很简单，这张表记录很多，且还会不断有大量的数据进入，那么就会面临着为维护一个索引而需要高额的数据库资源耗费！索引是好东西，但同时也是个“双刃剑”。
    通过改写该SQL后，虽然SQL还是执行了全表扫描，但是新增的条件，让ORACLE大幅降低了全扫数据的数量！即很有可能为原来的1/N(N为PARTITION的数量)。这种方法虽然不是最快的方法，但它并没有增加数据库额外的负担！所以综合来看，它是最好的方法。
    BALANCE!
    通过更为全局的观点，来达到整个数据库性能的综合提升，而不是在某一点性能的突出表现，来导致数据库整体性能的下降。
    另外，本文中提到的VIRTUAL INDEX技术，为DBA们提供了一个低成本优化SQL的办法，否则真是建立了一个INDEX而发现ORACLE并没有使用，那成本就太大了，如果在生产库上将是个灾难呀。

    我们需要在“平衡”中追求性能，而追求性能的脚步应该是不会停止的......

    附：
    很抱歉，因为没有控制好显示的宽度，所以执行计划稍显凌乱。
    真心的欢迎大家指正！-:)