並行處理 Parallel Processing

Parallel Processing

當執行一個 sql 語句時 , 預設是用一個 cpu 來連續執行 , 不管有多少個 cpu. 並行執行目的是分發單個 SQL 語句到多個 CPU 核上執行 .

When you submit a SQL statement to a database engine, by default it’s executed serially by a single server process.Therefore, even if the server running the database engine has several CPU cores, your SQL statement runs on a single CPU core. The purpose of parallel processing is to distribute the execution of a single SQL statement over several CPU cores.

  v$sql_monitor檢視會自動監控任何並行執行的語句！

 

和會話相關聯的處理程式行使 coordinator角色.

The coordination of the slave processes is under the control of the server process associated to the session that submits the SQL statement. Because of this role, it’s commonly called the query coordinator . The query coordinator is responsible for acquiring the slave processes, assigning a subtask to each of them, collecting and combining the partial result sets they deliver, and returning the final result set to the client.

Basic Configuration

Slave Processes Pool

最大可用並行處理在處理池裡定義 . Coordinator使用完就放到池裡去.

The maximum number of slave processes per database instance is limited and maintained by a database instance as a pool of slave processes. A query coordinator requests slave processes from the pool, uses them to execute one SQL statement, and finally, when the execution is complete, returns them to the pool. The following initialization parameters are set to configure the pool:

parallel_min_servers

例項啟動時初始化的並行處理個數 .

 

parallel_max_servers

池裡最大可用並行處理器個數 . 10-20倍CPU核個數是通常做法. 下面是一個32 CPU核的並行處理設定.

It’s difficult to give advice on how to set this parameter. Nevertheless, a value of 10–20 times the number of CPU cores is a good starting point.

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cpu_count                            integer     32

SQL> show parameter parallel_min_servers

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

parallel_min_servers                 integer     64

SQL> show parameter parallel_max

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

parallel_max_servers                 integer     128

 

透過下面的查詢檢視池的狀態 :

SQL> SELECT * FROM v$px_process_sysstat WHERE statistic LIKE 'Servers%';

 

STATISTIC                           VALUE     CON_ID

------------------------------ ---------- ----------

Servers In Use                          0          0

Servers Available                       66          0

Servers Started                         149          0

Servers Shutdown                       83          0

Servers Highwater                       69          0

Servers Cleaned Up                      0          0

 

RAC environment

每個例項有各自的並行處理器池 .

