Tips--Why did my query go parallel(Metalink)

This article outlines things that will force the use of parallel query

http://metalink.oracle.com/metalink/plsql/showdoc?db=Not&id=196938.1

Bookmark

Go to End

Doc ID: Note:196938.1 Subject: Why did my query go parallel? Type: TROUBLESHOOTING Status: PUBLISHED

Content Type: TEXT/X-HTML Creation Date: 28-MAY-2002 Last Revision Date: 29-SEP-2004

PURPOSE

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/