快捷搜索:

提高count查询速度

显然昨天做的分区并未提升count(1)的查询速度,怎样都避免不了全表扫描,后来开始在网上搜索,发现的确有很多方法,所以准备挨个尝试
首先正常查询一次
10:19:30 SQL> select count(1) from trans_tmp;
  COUNT(1)----------   8997943
已用时间:  918博天堂网址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 on11: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.3111:41:31 SQL> create bitmap index bitmap_id_idx on trans_tmp(id) online;
11:59:20 SQL> set autotrace trace exp11: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.2311: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 on12: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很低,但是执行时间却很长,看来还是只能并行全表扫描了

本文源自: AG88平台

您可能还会对下面的文章感兴趣: