【sql調優之執行計劃】使用hint(五)Hint for parallel
使用並行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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 使用hint改變執行計劃
- 使用hint來調優sql語句SQL
- 使用Oracle Hint提示來更改執行計劃Oracle
- 加hint改變執行計劃訪問順序優化sql優化SQL
- 使用Oracle Hint提示來更改執行計劃 ZTOracle
- 【sql調優之執行計劃】獲取執行計劃SQL
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- oracle hint_no_parallel_no_parallel_indexOracleParallelIndex
- oracle hint_parallel_parallel_indexOracleParallelIndex
- sql調優一例---索引排序hintSQL索引排序
- 使用USE_HASH Hint調優一個SQL語句SQL
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】temp table transformationSQLORM
- 通過內部的hint來控制執行計劃
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（五）Oracle
- 使用no_merge結合其它hint完全控制Oracle執行計劃Oracle
- 【sql調優之執行計劃】in相關的operationSQL
- 【sql調優之執行計劃】merge sort joinSQL
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- oracle hint_noappend_parallelOracleAPPParallel
- mysql調優之——執行計劃explainMySqlAI
- SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大優化執行）SQLServer索引優化
- SQL Server調優系列玩轉篇（如何利用查詢提示（Hint）引導語句執行）SQLServer
- 【sql hint 提示】SQL
- sql hint articleSQL
- Oralce SQL hintSQL
- Oracle調優之看懂Oracle執行計劃Oracle
- oracle筆記整理12——效能調優之hint標籤Oracle筆記