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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/195110/viewspace-1062312/
扫一扫
163
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
