HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

对Group By 语句的一次优化过程

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2008-05-07 14:50:13

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

生产环境中发现一条语句很慢,拿回来一看,其实是一个简单的Group By语句:

CCMMT的数据量比较大,5M多条记录。

 

1

SQL> select CDE, CID

  2  from CCMMT

  3  GROUP BY CDE, CID

  4  having max(ADT) < sysdate - 180;

 

707924 rows selected.

 

Elapsed: 00:06:17.49

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=238583 Bytes=4771660)

   1    0   FILTER

   2    1     SORT (GROUP BY NOSORT) (Cost=414 Card=238583 Bytes=4771660)

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CCMMT' (Cost=414 Card=57969096 Bytes=1159381920)

   4    3         INDEX (FULL SCAN) OF 'CCMMT_TEMP_IDX' (NON-UNIQUE) (Cost=26 Card=57969096)

 

Statistics

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

          0  recursive calls

          0  db block gets

    2769177  consistent gets

    1089991  physical reads

          0  redo size

   23926954  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     707924  rows processed

 

6min多返回。尝试调整语句写法,用minus代替Group By:

 

2

SQL> select DISTINCT CDE, CID

  2  from CCMMT

  3  where ADT < sysdate - 180

  4  minus

  5  select DISTINCT CDE, CID

  6  from CCMMT

  7  where ADT >= sysdate - 180;

 

707924 rows selected.

 

Elapsed: 00:00:21.53

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=190624 Card=2794940

          Bytes=111797600)

   1    0   MINUS

   2    1     SORT* (UNIQUE) (Cost=95312 Card=2794940 Bytes=55898800)                                               :Q13049001

   3    2       INDEX* (FAST FULL SCAN) OF 'CCMMT_UQ1' (UNIQUE) (Cost=77305 Card=2898455 Bytes=57969100)    :Q13049000

   4    1     SORT* (UNIQUE) (Cost=95312 Card=2794940 Bytes=55898800)                                               :Q13050001

   5    4       INDEX* (FAST FULL SCAN) OF 'CCMMT_UQ1' (UNIQUE) (Cost=77305 Card=2898455 Bytes=57969100)    :Q13050000

 

   2 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1 FROM :Q13049000 ORDER BY C0,C1

   3 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1 "CCMMT_UQ1")*/ A1."CDE" C0,A1."CA

   4 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1 FROM :Q13050000 ORDER BY C0,C1

   5 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1 "CCMMT_UQ1")*/ A1."CDE" C0,A1."CA

 

Statistics

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

          0  recursive calls

         33  db block gets

     126566  consistent gets

     129243  physical reads

          0  redo size

   18461368  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          4  sorts (memory)

          2  sorts (disk)

     707924  rows processed

 

效果不错,Consistent gets Physical Reads都下降了,同时只需要21s就返回了。但从查询计划看,用到了并行查询,因此会消耗更多的CPU

(ADT, CDE, CID )上创建索引,再次执行:

 

3

SQL> select DISTINCT CDE, CID

  2  from CCMMT

  3  where ADT < sysdate - 180

  4  minus

  5  select DISTINCT CDE, CID

  6  from CCMMT

  7  where ADT >= sysdate - 180;

 

707924 rows selected.

 

Elapsed: 00:00:26.94

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36018 Card=2794940 Bytes=111797600)

   1    0   MINUS

   2    1     SORT (UNIQUE) (Cost=18009 Card=2794940 Bytes=55898800)

   3    2       INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

   4    1     SORT (UNIQUE) (Cost=18009 Card=2794940 Bytes=55898800)

   5    4       INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

 

Statistics

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

          0  recursive calls

        118  db block gets

      22565  consistent gets

      31604  physical reads

          0  redo size

   18461368  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          1  sorts (memory)

          1  sorts (disk)

     707924  rows processed

 

效果也比较理想,consistent getsphysical reads再次大大下降,返回时间和上面差不多,在一个数量级上,但是不再使用并行查询了。

NOT Exists代替minus:

 

4

SQL> select DISTINCT CDE, CID

  2  from CCMMT a

  3  where ADT < sysdate - 180

  4  AND NOT EXISTS

  5  (SELECT CDE, CID FROM

  6  (select DISTINCT CDE, CID

  7  from CCMMT

  8  where ADT >= sysdate - 180) b

  9  WHERE a.CDE = b.CDE

 10  AND a.CID = b.CID);

 

