oracle11g 自動並行

denglt發表於2013-12-05

Oracle 11g introduced automatic DOP 。
ODM KNOWLEDGE:

When automatic degree of parallelism (Auto DOP) is on the Oracle Optimizer will Automatically decide the DOP for a statement based on the resource requirements of the statement. Any statement that can be parallelized is a candidate for AUTO DOP.
You can see the DOP that the optimizer came up with in the notes section of the explain plan (see below)
There are two init.ora parameters that control auto DOP
PARALLEL_DEGREE_POLICY and PARALLEL_MIN_TIME_THRESHOLD.
PARALLEL_DEGREE_POLICY controls whether or not auto DOP will be used.
By default Auto DOP is switch off for backward compatibility (MANUAL). To Enable Auto DOP for everything set the parameter to AUTO. It is recommended that DW users choose the LIMITED setting. It will apply auto DOP only for statements where at least one table is decorated with PARALLEL clause.
AUTO_DEGREE_POLICY:
o        MANUAL - reverts to Oracle Database 10g behavior (Default).
o        LIMITED - auto DOP applied only to stmts that contain tables or indexes decorated explicitly with the parallel clause with or without explicit DOP
o        AUTO  - automatic PQ for all statements. 

PARALLEL_MIN_TIME_THREADHOLD: the execution time, as estimated by the optimizer, above which a statement is considered for automatic PQ and automatic derivation of DOP.

By default this is set to AUTO which means 30sec.

When a SQL statement is executed it will be hard parsed and a serial plan will be developed. The expected elapse time of that plan will be examined.
If the expected Elapse time is Less than PARALLEL_MIN_TIME_THRESHOLD  then the query will execute serially.
If the expected Elapse time is greater than PARALLEL_MIN_TIME_THRESHOLD  then the plan Will be re-evaluated to run in parallel and the optimizer will determine the ideal DOP.
The Optimizer automatically determines the DOP based on the resource required for all scan operations(full table scan, index fast full scan and so on)

However, the optimizer will cap the actual DOP for a statement with the default DOP (paralllel_threads_per_cpu X CPU_COUNT X INSTANCE_COUNT), to ensure parallel Processes do not flood the system.

Controlling Auto DOP
Controlled by two init.ora parameters:
PARALLEL_DEGREE_POLICY :Controls whether or not auto DOP will be used Default is MANUAL which means no Auto DOP .Set to AUTO to enable auto DOP
PARALLEL_MIN_TIME_THRESHOLD : Controls which statements are candidate for parallelism Default is 30 seconds

How Auto DOP Works

Statement with an elapse time estimate of less than PARALLEL_MIN_TIME_THRESHOLD will run serial .
Statement above threshold are candidate for parallelism
Maximum DOP controlled by PARALLEL_DEGREE_LIMIT
Default value is
PARALLEL_THREADS_PER_CPU X CPU_COUNT
Actual DOP = MIN(PARALLEL_DEGREE_LIMIT, ideal DOP)

驗證試驗:
SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU   -- 限制為cpu的個數
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     240
parallel_min_percent                 integer     0

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     96
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

SQL> explain plan for
  2                  select id, grade, cnt, seq_add_num_seqid.nextval, parentid
  3                    from (select
  4                           a.id, a.parentid, 10 - 1 grade, sum(c.leafcount) cnt
  5                            from addr a, addr_num c
  6                           where a.id = c.parentid
  7                             and a.grade = 10 - 1
  8                             and c.grade = 10
  9                           group by a.id, a.parentid);

Explained.

SQL> @xplan
SQL執行計劃型別[BASIC|TYPICAL|ALL|OUTLINE]
請輸入要檢視的型別(TYPICAL): outline

執行計劃如下:

Plan hash value: 1443883871

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |  2472K|    99M|       |   130K  (1)| 00:26:12 |
|   1 |  SEQUENCE             | SEQ_ADD_NUM_SEQID |       |       |       |            |          |
|   2 |   VIEW                |                   |  2472K|    99M|       |   130K  (1)| 00:26:12 |
|   3 |    HASH GROUP BY      |                   |  2472K|    73M|   104M|   130K  (1)| 00:26:12 |
|*  4 |     HASH JOIN         |                   |  2472K|    73M|    42M|   109K  (1)| 00:21:58 |
|*  5 |      TABLE ACCESS FULL| ADDR              |  1544K|    25M|       | 80171   (1)| 00:16:03 |
|*  6 |      TABLE ACCESS FULL| ADDR_NUM          |  7355K|    98M|       | 18337   (2)| 00:03:41 |
---------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$2")
      USE_HASH(@"SEL$2" "C"@"SEL$2")
      LEADING(@"SEL$2" "A"@"SEL$2" "C"@"SEL$2")
      FULL(@"SEL$2" "C"@"SEL$2")
      FULL(@"SEL$2" "A"@"SEL$2")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."ID"="C"."PARENTID")
   5 - filter("A"."GRADE"=9)
   6 - filter("C"."GRADE"=10)
