ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 提高count查询速度

提高count查询速度

原创 Linux操作系统 作者:LDean 时间:2011-12-30 11:10:15 0 删除 编辑
显然昨天做的分区并未提升count(1)的查询速度,怎样都避免不了全表扫描,后来开始在网上搜索,发现的确有很多方法,所以准备挨个尝试
首先正常查询一次
10:19:30 SQL> select count(1) from trans_tmp;

  COUNT(1)
----------
   8997943

已用时间:  00: 03: 23.76

执行计划
----------------------------------------------------------
Plan hash value: 55114451

-------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |     1 |  6382   (5)| 00:01:17 |
|   1 |  SORT AGGREGATE       |                     |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TRANS_TMP_ID |    11M|  6382   (5)| 00:01:17 |
-------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
        231  recursive calls
          0  db block gets
      28019  consistent gets
      22734  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

3分多钟,6382 cost
1.尝试bitmap索引,还是以id列做索引
首先删掉之前的btree索引
drop index trans_tmp_id;
唉,这就执行了好久,然后建立bitmap索引
create bitmap index bitmap_idx_id on trans_tmp(id) online;
因为应用还在跑着,所以采用online的方式,漫长等待后,进行查询

10:43:52 SQL> select count(1) from trans_tmp;

  COUNT(1)
----------
   8997943

已用时间:  00: 03: 57.18

执行计划
----------------------------------------------------------
Plan hash value: 3875526975

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 | 29563   (1)| 00:05:55 |
|   1 |  SORT AGGREGATE               |               |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |               |    11M| 29563   (1)| 00:05:55 |
|   3 |    BITMAP INDEX FAST FULL SCAN| BITMAP_IDX_ID |       |            |          |
---------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      40454  consistent gets
      32655  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

近4分钟,而且cost 29563,怎么更高了呢?继续在这上尝试

2.更改bitmap索引并行
10:52:37 SQL> alter index bitmap_idx_id parallel 4;

索引已更改。

10:54:40 SQL> select count(1) from trans_tmp;

已用时间:  00: 03: 40.23

执行计划
----------------------------------------------------------
Plan hash value: 408296253

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

| Id  | Operation                         | Name          | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT                  |               |     1 | 29563   (1)| 00:05:55 |        |   |               |

|   1 |  SORT AGGREGATE                   |               |     1 |            |          |        |   |               |

|   2 |   PX COORDINATOR                  |               |       |            |          |        |   |               |

|   3 |    PX SEND QC (RANDOM)            | :TQ10000      |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE                |               |     1 |            |          |  Q1,00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR            |               |    11M| 29563   (1)| 00:05:55 |  Q1,00 | PCWC |            |

|   6 |       BITMAP CONVERSION COUNT     |               |    11M| 29563   (1)| 00:05:55 |  Q1,00 | PCWP |            |

|   7 |        BITMAP INDEX FAST FULL SCAN| BITMAP_IDX_ID |       |            |          |  Q1,00 | PCWP |            |

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



统计信息
----------------------------------------------------------
        939  recursive calls
          3  db block gets
      33540  consistent gets
      32656  physical reads
        628  redo size
        411  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         14  sorts (memory)
          0  sorts (disk)
          1  rows processed

时间只减少了10几秒,cost 29563 一点没变;既然并行了索引,那么也并行表吧
10:58:32 SQL> alter table trans_tmp parallel 4;

表已更改。

11:01:08 SQL> select count(1) from trans_tmp;
已用时间:  00: 00: 01.03

执行计划
----------------------------------------------------------
Plan hash value: 408296253

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |     1 |  8212   (1)| 00:01:39 |        |   |               |
|   1 |  SORT AGGREGATE                   |               |     1 |            |          |        |   |               |
|   2 |   PX COORDINATOR                  |               |       |            |          |        |   |               |
|   3 |    PX SEND QC (RANDOM)            | :TQ10000      |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                |               |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR            |               |    11M|  8212   (1)| 00:01:39 |  Q1,00 | PCWC |            |
|   6 |       BITMAP CONVERSION COUNT     |               |    11M|  8212   (1)| 00:01:39 |  Q1,00 | PCWP |            |
|   7 |        BITMAP INDEX FAST FULL SCAN| BITMAP_IDX_ID |       |            |          |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

去掉了查询部分,只看执行计划,这回cost 8212,下来不少
3.禁用索引,并行扫描全表

11:03:15 SQL> select /*+full(trans_tmp)parallel(trans_tmp 2)*/ count(1) from trans_tmp;
已用时间:  00: 00: 00.17

执行计划
----------------------------------------------------------
Plan hash value: 1765477136

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |     1 | 93757   (1)| 00:18:46 |        |      |         |
|   1 |  SORT AGGREGATE        |                  |     1 |            |          |        |      |         |
|   2 |   PX COORDINATOR       |                  |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM) | :TQ10000         |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                  |     1 |            |          |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR |                  |    11M| 93757   (1)| 00:18:46 |  Q1,00 | PCWC |         |
|   6 |       TABLE ACCESS FULL| TRANS_TMP |    11M| 93757   (1)| 00:18:46 |  Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------
cost 93757 看来很高,不过之前已经把并行度改成了4,那么直接查询试试看

11:06:45 SQL> select  /*+full(trans_tmp)*/ count(1) from trans_tmp;
已用时间:  00: 00: 00.96

执行计划
----------------------------------------------------------
Plan hash value: 1765477136

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |     1 | 46879   (1)| 00:09:23 |        |      |         |
|   1 |  SORT AGGREGATE        |                  |     1 |            |          |        |      |         |
|   2 |   PX COORDINATOR       |                  |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM) | :TQ10000         |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                  |     1 |            |          |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR |                  |    11M| 46879   (1)| 00:09:23 |  Q1,00 | PCWC |         |
|   6 |       TABLE ACCESS FULL| TRANS_TMP |    11M| 46879   (1)| 00:09:23 |  Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------
cost 46897 降了一半左右,既然bitmap索引效果不大,删掉,然后重建btree索引继续尝试并行
4.btree索引并行
11:03:34 SQL> drop index bitmap_idx_id;

索引已删除。
11:07:11 SQL> create index trans_tmp_id on trans_tmp(id) online;

索引已创建。

11:13:51 SQL> alter index trans_tmp_id parallel 4;

索引已更改。

已用时间:  00: 00: 00.32

11:27:44 SQL> select count(1) from trans_tmp;
已用时间:  00: 00: 00.42

执行计划
----------------------------------------------------------
Plan hash value: 1765477136

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |     1 | 46879   (1)| 00:09:23 |        |      |         |
|   1 |  SORT AGGREGATE        |                  |     1 |            |          |        |      |         |
|   2 |   PX COORDINATOR       |                  |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM) | :TQ10000         |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                  |     1 |            |          |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR |                  |    11M| 46879   (1)| 00:09:23 |  Q1,00 | PCWC |         |
|   6 |       TABLE ACCESS FULL| TRANS_TMP |    11M| 46879   (1)| 00:09:23 |  Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------
全表扫描了?gather stat了一下还是全表,那么就强制走索引吧

11:32:03 SQL> select /*+rule*/ count(1) from trans_tmp;
已用时间:  00: 00: 00.18

执行计划
----------------------------------------------------------
Plan hash value: 1765477136

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |     1 | 46879   (1)| 00:09:23 |        |      |         |
|   1 |  SORT AGGREGATE        |                  |     1 |            |          |        |      |         |
|   2 |   PX COORDINATOR       |                  |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM) | :TQ10000         |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                  |     1 |            |          |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR |                  |    11M| 46879   (1)| 00:09:23 |  Q1,00 | PCWC |         |
|   6 |       TABLE ACCESS FULL| TRANS_TMP |    11M| 46879   (1)| 00:09:23 |  Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------
呃,竟然一样,好吧,我准备继续折腾
11:35:43 SQL> set autotrace on
11:35:58 SQL> select count(1) from trans_tmp;

  COUNT(1)