707924 rows selected.

 

Elapsed: 00:10:35.70

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=600 Card=144923 Bytes=2898460)

   1    0   SORT (UNIQUE) (Cost=600 Card=144923 Bytes=2898460)

   2    1     INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE)(Cost=2 Card=144923 Bytes=2898460)

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CCMMT' (Cost=2 Card=1 Bytes=20)

   4    3         INDEX (RANGE SCAN) OF 'CCMMT_TEMP_IDX' (NON-UNIQUE) (Cost=1 Card=9)

 

Statistics

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

          5  recursive calls

        118  db block gets

   40535587  consistent gets

    3157604  physical reads

          0  redo size

   18461368  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          2  sorts (memory)

          1  sorts (disk)

     707924  rows processed

 

FT! consistent getsphysical reads爆涨,10min才返回结果!

Not In换掉Not Exists:

 

5

SQL> select DISTINCT CDE, CID

  2  from CCMMT a

  3  where ADT < sysdate - 180

  4  AND (CDE, CID) NOT IN

  5  (select DISTINCT CDE, CID

  6  from CCMMT

  7  where ADT >= sysdate - 180);

 

707924 rows selected.

 

Elapsed: 00:01:00.70

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36425 Card=1 Bytes=40)

   1    0   SORT (UNIQUE NOSORT) (Cost=36425 Card=1 Bytes=40)

   2    1     MERGE JOIN (ANTI) (Cost=36423 Card=1 Bytes=40)

   3    2       SORT (JOIN) (Cost=18212 Card=2898455 Bytes=57969100)

   4    3         INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

   5    2       SORT (UNIQUE) (Cost=18212 Card=2898455 Bytes=57969100)

   6    5         INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

 

Statistics

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

          0  recursive calls

        419  db block gets

      22565  consistent gets

      98692  physical reads

          0  redo size

   18461368  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          1  sorts (memory)

          1  sorts (disk)

     707924  rows processed

 

恩,consistent gets和建了索引时的minus方式一样,但是physical reads太大,返回时间太长---1min。同时用到了刚才建的索引。(呵呵,所以说,NOT EXISTS并不是什么情况下都比NOT IN更优啊)

在尝试用left join + is null代替not in:

 

6

SQL> SELECT a.CDE, a.CID

  2  FROM

  3  (select DISTINCT CDE, CID

  4  from CCMMT

  5  where ADT < sysdate - 180) a,

  6  (select DISTINCT CDE, CID

  7  from CCMMT

  8  where ADT >= sysdate - 180) b

  9  WHERE a.CDE = b.CDE(+)

 10  AND a.CID = b.CID(+)

 11  AND b.CDE IS NULL;

 

707924 rows selected.

 

Elapsed: 00:00:25.46

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=54675 Card=2794940 Bytes=117387480)

   1    0   FILTER

   2    1     MERGE JOIN (OUTER)

   3    2       VIEW (Cost=18009 Card=2794940 Bytes=58693740)

   4    3         SORT (UNIQUE) (Cost=18009 Card=2794940 Bytes=55898800)

   5    4           INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

   6    2       SORT (JOIN) (Cost=36667 Card=2794940 Bytes=58693740)

   7    6         VIEW (Cost=18009 Card=2794940 Bytes=58693740)

   8    7           SORT (UNIQUE) (Cost=18009 Card=2794940 Bytes=55898800)

   9    8             INDEX (RANGE SCAN) OF 'CCMMT_IDX3' (NON-UNIQUE) (Cost=2 Card=2898455 Bytes=57969100)

 

Statistics

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

         10  recursive calls

        118  db block gets

      22569  consistent gets

      31300  physical reads

          0  redo size

   18461368  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          6  sorts (memory)

          1  sorts (disk)

     707924  rows processed

 

效果不错,和有索引时使用minus在同一数量级上。

 

总结,以上几种方式中,效果最好的应该是第3种和第6种,buffer gets、磁盘IOCPU消耗都比较少,返回时间大大减少,但是需要新建一个索引,消耗更多磁盘空间,并存在影响其它语句的正常查询计划的风险。而第2种方式应该是次好的。在返回时间上,和上面两种差不多,不需要新的索引,但是会消耗更多的内存、磁盘和CPU资源。

出于综合考虑,采用了第2种方式对生产库进行了优化。

(以上例子中的对象名进行了替换,其他都是原版)

 

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat