性能调优：看看这个匪夷所思的执行计划。

业务人员反映系统很慢。登录操作系统，用TOP命令查看，发现大量的CPU都飙升到了100%.根据进程PID一步步找到执行的SQL，都是一个SQL导致的，如下：
SELECT AB14.BAE204 AB14_BAE204,
       AB14.BAE214 AB14_BAE214,
       AB14.BAE203 AB14_BAE203,
       AB13.BAZ010 AB14_AAZ010,
       AB13.BAE265 AB14_BAE165,
       AB14.BAE006 AB14_BAE006,
       (CASE
         WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
          '31'
         ELSE
          AAE140
       END) AB14_AAE140,
       SUM(NVL(AB14.BAE101, 0)) AB14_BAE101,
       MAX(TRUNC(AB14.AAB191, 'DD')) AB14_AAB191,
       MAX(AB14.AAE011) AB14_AAE011
  FROM AB14, AB13
WHERE AB14.BAE204 = AB13.BAE204
   AND NVL(BAE171, '0') = '1'
   AND NVL(BAE205, 0) = 0
   AND AB13.BAE265 = '1'
   AND AB14.AAE140 = '11'
   AND AB13.AAE036 >= TO_DATE('2010-06-01', 'YYYY-MM-DD')
   AND AB13.AAE036 < TO_DATE('2010-06-24', 'YYYY-MM-DD') + 1
GROUP BY AB14.BAE204,
          AB14.BAE214,
          AB14.BAE203,
          AB13.BAZ010,
          AB13.BAE265,
          AB14.BAE006,
          (CASE
            WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
             '31'
            ELSE
             AAE140
          END)
;
查看执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 2048427295

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |     1 |    99 |     8  (13)| 00:00:01 |
|   1 |  HASH GROUP BY                |                 |     1 |    99 |     8  (13)| 00:00:01 |
|   2 |   NESTED LOOPS                |                 |     1 |    99 |     7   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| AB13            |     1 |    22 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_AB13_AAE036 |     1 |       |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AB14            |     1 |    77 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IND_AB14        |     2 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("AB13"."BAE265"='1')
   4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "AB13"."AAE036"   6 - access(NVL("BAE205",0)=0 AND NVL("BAE171",'0')='1' AND "AB14"."AAE140"='11' AND
              "AB14"."BAE204"="AB13"."BAE204")


从执行计划来看，这个SQL不应该出现性能问题，莫非是CBO评估出现了问题。给SQL增加HINT/*+ gather_plan_statistics */，再次执行。执行了18: 42.75，将近19分钟。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
Plan hash value: 2048427295

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                |                 |      1 |      1 |    730 |00:18:14.73 |      16M|      3 |
|   2 |   NESTED LOOPS                |                 |      1 |      1 |   2974 |00:19:03.76 |      16M|      3 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| AB13            |      1 |      1 |   9790 |00:00:00.24 |   13301 |      3 |
|*  4 |     INDEX RANGE SCAN          | IDX_AB13_AAE036 |      1 |      1 |  21607 |00:00:00.06 |     125 |      0 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AB14            |   9790 |      1 |   2974 |00:18:02.19 |      16M|      0 |
|*  6 |     INDEX RANGE SCAN          | IND_AB14        |   9790 |      2 |    582M|00:09:43.72 |    2398K|      0 |
--------------------------------------------------------------------------------------------------------------------

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

   3 - filter("AB13"."BAE265"='1')
   4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "AB13"."AAE036"   5 - filter("AB14"."BAE204"="AB13"."BAE204")
   6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11')

匪夷所思的事情发生了：
1）看ID=6的A-Rows列，实际通过索引IND_AB14返回的行数高达582M行。而评估出来的只有两条。查看统计信息都不缺失。
2）IND_AB14是一个函数索引。创建语句如下：create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140)，但是看执行计划的谓词部分（id为6的行），"AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1'从何而来啊，索引里是没有这两个列的信息的。貌似SYS_NC00031$对于的是NVL("BAE205",0)，SYS_NC00032$对于的是NVL("BAE171",'0')。
3）我用语句查询了一下，根据索引应该返回的行数。
SQL> SELECT COUNT(*) FROM ab14 WHERE  NVL(BAE171, '0') = '1' AND NVL(BAE205, 0) = 0 AND aae140='11';

  COUNT(*)
----------
     59623
只有不到六万的数据量，怎么实际返回了582M的数据。


不过从执行计划ID=5行可以看出根据"AB14"."BAE204"="AB13"."BAE204"可以过滤掉大量数据，于是修改了之前的函数索引增加了BAE204这个字段。
create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140,bae204)

修改索引后，重新执行，只花了2.3秒。
Plan hash value: 1600515523

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                |                 |      1 |      1 |    743 |00:00:00.37 |   33700 |     55 |
|   2 |   NESTED LOOPS                |                 |      1 |      1 |   3073 |00:00:00.31 |   33700 |     55 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| AB13            |      1 |      1 |   9803 |00:00:00.06 |   13282 |      0 |
|*  4 |     INDEX RANGE SCAN          | IDX_AB13_AAE036 |      1 |      1 |  21624 |00:00:00.02 |     108 |      0 |
|   5 |    TABLE ACCESS BY INDEX ROWID| AB14            |   9803 |      1 |   3073 |00:00:00.27 |   20418 |     55 |
|*  6 |     INDEX RANGE SCAN          | IND_AB14_BAE204 |   9803 |      1 |   3073 |00:00:00.25 |   19619 |     55 |
--------------------------------------------------------------------------------------------------------------------

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

   3 - filter("AB13"."BAE265"='1')
   4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "AB13"."AAE036"   6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11' AND
              "AB14"."BAE204"="AB13"."BAE204"


CBO评估E-Rows依然非常不准确，重新分析表两个表(仅提供一个AB14的,AB13的略)：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => 'NCSI',
                                TABNAME          => 'AB14',
                                ESTIMATE_PERCENT => 100,
                                METHOD_OPT       => 'FOR ALL COLUMNS  SIZE SKEWONLY',
                                CASCADE          => TRUE,
                                DEGREE           => 10);
END;

重新执行查看执行计划：
Plan hash value: 1600515523

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                |                 |      1 |      1 |    743 |00:00:00.23 |   33709 |      2 |
|   2 |   NESTED LOOPS                |                 |      1 |      1 |   3073 |00:00:00.20 |   33709 |      2 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| AB13            |      1 |      1 |   9806 |00:00:00.06 |   13285 |      1 |
|*  4 |     INDEX RANGE SCAN          | IDX_AB13_AAE036 |      1 |      1 |  21628 |00:00:00.02 |     108 |      0 |
|   5 |    TABLE ACCESS BY INDEX ROWID| AB14            |   9806 |      1 |   3073 |00:00:00.11 |   20424 |      1 |
|*  6 |     INDEX RANGE SCAN          | IND_AB14_BAE204 |   9806 |      1 |   3073 |00:00:00.09 |   19625 |      0 |
--------------------------------------------------------------------------------------------------------------------

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

   3 - filter("AB13"."BAE265"='1')
   4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "AB13"."AAE036"   6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11' AND
              "AB14"."BAE204"="AB13"."BAE204")

几乎没任何改善。评估出来的依然很差。

[ 本帖最后由 wei-xh 于 2010-6-24 17:12 编辑 ]

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

转载于:http://blog.itpub.net/22034023/viewspace-666125/