正在開啟[F:\oradba_output\xplain.txt]檔案

SQL> alter session set PARALLEL_DEGREE_POLICY=auto;

Session altered.

SQL> alter session set PARALLEL_MIN_TIME_THRESHOLD=10;

Session altered.

SQL> explain plan for
  2                  select id, grade, cnt, seq_add_num_seqid.nextval, parentid
  3                    from (select
  4                           a.id, a.parentid, 10 - 1 grade, sum(c.leafcount) cnt
  5                            from addr a, addr_num c
  6                           where a.id = c.parentid
  7                             and a.grade = 10 - 1
  8                             and c.grade = 10
  9                           group by a.id, a.parentid);

Explained.

SQL> @xplan
SQL執行計劃型別[BASIC|TYPICAL|ALL|OUTLINE]
請輸入要檢視的型別(TYPICAL): outline

執行計劃如下:

Plan hash value: 1410895322

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |  2472K|    99M|       |  8161   (1)| 00:01:38 |        |      |
|   1 |  SEQUENCE                   | SEQ_ADD_NUM_SEQID |       |       |       |            |       |   |      |            |
|   2 |   PX COORDINATOR            |                   |       |       |       |            |       |   |      |            |
|   3 |    PX SEND QC (RANDOM)      | :TQ10002          |  2472K|    99M|       |  8161   (1)| 00:01:38 |  Q1,02 | P->S | QC (RAND)
|   4 |     VIEW                    |                   |  2472K|    99M|       |  8161   (1)| 00:01:38 |  Q1,02 | PCWP |
|   5 |      HASH GROUP BY          |                   |  2472K|    73M|   104M|  8161   (1)| 00:01:38 |  Q1,02 | PCWP |
|*  6 |       HASH JOIN             |                   |  2472K|    73M|       |  6833   (1)| 00:01:22 |  Q1,02 | PCWP |
|   7 |        JOIN FILTER CREATE   | :BF0000           |  1544K|    25M|       |  5557   (1)| 00:01:07 |  Q1,02 | PCWP |
|   8 |         PX RECEIVE          |                   |  1544K|    25M|       |  5557   (1)| 00:01:07 |  Q1,02 | PCWP |
|   9 |          PX SEND HASH       | :TQ10000          |  1544K|    25M|       |  5557   (1)| 00:01:07 |  Q1,00 | P->P | HASH
|  10 |           PX BLOCK ITERATOR |                   |  1544K|    25M|       |  5557   (1)| 00:01:07 |  Q1,00 | PCWC |
|* 11 |            TABLE ACCESS FULL| ADDR              |  1544K|    25M|       |  5557   (1)| 00:01:07 |  Q1,00 | PCWP |
|  12 |        PX RECEIVE           |                   |  7355K|    98M|       |  1271   (2)| 00:00:16 |  Q1,02 | PCWP |
|  13 |         PX SEND HASH        | :TQ10001          |  7355K|    98M|       |  1271   (2)| 00:00:16 |  Q1,01 | P->P | HASH
|  14 |          JOIN FILTER USE    | :BF0000           |  7355K|    98M|       |  1271   (2)| 00:00:16 |  Q1,01 | PCWP |
|  15 |           PX BLOCK ITERATOR |                   |  7355K|    98M|       |  1271   (2)| 00:00:16 |  Q1,01 | PCWC |
|* 16 |            TABLE ACCESS FULL| ADDR_NUM          |  7355K|    98M|       |  1271   (2)| 00:00:16 |  Q1,01 | PCWP |
-------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$2")
      PX_JOIN_FILTER(@"SEL$2" "C"@"SEL$2")
      PQ_DISTRIBUTE(@"SEL$2" "C"@"SEL$2" HASH HASH)
      USE_HASH(@"SEL$2" "C"@"SEL$2")
      LEADING(@"SEL$2" "A"@"SEL$2" "C"@"SEL$2")
      FULL(@"SEL$2" "C"@"SEL$2")
      FULL(@"SEL$2" "A"@"SEL$2")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      SHARED(16)
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("A"."ID"="C"."PARENTID")
  11 - filter("A"."GRADE"=9)
  16 - filter("C"."GRADE"=10 AND SYS_OP_BLOOM_FILTER(:BF0000,"C"."PARENTID"))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 16
正在開啟[F:\oradba_output\xplain.txt]檔案

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/195110/viewspace-1062312/,如需轉載,請註明出處,否則將追究法律責任。

相關文章