提高count查詢速度

LDean發表於2011-12-30
顯然昨天做的分割槽並未提升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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章