测试表AC43有记录204690413，二亿多条。
并行处理花费时间17.47秒，正常查询花费时间49.21，将近三倍的差距。
SQL> set timing on
SQL> set autotrace on
SQL> SELECT /*+ parallel(ac04 5) */count(DISTINCT aac001) FROM ac04;
COUNT(DISTINCTAAC001)
---------------------
1156171
已用时间: 00: 00: 17.47
执行计划
----------------------------------------------------------
Plan hash value: 3388306996
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 13199 (1)| 00:02:39 | | | |
| 1 | SORT GROUP BY | | 1 | 5 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 5 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT GROUP BY | | 1 | 5 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 5 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 5 | | | Q1,00 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 5 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 26M| 124M| 13199 (1)| 00:02:39 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| AC04 | 26M| 124M| 13199 (1)| 00:02:39 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
87 recursive calls
4 db block gets
277758 consistent gets
277030 physical reads
720 redo size
223 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT count(DISTINCT aac001) FROM ac04;
COUNT(DISTINCTAAC001)
---------------------
1156171
已用时间: 00: 00: 49.21
执行计划
----------------------------------------------------------
Plan hash value: 345483231
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 25391 (2)| 00:05:05 |
| 1 | SORT GROUP BY | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| PK_AC04 | 26M| 124M| 25391 (2)| 00:05:05 |
---------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
119794 consistent gets
2 physical reads
688 redo size
240 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可是我在一张相对较小的表AC01上测试的情况恰恰相反。
并行用了9秒多，正常查询只用了2秒多。
查看执行计划，并行查询用了全表扫描，正常的查询是索引的方式。
看来用并行查询的时候还是需要提前做好测试。
SQL> SELECT /*+ parallel(ac01 5) */count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
879392
已用时间: 00: 00: 09.41
执行计划
----------------------------------------------------------
Plan hash value: 1972307605
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2881 (1)| 00:00:35 | | |
| 1 | SORT GROUP BY | | 1 | 7 | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 7 | | | Q1,01 | P->S | QC (RAND)
| 4 | SORT GROUP BY | | 1 | 7 | | | Q1,01 | PCWP |
| 5 | PX RECEIVE | | 1 | 7 | | | Q1,01 | PCWP |
| 6 | PX SEND HASH | :TQ10000 | 1 | 7 | | | Q1,00 | P->P | HASH
| 7 | SORT GROUP BY | | 1 | 7 | | | Q1,00 | PCWP |
| 8 | PX BLOCK ITERATOR | | 1928K| 12M| 2881 (1)| 00:00:35 | Q1,00 | PCWC |
| 9 | TABLE ACCESS FULL| AC01 | 1928K| 12M| 2881 (1)| 00:00:35 | Q1,00 | PCWP |
------------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
53 recursive calls
3 db block gets
60275 consistent gets
60094 physical reads
728 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
880064
已用时间: 00: 00: 01.76
执行计划
----------------------------------------------------------
Plan hash value: 3761785375
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1064 (3)| 00:00:13 |
| 1 | SORT GROUP BY | | 1 | 7 | | |
| 2 | INDEX FAST FULL SCAN| IDX_AC01_AAC003 | 1928K| 12M| 1064 (3)| 00:00:13 |
-----------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
2 recursive calls
0 db block gets
5428 consistent gets
0 physical reads
0 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
还有怎么才能在查询中使用两个HINT，我试了如下的方式，使用并行查询，且想让查询使用索引IDX_AC01_AAC003，结果用的是另一个索引，并行查询也没起作用。我这种写法对吗？
SQL> SELECT /*+ parallel(ac01 5) index(ac01 IDX_AC01_AAC003) */count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
879393
已用时间: 00: 00: 14.47
执行计划
----------------------------------------------------------
Plan hash value: 994991478
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 144K (1)| 00:28:53 |
| 1 | SORT GROUP BY | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | AC01 | 1928K| 12M| 144K (1)| 00:28:53 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 4 | BITMAP INDEX FULL SCAN | IDX_AC01_BAC136 | | | | |
-------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
3 recursive calls
0 db block gets
210121 consistent gets
53 physical reads
216 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
[ 本帖最后由 wei-xh 于 2010-5-4 09:59 编辑 ]
并行处理花费时间17.47秒，正常查询花费时间49.21，将近三倍的差距。
SQL> set timing on
SQL> set autotrace on
SQL> SELECT /*+ parallel(ac04 5) */count(DISTINCT aac001) FROM ac04;
COUNT(DISTINCTAAC001)
---------------------
1156171
已用时间: 00: 00: 17.47
执行计划
----------------------------------------------------------
Plan hash value: 3388306996
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 13199 (1)| 00:02:39 | | | |
| 1 | SORT GROUP BY | | 1 | 5 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 5 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT GROUP BY | | 1 | 5 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 5 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 5 | | | Q1,00 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 5 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 26M| 124M| 13199 (1)| 00:02:39 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| AC04 | 26M| 124M| 13199 (1)| 00:02:39 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
87 recursive calls
4 db block gets
277758 consistent gets
277030 physical reads
720 redo size
223 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT count(DISTINCT aac001) FROM ac04;
COUNT(DISTINCTAAC001)
---------------------
1156171
已用时间: 00: 00: 49.21
执行计划
----------------------------------------------------------
Plan hash value: 345483231
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 25391 (2)| 00:05:05 |
| 1 | SORT GROUP BY | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| PK_AC04 | 26M| 124M| 25391 (2)| 00:05:05 |
---------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
119794 consistent gets
2 physical reads
688 redo size
240 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可是我在一张相对较小的表AC01上测试的情况恰恰相反。
并行用了9秒多，正常查询只用了2秒多。
查看执行计划，并行查询用了全表扫描，正常的查询是索引的方式。
看来用并行查询的时候还是需要提前做好测试。
SQL> SELECT /*+ parallel(ac01 5) */count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
879392
已用时间: 00: 00: 09.41
执行计划
----------------------------------------------------------
Plan hash value: 1972307605
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2881 (1)| 00:00:35 | | |
| 1 | SORT GROUP BY | | 1 | 7 | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 7 | | | Q1,01 | P->S | QC (RAND)
| 4 | SORT GROUP BY | | 1 | 7 | | | Q1,01 | PCWP |
| 5 | PX RECEIVE | | 1 | 7 | | | Q1,01 | PCWP |
| 6 | PX SEND HASH | :TQ10000 | 1 | 7 | | | Q1,00 | P->P | HASH
| 7 | SORT GROUP BY | | 1 | 7 | | | Q1,00 | PCWP |
| 8 | PX BLOCK ITERATOR | | 1928K| 12M| 2881 (1)| 00:00:35 | Q1,00 | PCWC |
| 9 | TABLE ACCESS FULL| AC01 | 1928K| 12M| 2881 (1)| 00:00:35 | Q1,00 | PCWP |
------------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
53 recursive calls
3 db block gets
60275 consistent gets
60094 physical reads
728 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
880064
已用时间: 00: 00: 01.76
执行计划
----------------------------------------------------------
Plan hash value: 3761785375
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1064 (3)| 00:00:13 |
| 1 | SORT GROUP BY | | 1 | 7 | | |
| 2 | INDEX FAST FULL SCAN| IDX_AC01_AAC003 | 1928K| 12M| 1064 (3)| 00:00:13 |
-----------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
2 recursive calls
0 db block gets
5428 consistent gets
0 physical reads
0 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
还有怎么才能在查询中使用两个HINT，我试了如下的方式，使用并行查询，且想让查询使用索引IDX_AC01_AAC003，结果用的是另一个索引，并行查询也没起作用。我这种写法对吗？
SQL> SELECT /*+ parallel(ac01 5) index(ac01 IDX_AC01_AAC003) */count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
879393
已用时间: 00: 00: 14.47
执行计划
----------------------------------------------------------
Plan hash value: 994991478
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 144K (1)| 00:28:53 |
| 1 | SORT GROUP BY | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | AC01 | 1928K| 12M| 144K (1)| 00:28:53 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 4 | BITMAP INDEX FULL SCAN | IDX_AC01_BAC136 | | | | |
-------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
3 recursive calls
0 db block gets
210121 consistent gets
53 physical reads
216 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
[ 本帖最后由 wei-xh 于 2010-5-4 09:59 编辑 ]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-662230/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
动态监听与静态监听的一些特点。
请登录后发表评论
登录
全部评论
