使用并行hint,将一个sql分部到多个cpu上执行
SQL> select /*+ full(a) parallel(a 1) */count(*) from t_policy a where rownum <=10000;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
Plan hash value: 4050205001
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35115 (9)| 00:01:03 |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS FULL| T_POLICY | 5025K| 35115 (9)| 00:01:03 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=10000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
784 consistent gets
0 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
SQL>
SQL> select /*+ full(a) parallel(a 2) */count(*) from t_policy a where rownum <=10000;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
Plan hash value: 2686624518
--------------------------------------------------------------------------------
-------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-------------------------
| 0 | SELECT STATEMENT | | 1 | 18207 (3)| 00:00:33 |
| | |
| 1 | SORT AGGREGATE | | 1 | | |
| | |
|* 2 | COUNT STOPKEY | | | | |
| | |
| 3 | PX COORDINATOR | | | | |
| | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 5025K| 18207 (3)| 00:00:33 | Q1
,00 | P->S | QC (RAND) |
|* 5 | COUNT STOPKEY | | | | | Q1
,00 | PCWC | |
| 6 | PX BLOCK ITERATOR | | 5025K| 18207 (3)| 00:00:33 | Q1
,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T_POLICY | 5025K| 18207 (3)| 00:00:33 | Q1
,00 | PCWP | |
--------------------------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=10000)
5 - filter(ROWNUM<=10000)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
1557 consistent gets
1596 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
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select /*+ full(a) parallel(a 3) */count(*) from t_policy a where rownum <=10000;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
Plan hash value: 2686624518
--------------------------------------------------------------------------------
-------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-------------------------
| 0 | SELECT STATEMENT | | 1 | 12138 (3)| 00:00:22 |
| | |
| 1 | SORT AGGREGATE | | 1 | | |
| | |
|* 2 | COUNT STOPKEY | | | | |
| | |
| 3 | PX COORDINATOR | | | | |
| | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 5025K| 12138 (3)| 00:00:22 | Q1
,00 | P->S | QC (RAND) |
|* 5 | COUNT STOPKEY | | | | | Q1
,00 | PCWC | |
| 6 | PX BLOCK ITERATOR | | 5025K| 12138 (3)| 00:00:22 | Q1
,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T_POLICY | 5025K| 12138 (3)| 00:00:22 | Q1
,00 | PCWP | |
--------------------------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=10000)
5 - filter(ROWNUM<=10000)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
1521 consistent gets
1591 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
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> show parameter cpu_count;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 2
SQL>
修改table的并行度,则table上的full scan的执行计划默认为并行度为2的parallel:
SQL> alter table scott.emp parallel 2;
Table altered.
SQL> set autot trace exp
SQL> select * from scott.emp a ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
SQL>
使用noparallel的hint使得查询不使用并行:
SQL> select /*+ noparallel(a) */* from scott.emp a;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>
同样的,parallel_index和noparallel_index的用法类似
Pq_distribute
使用这个hint来优化parallel join操作,设置连接的表的行应该如何在生产者和消费者查询服务之间来分发。如果所有的table都是serial的,没有parallel的,那么优化器将忽略转发的hint。上面的例子中已经将scott的emp表的并行度设置为了2,看看一些查询的例子,具体参数解释在9i或者10g的performance tunning guide的hint章节。
SQL> select /*+ PQ_DISTRIBUTE(a,hash,hash)*/* from scott.emp a ,scott.dept b
2 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3268189581
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 754 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 13 | 754 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 13 | 754 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 5 | PX RECEIVE | | 4 | 80 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 4 | 80 | 2 (0)| 00:00:01 | | S->P | HASH |
| 7 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 8 | PX RECEIVE | | 13 | 494 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 13 | 494 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 13 | 494 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS FULL| EMP | 13 | 494 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DEPTNO"="B"."DEPTNO")
11 - filter("A"."DEPTNO" IS NOT NULL)
SQL>
SQL> select /*+ PQ_DISTRIBUTE(a,none,none)*/* from scott.emp a ,scott.dept b
2 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 1393584480
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 754 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 754 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 13 | 754 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 4 | 80 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 4 | 80 | 2 (0)| 00:00:01 | | S->P | BROADCAST |
| 7 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 8 | PX BLOCK ITERATOR | | 13 | 494 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS FULL | EMP | 13 | 494 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DEPTNO"="B"."DEPTNO")
9 - filter("A"."DEPTNO" IS NOT NULL)
SQL>
SQL> select /*+ PQ_DISTRIBUTE(a,none,none) use_hash(a) ordered */* from scott.emp a ,scott.dept b
2 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 1394072867
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 754 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 754 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 13 | 754 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 13 | 494 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 494 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 4 | 80 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 4 | 80 | 2 (0)| 00:00:01 | | S->P | BROADCAST |
| 9 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DEPTNO"="B"."DEPTNO")
5 - filter("A"."DEPTNO" IS NOT NULL)
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-674831/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-674831/