显然昨天做的分区并未提升count(1)的查询速度,怎样都避免不了全表扫描,后来开始在网上搜索,发现的确有很多方法,所以准备挨个尝试
首先正常查询一次
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的方式,漫长等待后,进行查询
近4分钟,而且cost 29563,怎么更高了呢?继续在这上尝试
2.更改bitmap索引并行
首先正常查询一次
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
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几秒,cost 29563 一点没变;既然并行了索引,那么也并行表吧
10:58:32 SQL> alter table trans_tmp parallel 4;
表已更改。
去掉了查询部分,只看执行计划,这回cost 8212,下来不少
3.禁用索引,并行扫描全表
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索引并行
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
并行扫描全表用时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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25680865/viewspace-714224/