----------
   8997943

已用时间:  00: 02: 35.10

执行计划
----------------------------------------------------------
Plan hash value: 1765477136

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |     1 | 46879   (1)| 00:09:23 |        |      |         |
|   1 |  SORT AGGREGATE        |                  |     1 |            |          |        |      |         |
|   2 |   PX COORDINATOR       |                  |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM) | :TQ10000         |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                  |     1 |            |          |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR |                  |    11M| 46879   (1)| 00:09:23 |  Q1,00 | PCWC |         |
|   6 |       TABLE ACCESS FULL| TRANS_TMP |    11M| 46879   (1)| 00:09:23 |  Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
        273  recursive calls
          0  db block gets
     769352  consistent gets
     768853  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed
并行扫描全表用时2分35,至少是比最开始快了一些

最后再次尝试一下bitmap

11:38:44 SQL> drop index trans_id;

索引已删除。

已用时间:  00: 00: 01.31
11:41:31 SQL> create bitmap index bitmap_id_idx on trans_tmp(id) online;
11:59:20 SQL> set autotrace trace exp
11:59:36 SQL> select count(1) from trans_tmp;
已用时间:  00: 00: 00.15

执行计划
----------------------------------------------------------
Plan hash value: 4075861796

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 | 29563   (1)| 00:05:55 |
|   1 |  SORT AGGREGATE               |               |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |               |    11M| 29563   (1)| 00:05:55 |
|   3 |    BITMAP INDEX FAST FULL SCAN| BITMAP_ID_IDX |       |            |          |
---------------------------------------------------------------------------------------

11:59:41 SQL> alter index bitmap_id_idx parallel 4;

索引已更改。

已用时间:  00: 00: 00.23
11:59:55 SQL> select count(1) from trans_tmp;
已用时间:  00: 00: 00.23

执行计划
----------------------------------------------------------
Plan hash value: 3303238536

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |     1 |  8212   (1)| 00:01:39 |        |   |               |
|   1 |  SORT AGGREGATE                   |               |     1 |            |          |        |   |               |
|   2 |   PX COORDINATOR                  |               |       |            |          |        |   |               |
|   3 |    PX SEND QC (RANDOM)            | :TQ10000      |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                |               |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR            |               |    11M|  8212   (1)| 00:01:39 |  Q1,00 | PCWC |            |
|   6 |       BITMAP CONVERSION COUNT     |               |    11M|  8212   (1)| 00:01:39 |  Q1,00 | PCWP |            |
|   7 |        BITMAP INDEX FAST FULL SCAN| BITMAP_ID_IDX |       |            |          |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

11:59:58 SQL> set autot on
12:00:12 SQL> select count(1) from trans_tmp;

  COUNT(1)
----------
   8997943

已用时间:  00: 03: 39.95

执行计划
----------------------------------------------------------
Plan hash value: 3303238536

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |     1 |  8212   (1)| 00:01:39 |        |   |               |
|   1 |  SORT AGGREGATE                   |               |     1 |            |          |        |   |               |
|   2 |   PX COORDINATOR                  |               |       |            |          |        |   |               |
|   3 |    PX SEND QC (RANDOM)            | :TQ10000      |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                |               |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR            |               |    11M|  8212   (1)| 00:01:39 |  Q1,00 | PCWC |            |
|   6 |       BITMAP CONVERSION COUNT     |               |    11M|  8212   (1)| 00:01:39 |  Q1,00 | PCWP |            |
|   7 |        BITMAP INDEX FAST FULL SCAN| BITMAP_ID_IDX |       |            |          |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
        229  recursive calls
          0  db block gets
      34045  consistent gets
      32656  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed
虽然cost很低,但是执行时间却很长,看来还是只能并行全表扫描了

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

下一篇: 守望2012
请登录后发表评论 登录
全部评论

注册时间:2011-06-11

  • 博文量
    17
  • 访问量
    35074