http://metalink.oracle.com/metalink/plsql/showdoc?db=Not&id=196938.1
Bookmark | Go to End |
|
|
This article outlines things that will force the use of parallel query.
SCOPE & APPLICATION
DBA's Support Analysts
WHY DID MY QUERY GO PARALLEL?
The following are features that will make the optimizer consider the use of a parallel plan for a query:
- Parallel hints i.e. /*+ PARALLEL(emp,5) */ ?
- Parallel session setting e.g.:
<Parameter:parallel_automatic_tuning>
<Parameter:parallel_adaptive_multi_user> - Setting a degree of parallel and/or the number of instances on a table or index in a query
Note that the affect of setting a degree of parallelism on an index has changed. Prior to 8i this would have had no affect. Post 8i a parallel plan will be investigated. If degree is set ( to an integer value > 1 or to 'DEFAULT' ) on an object then this will mean a parallel plan is considered. Remember that the optimizer works on a cost basis so just because a parallel plan may be considered, does not mean that it will be chosen. Since 8i we have changed the syntax for the setting of degree of parallelism See Note 260845.1 Old and new Syntax for setting Degree of Parallelism
Examples
The following examples use EMP which has no parallel degree or instances set. Choice of a parallel plan can be seen from the autotrace output (set autot trace explain). The tests were run on 9.0.1 on Solaris
SQL> select degree, instances from user_tables where table_name= 'EMP';
Unhinted Query:
SQL> select * from emp; Parallel Degree hint or setting degree on the table:
SQL> select /*+ parallel(emp,5) */ * from emp;
OR
SQL> alter table emp parallel(degree 5); Parallel Instances hint or instances set in the object:
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=448) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=448)
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=448) 1 0 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=448) :Q492000 1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO" ,A1."ENAME",A1."JOB",A1."MGR",A1."HI
SQL> select /*+ parallel(emp,1,5) */ * from emp;
OR
SQL> alter table emp parallel(degree 1 instances 5);
NOTE that setting degree or instances will override any RULE hints:
SQL> select /*+ rule */ * from emp; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE (Cost=1 Card=14 Bytes=448) 1 0 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=448) :Q500000 1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO" ,A1."ENAME",A1."JOB",A1."MGR",A1."HI
RELATED DOCUMENTS
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=448) 1 0 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=448) :Q494000 1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO" ,A1."ENAME",A1."JOB",A1."MGR",A1."HI
DEGREE INSTANCES ---------- ---------- 1 1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29987/viewspace-51855/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29987/viewspace-51855/