oracle hint
parallel
1,并行度如合理,启用
2,并行度如不合同不启用
3,default,启用默认参数的配置值
SQL> create table t_parallel(a int) parallel 2;
1,并行度如合理,启用
2,并行度如不合同不启用
3,default,启用默认参数的配置值
SQL> create table t_parallel(a int) parallel 2;
表已创建。
SQL> explain plan for select a from t_parallel;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 2003410281
--------------------------------------------------------------------------------
--------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| TQ |IN-OUT| PQ Distrib |
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
--------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:0
1 | | | |
| 1 | PX COORDINATOR | | | | |
| | | |
| | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 13 | 2 (0)| 00:00:0
1 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 13 | 2 (0)| 00:00:0
1 | Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL| T_PARALLEL | 1 | 13 | 2 (0)| 00:00:0
1 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
--------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择15行。
SQL> explain plan for select /*+ parallel(t_parallel,default) */ a from t_paral
lel;
lel;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 2003410281
--------------------------------------------------------------------------------
--------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| TQ |IN-OUT| PQ Distrib |
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
--------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:0
1 | | | |
| 1 | PX COORDINATOR | | | | |
| | | |
| | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 13 | 2 (0)| 00:00:0
1 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 13 | 2 (0)| 00:00:0
1 | Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL| T_PARALLEL | 1 | 13 | 2 (0)| 00:00:0
1 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
--------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择15行。
SQL>
hint
parallel_index(table,index,parallel)
1,启用分区索引的并行模式
SQL> create table t_parallel(a int) parallel 3;
表已创建。
SQL> create index idx_t_parallel on t_parallel(a) parallel 2;
索引已创建。
SQL> insert into t_parallel values(1);
已创建 1 行。
SQL> insert into t_parallel values(12);
已创建 1 行。
SQL> insert into t_parallel values(123);
已创建 1 行。
SQL> commit;
提交完成。
SQL> explain plan for select a from t_parallel where a=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 2359513259
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
|
--------------------------------------------------------------------------------
---
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:0
1 |
|* 1 | INDEX RANGE SCAN| IDX_T_PARALLEL | 1 | 13 | 1 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------
1 - access("A"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择17行。
--说明即使对于启用了并行模式的普通索引也不能启用其索引的并行模式
SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,2) */
a from t_parallel where a=1;
SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,2) */
a from t_parallel where a=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 2359513259
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
|
--------------------------------------------------------------------------------
---
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:0
1 |
|* 1 | INDEX RANGE SCAN| IDX_T_PARALLEL | 1 | 13 | 1 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------
1 - access("A"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择17行。
SQL>
小结:看文档一定要仔细,往往一个小问题会浪费诸多时间
请看下述的测试
--下述的唯一扫描不会启用分区索引的并行功能
SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,3) */
partition_id from t_parallel where partition_id=1;
请看下述的测试
--下述的唯一扫描不会启用分区索引的并行功能
SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,3) */
partition_id from t_parallel where partition_id=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 3969493144
--------------------------------------------------------------------------------
---------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
| Pstart| Pstop |
--------------------------------------------------------------------------------
---------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00
:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 13 | 3 (0)| 00:00
:01 | 1 | 1 |
|* 2 | TABLE ACCESS FULL | T_PARALLEL | 1 | 13 | 3 (0)| 00:00
:01 | 1 | 1 |
--------------------------------------------------------------------------------
---------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - filter("PARTITION_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择18行。
---如果是范围查找,启用分区索引的并行功能
SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,3) */
partition_id from t_parallel where partition_id between 1 and 2000;
---如果是范围查找,启用分区索引的并行功能
SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,3) */
partition_id from t_parallel where partition_id between 1 and 2000;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 1860071282
--------------------------------------------------------------------------------
--------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
--------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 26000 | 4 (0)|
00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | |
| | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 2000 | 26000 | 4 (0)|
00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX PARTITION RANGE ALL| | 2000 | 26000 | 4 (0)|
00:00:01 | 1 | 3 | Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
|* 4 | INDEX RANGE SCAN | IDX_T_PARALLEL | 2000 | 26000 | 4 (0)|
00:00:01 | 1 | 3 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
4 - access("PARTITION_ID">=1 AND "PARTITION_ID"<=2000)
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择20行。
--不加hint oracle cbo也会启用分区索引的并行功能
SQL> explain plan for select partition_id from t_parallel where partition_id be
tween 1 and 2000;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 1860071282
--------------------------------------------------------------------------------
--------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
--------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 26000 | 4 (0)|
00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | |
| | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 2000 | 26000 | 4 (0)|
00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX PARTITION RANGE ALL| | 2000 | 26000 | 4 (0)|
00:00:01 | 1 | 3 | Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
|* 4 | INDEX RANGE SCAN | IDX_T_PARALLEL | 2000 | 26000 | 4 (0)|
00:00:01 | 1 | 3 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
4 - access("PARTITION_ID">=1 AND "PARTITION_ID"<=2000)
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择20行。
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-751532/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-751532/