oracle11g 自動並行
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [RAC] Oracle11g RAC禁止DB自動啟動Oracle
- Oracle 11gr2中的自動並行度Oracle並行
- windows自動登入linux 並執行指令碼WindowsLinux指令碼
- ORACLE11G自動維護任務簡析Oracle
- win 開機自動輸入密碼 並設定 cmder 開機自啟執行密碼
- 我的測試之旅：（4）並行——自動化迴歸測試並行
- Centos7靜默安裝Oracle11g並設定開機自啟CentOSOracle
- oracle11g控制檔案自動備份延遲特性Oracle
- 禁用Windows自動更新並允許手動更新Windows
- Oracle11g 統計資訊-----統計資訊自動收集任務Oracle
- Oracle11g 統計資訊——統計資訊自動收集任務Oracle
- ultraedit 自動換行
- 執行 adb 命令的時候自動生成並啟動 ._cache_adb.exe 程式
- 【最佳化】SPM(上）自動捕獲sql執行計劃並演進SQL
- 自動部署tomcat，並以普通使用者身份執行 for centos6TomcatCentOS
- Oracle11g 自動化建庫及調整相關引數Oracle
- oracle11g 新特性 - rman自動備份控制檔案延遲Oracle
- 安裝Oracle11g叢集軟體不能自動識別磁碟Oracle
- Excel匯出 並完成後自動開啟Excel
- 使用迭代器接收資料並自動停止
- python自動製作gif並新增文字Python
- 自動建立pid檔案，並加鎖
- iOS 自動打包匯出並安裝ipaiOS
- 將RichTextBox設定為自動換行或非自動換行 (轉)
- 來自czmmiao深入理解Oracle的並行操作Oracle並行
- 【自動化】淺度分析自動化行業，深度好文！行業
- 為Ami.BlazorOne新增Docker支援並自動部署BlazorDocker
- css自動換行與不換行CSS
- android使用jenkins進行自動化打包並且上傳到fir或者蒲公英AndroidJenkins
- CentOS 7下掛載NTFS檔案系統並實行開機自動掛載CentOS
- Oracle11g RAC在例項關閉後自動在啟動例項上歸檔Oracle
- 隨身碟插入自動讀寫/隨身碟插入自動複製檢測隨身碟的插入，以及進行自動複製檔案並寫入檔案
- 如何執行自動 Mac 清理Mac
- RMAN自動執行計劃
- 自動執行任務crontab
- canvas 如何自動去換行Canvas
- web自動化測試框架-01 搭建基礎架構並執行一個樣例Web框架架構
- 在oracle10g執行alter system switch logfile並未開啟自動歸檔Oracle