ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Tips--Why did my query go parallel(Metalink)

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

原创 Linux操作系统 作者:vongates 时间:2019-04-11 09:15:05 0 删除 编辑
This article outlines things that will force the use of parallel query

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

BookmarkGo 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:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-11

  • 博文量
    201
  • 访问量
    143338