Oracle & Starcraft

oracle提供了parallel一系列参数和parallel hint可以实现并行扫描，我们也可以使用rowid来手动进行并行查询。

比如说有一个语句
select count(*) from test;

我们可以使用一下脚本来编写手动并行扫描的脚本

SQL 10G>select ’select count(*) from test where rowid between chartorowid(”’||dbms_rowid.rowid_create( 1, data_object_id, relative_fno, min_block, 0 ) ||”’)'||’ and chartorowid(”’||dbms_rowid.rowid_create( 1, data_object_id, relative_fno,max_block+blocks-1, 10000 )||”’);’ script
2 from (
3 select relative_fno,
4 block_id,
5 min(block_id) over (partition by grp) min_block,
6 max(block_id) over (partition by grp) max_block,
7 blocks,
8 sum(blocks) over (partition by grp) sum_blocks
9 from (
10 select relative_fno,
11 block_id,
12 blocks,
13 trunc( (sum(blocks) over (order by block_id)-0.01) /
14 (sum(blocks) over ()/4) ) grp
15 from dba_extents
16 where segment_name = ‘TEST’
17 and owner = user order by block_id
18 )
19 ), (select data_object_id
20 from user_objects
21 where object_name = ‘TEST’ )
22 where block_id = max_block
23 /
SCRIPT
——————————————————————————————————————————————————————————————————–
select count(*) from test where rowid between chartorowid(’AAAOYHAAEAAAAcxAAA’) and chartorowid(’AAAOYHAAEAAAA64CcQ’);
select count(*) from test where rowid between chartorowid(’AAAOYHAAEAAAA8JAAA’) and chartorowid(’AAAOYHAAEAAABAICcQ’);
select count(*) from test where rowid between chartorowid(’AAAOYHAAEAAABAJAAA’) and chartorowid(’AAAOYHAAEAAABCICcQ’);
select count(*) from test where rowid between chartorowid(’AAAOYHAAEAAABCJAAA’) and chartorowid(’AAAOYHAAEAAABToCcQ’);

这样就可以使用4个session来做并行查询了，等做完再手工合并结果集

来看一下执行计划

SQL 10G>set autotrace trace;
SQL 10G>select count(*) from test where rowid between chartorowid(’AAAOYHAAEAAAAcxAAA’) and chartorowid(’AAAOYHAAEAAAA64CcQ’);

Execution Plan
———————————————————-
Plan hash value: 1023884461
————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 12 | 159 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS BY ROWID RANGE| TEST | 8035 | 96420 | 159 (2)| 00:00:02 |
————————————————————————————-
这里走了rowid range scan，效率还是很高的

我们来看看使用parallel hint的执行计划
SQL 10G>select/*+ parallel(test 4)*/count(*) from test;

Execution Plan
———————————————————-
Plan hash value: 2661943167
——————————————————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
——————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 44 (0)| 00:00:01 | | | |
| 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 | | 49717 | 44 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TEST | 49717 | 44 (0)| 00:00:01 | Q1,00 | PCWP | |
——————————————————————————————————–

自动并行查询其实也是由coordinator根据rowid切分查询范围然后传递sql语句给px slave是执行，然后执行完slave返回结果集由coordinator合并后传递给client.