1. parallel_instance_group and instance_groups 用來限制並行處理器到指定的例項. instance_groups 從 11g 後開始不用了 . With the parallel_instance_group initialization parameter, you specify which group the slave processes are allocated from. 如orcl1.parallel_instance_group= ’ service_name ’ (指定和orcl1例項相同service的的例項上分配PX處理.

2. 從11.1開始,PX服務程式基於服務(services)是控制PX服務程式位置的首選方法. 例如,假設連線到po服務,且po服務在orcl1和orcl2 例項上都是活躍的狀態 ,那麼PX服務程式就能從orcl1和orcl2例項進行分配.如下例子,如果你連線到finance服務並執行並行操作,那麼PX服務程式只能從ORCL3例項進行分配.

3. parallel_force_local 指定只能在當前例項上分配 PX 程式 .

 

Memory Utilization

…

Degree of Parallelism(DOP)

當並行處理 SQL語句時,資料庫需要給出並行級別(DOP),有兩種並行級別, manual 和 auto.

1.         Manual degree of parallelism : In this mode, you can control the degree of parallelism either at the session, object, or SQL statement level.

2. Automatic degree of parallelism : In this mode, the database engine automatically selects the

optimal degree of parallelism for every SQL statement.

Default Degree of Parallelism

預設的並行級別基本上就是最大的並行級別 ,如下例,兩節點的RAC, DOP就是32 * 1 * 2 = 64

The default degree of parallelism is the maximum degree of parallelism you might want to use for any parallel SQL statement

default_dop = cpu_count × parallel_threads_per_cpu × number_of_instances

 

SQL> show parameter cpu_count

NAME                         TYPE        VALUE

------------------------------------ ----------- ------------------------------

cpu_count                         integer     32

SQL> show parameter parallel_threads_per_cpu

NAME                         TYPE        VALUE

------------------------------------ ----------- ------------------------------

parallel_threads_per_cpu             integer     1

Manual DOP

下面是 manual dop 例子

CREATE TABLE t (id NUMBER, pad VARCHAR2(1000)) PARALLEL 4

ALTER TABLE t PARALLEL 2

CREATE INDEX i ON t (id) PARALLEL 4

ALTER INDEX i PARALLEL 2

可能在用大批次資料裝載時建立了並行的表或索引 , 但以後不需要就用下面的語句取消 .

ALTER TABLE t PARALLEL 1

ALTER INDEX i NOPARALLEL

 

如果用了 hint, 則重置物件上的 parallel 級別 .

SELECT /*+ parallel(t 16) */ * FROM t

SELECT /*+ parallel_index(t i 16) */ * FROM t

With the parallel hint, it ’ s also possible to explicitly call for the default degree of parallelism:

SELECT /*+ parallel(t default) */ * FROM t

 

Auto DOP

Auto DOP（自動並行級別） ， 很難有個引數去判斷什麼級別是合適的，應用自動DOP， oracle去判斷 什麼時候用且級別是多少，基本上任何大於10秒的序列查詢(query serially) 將被考慮做並行查詢.

 

以下圖表是當做表的全表掃描時 , 最佳化器根據表的資料量大小選擇的不同的並行級別 ,

 

兩個重要引數 :

PARALLEL_DEGREE_POLICY:

Auto DOP is enabled by setting the PARALLEL_DEGREE_POLICY parameter to a value of AUTO , LIMITED ,or ADAPTIVE (12c). The default setting for this parameter is MANUAL , which disables all three of the new 11gR2 parallel features (Auto DOP, Parallel Statement Queueing, In-Memory Parallel Execution).

 

PARALLEL_MIN_TIME_THRESHOLD:

如果 PARALLEL_DEGREE_POLICY 值是 AUTO, 任何大於 10 秒的序列查詢 (query serially) 將被考慮做並行查詢.

When Auto DOP is enabled, Oracle evaluates each statement to determine whether it should be run in parallel and, if so, what DOP should be used. Basically, any statement that the optimizer concludes will take longer than 10 seconds to run serially will be a candidate to run in parallel. The 10-second threshold can be controlled by setting the PARALLEL_MIN_TIME_THRESHOLD parameter. This decision is made regardless of whether any of the objects involved in the statement have been decorated with a parallel degree setting or not.

 

Operation and Configuration

When Auto DOP is enabled, Oracle evaluates each statement to determine whether it should be run in parallel and, if so, what DOP should be used. Basically, any statement that the optimizer concludes will take longer than 10 seconds to run serially will be a candidate to run in parallel. The 10-second threshold can be controlled by setting the PARALLEL_MIN_TIME_THRESHOLD parameter. This decision is made regardless of whether any of the objects involved in the statement have been decorated with a parallel degree setting or not.

Auto DOP is enabled by setting the PARALLEL_DEGREE_POLICY parameter to a value of AUTO , LIMITED ,

or ADAPTIVE (12c). The default setting for this parameter is MANUAL , which disables all three of the new 11gR2 parallel features (Auto DOP, Parallel Statement Queueing, In-Memory Parallel Execution). Unfortunately, PARALLEL_DEGREE_POLICY is one of those parameters that control more than one thing. The following list shows the effects of the various settings for this parameter:

MANUAL : If PARALLEL_DEGREE_POLICY is set to MANUAL , none of the new 11gR2

parallel features will be enabled. Parallel processing will work as it did in

previous versions. That is to say, statements will only be parallelized if a hint is

used or an object is decorated with a parallel setting .

LIMITED : If PARALLEL_DEGREE_POLICY is set to LIMITED , only Auto DOP is

enabled while Parallel Statement Queueing and In-Memory Parallel Execution

remain disabled. In addition, only statements accessing objects that have been

decorated with the default parallel setting will be considered for Auto DOP

calculation.

AUTO : If PARALLEL_DEGREE_POLICY is set to AUTO , all three of the new features are

enabled. Statements will be evaluated for parallel execution regardless of any

parallel decoration at the object level.

ADAPTIVE (12c) : This new 12c parameter enables the same functionality as the

previously discussed AUTO value. In addition to these, Oracle may re-evaluate

the statement in order to provide a better degree of parallelism for subsequent

executions based on feedback gathered during statement execution.

Although the only documented way to enable Parallel Statement Queueing and In-Memory Parallel

Execution is via the all-or-nothing setting of AUTO or ADAPTIVE , the developers have thoughtfully provided hidden parameters that provide independent control of these features. Table 6-3 shows the parameters and how the settings of PARALLEL_DEGREE_POLICY alter the hidden parameters.

 

When to Use It

僅僅在兩種情況下考慮用並行

1.   First, you can use it when plenty of free resources (CPU, memory, and disk I/O bandwidth) are available.

2.   only SQL statements that take more than a few minutes, or even longer, are good candidates for being executed in parallel.

Parallel processing should be used only when two conditions are met. First, you can use it when plenty of free resources (CPU, memory, and disk I/O bandwidth) are available. Remember, the aim of parallel processing is to reduce the response time by distributing the work usually done by a single process (and hence a single CPU core) to several processes (and hence several CPU cores). Second, you can use it for SQL statements that take more than a dozen seconds to execute serially; otherwise, the time and resources needed to initialize, coordinate and terminate

the parallel environment (mainly, the slave processes and the table queues) might be higher than the time gained by the parallelization itself. The actual limit depends on the amount of resources that are available. Therefore, in some situations, only SQL statements that take more than a few minutes, or even longer, are good candidates for being executed in parallel. It’s important to stress that if these two conditions aren’t met, performance could decrease instead of increase.

If parallel processing is commonly used for many SQL statements, either automatic degree of parallelism is enabled at the system level or manual degree of parallelism is enabled at the segment levels. Otherwise, if it’s used only for specific batches or reports, it’s usually better to enable it at the session level or through hints.