Oracle 11.2中控制並行的新引數
在Oracle 11.2中引入了幾個新的並行查詢引數。對於資料倉儲應用來說經常利用並行處理來快速有效地處理資訊，尤其是查詢非常大的表或加入了複雜的算式更應該使用並行查詢。在Oracle之前的版本中，我們不得不或多或秒的來決定自動並行度。決定一個最佳並行度是非常困難的。真實最佳並行度依賴於資料塊在磁碟上的物理位置以及伺服器的CPU數量(cpu_count)，為了解決並行查詢的這些問題
在Oracle11.2中引入了以下新的並行查詢引數
1.parallel_degree_policy
parallel_degree_policy引數可以被設定為manual,auto或limited在Oracle11.1中parallel_degree_policy預設設定為manual(禁用了automatic degree of parallelism,statement queuing與in-memory parallel execution)
SQL> show parameter parallel_degree_policy;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string manual
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
22040576
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84998 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 21M| 84998 (1)| 00:00:06 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
469904 consistent gets
313229 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
但我們可以手動指定並行度
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string manual
SQL> set autotrace on
SQL> select /*+ parallel */ count(*) from t1;
COUNT(*)
----------
22040576
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47183 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=5)
- automatic DOP: Computed Degree of Parallelism is 2
Statistics
----------------------------------------------------------
20 recursive calls
4 db block gets
470138 consistent gets
313225 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
parallel_degree_policy=auto就會啟用以下新功能:
並行度(DOP)將會基於SQL語句中的操作型別和表的大小來自動計算。例如對大表排序的並行度(DOP)可能比對小表操作的並行度高。
如果請求或請求的並行度(DOP)因為並行服務程式正處於繁忙狀態而不能獲得滿足，那麼Oracle直到有足夠的並行子程式可用之前將不會執行語句，而不是降低並行度或序列執行SQL語句。在11gr2之前的版本中，當沒有足夠的並行程式服務程式滿足所請求的並行度(DOP)時，可以會出現以下三種情況中的一種:
SQL語句將會降低並行度(DOP)來以並行方式執行
SQL語句以序列方式來執行
如果parallel_min_percent被設定將收到"ORA-12827:insufficient parallel query slaves available"
Oracle並行子程式可能使用buffered IO而不是直接IO。例如"in-memory parallel execution"
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO
SQL> select degree,instances from user_tables where table_name = 'T1';
DEGREE INSTANCES
-------------------- --------------------
1 1
Elapsed: 00:00:00.00
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
22040576
Elapsed: 00:00:18.50
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47183 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 2
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
469841 consistent gets
313226 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
parallel_degree_policy設定為limited
對某些語句啟用自動並行度，但statement queuing與in-memory parallel execution被禁用。只會對訪問使用parallel子句來設定DEFAULT並行度的表或索引應用自動並行度。
SQL> select degree,instances from user_tables where table_name = 'T1';
DEGREE INSTANCES
-------------------- --------------------
1 1
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string LIMITED
SQL> set autotrace on;
SQL> select count(*) from t1;
COUNT(*)
----------
22040576
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84998 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 21M| 84998 (1)| 00:00:06 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
469898 consistent gets
313399 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
因為表的並行度是1,而不是default,現在使用parallel子句來修改表t1的並行度
SQL> alter table t1 parallel;
Table altered.
SQL> select degree,instances from user_tables where table_name = 'T1';
DEGREE INSTANCES
-------------------- --------------------
DEFAULT DEFAULT
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
22040576
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47183 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=5)
- automatic DOP: Computed Degree of Parallelism is 2
Statistics
----------------------------------------------------------
83 recursive calls
0 db block gets
470167 consistent gets
313413 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
2.parallel_min_time_threshold
parallel_min_time_threshold引數用來指定SQL語句是否並行執行一個閾值，也就是當最佳化器根據統計資訊所估算的執行時間如果大於這個引數值就是使用並行，如果估算的執行時間小於這個引數值就會序列執行。這個引數值預設值是10秒。並且自動並行度只要在parallel_degree_policy引數被設定為auto或limited時才會生效。從下面的資訊可以看到到語句的執行時間小於10秒時，最佳化器以是序列而不是並行方式來執行的
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
2755072
Elapsed: 00:00:02.66
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10627 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 2569K| 10627 (1)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
57150 consistent gets
39162 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
再次增加表t1的資料記錄
SQL> insert into t1 select * from t1;
5510144 rows created.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
11020288
Elapsed: 00:00:09.05
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42507 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 11M| 42507 (1)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
223549 consistent gets
156619 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到執行時間為9.05秒，Oracle使用序列執行，繼續向表t1增加記錄
SQL> insert into t1 select * from t1;
11020288 rows created.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO
SQL> select * from V$IO_CALIBRATION_STATUS;
STATUS CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY 13-APR-16 10.12.58.413 PM
Elapsed: 00:00:00.08
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
22040576
Elapsed: 00:00:18.50
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47183 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 2
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
469841 consistent gets
313226 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
現在可以看到當parallel_degree_policy=auto,parallel_min_time_threshold=auto時，sql執行時間超長10秒時就會使用自動並行。
3.parallel_degree_limit
使用自動並行度時，Oracle會自動決定是否以並行方式來執行SQL語句以及所使用的並行度。最佳化根據語句所請求的資源來決定一個語句的並行度。然而最佳化器所使用的並行度是受限制的以防止並行程式擊垮系統。也就是是系統中所能使用的並行度的上限為parallel_degree_limit引數值。它有三個引數值可以選擇：
CPU
最大並行度由系統中的CPU數量來限制。其計算公式為parallel_degree_limit=parallel_thread_per_cpu*cpu_count
當然，你也可以將parallel_degree_limit的值設定為一個具體的值，以達到明確控制實際並行度的目的。
IO
最佳化器能使用的最大並行度由系統的I/O能力來限制。這個值等於系統總吞吐量除以每個程式的最大I/O頻寬。但在Oracle 11.2中為了將parallel_degree_limit設定為IO必須執行dbms_resource_manager.calibrate_io過程來收集系統的I/O統計資訊。這個過程將會計算系統的總吞吐量與每個程式的最大IO頻寬。
具體數字
當自動並行度被啟用時，指定一個SQL語句所能使用的最大並行度。這個引數只有當parallel_degree_policy設定為auto或limited時才生效。
4.parallel_force_local
parallel_force_local引數控制RAC環境中的並行執行。預設情況下，最佳化器可以從RAC中的任何節點或所有節點中選擇並行執行SQL語句的並行程式。當parallel_force_local設定為true時，那麼並行程式就只能是與查詢協調者(執行sql語句的節點)在同一個RAC節點中，也就是說並行程式是不能跨節點的.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2083087/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- EXPDP/IMPDP 中的並行度PARALLEL引數並行Parallel
- ORACLE並行相關的引數Oracle並行
- Oracle11.2新特性之使用DBMS_PARALLEL_EXECUTE包實現並行OracleParallel並行
- 釋義Oracle 11r2中並行執行相關引數Oracle並行
- oracle parallel並行_引數parameter_parallel_max_serverOracleParallel並行Server
- Oracle中的並行Oracle並行
- ORACLE 11.2 RAC修改資料庫靜態引數Oracle資料庫
- oracle 11.2 版本 asmcmd 針對 spfile 引數檔案增加的命令OracleASM
- oracle 之 控制oracle RAC 進行並行運算Oracle並行
- Oracle 並行相關的初始化引數Oracle並行
- 【Data Pump】Data Pump的並行引數原理並行
- Oracle 中的並行系列（一）Oracle並行
- rac中控制節點間並行並行
- oracle中的processes,session,transaction引數OracleSession
- Oracle11.2表分割槽新特性Oracle
- Oracle11.2新特性之儲存Oracle
- PostgreSQL並行查詢相關配置引數SQL並行
- 查詢oracle中的隱形引數Oracle
- Oracle升級中的引數補充Oracle
- Oracle中INITRANS和MAXTRANS引數Oracle
- (文件 ID 1373242.1) oracle 11.2之後安裝GI 網路卡引數bugOracle
- parallel並行度的相關操作、概念、引數解釋Parallel並行
- Oracle的並行Oracle並行
- 記一道控制並行數的前端面試題並行前端面試題
- Oracle11gRAC跨節點 並行查詢的控制Oracle並行
- Oracle中的並行系列（二）：你設定的並行真的生效了嗎？Oracle並行
- Oracle控制優化器偏好--optimizer_mode引數Oracle優化
- oracle控制檔案及引數檔案問題Oracle
- oracle中的processes,session,transaction引數詳解OracleSession
- Oracle中的sysctl.conf核心引數Oracle
- Oracle資料庫系統中的引數Oracle資料庫
- Oracle資料庫中的系統引數Oracle資料庫
- Oracle 11g RAC跨例項控制並行Oracle並行
- Oracle 10g RAC跨例項控制並行Oracle 10g並行
- 【SQL*Plus】使用Oracle 11gR2的EXITCOMMIT引數控制在SQL*Plus中exit時commit的行為SQLOracleMIT
- 並行相關的幾個引數並行
- 給一個介面傳遞引數，並接收返回的引數
- Oracle引數-隱藏引數Oracle