Orcle 12c 新特性---增強并行語句隊列

1 說明

官方文檔： degree of parallelism (DOP)

​​​http://docs.oracle.com/database/121/VLDBG/GUID-4A311C0C-8ABC-4E5A-A854-19F04DACBC36.htm#VLDBG1396​​

Enhancing parallel statement queuing provides more flexibility to address business requirements for mission-critical environments.

增強并行語句隊列提供了更多的靈活性，以滿足任務關鍵環境的業務需求。

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database queues SQL statements that require parallel execution if the necessary number of parallel execution server processes are not available. After the necessary resources become available, the SQL statement is dequeued and allowed to execute. The default dequeue order is a simple first in, first out queue based on the time a statement was issued.

下圖是SQL并行執行的流程圖：

• 1.解析SQL并自動決定DOP(degree of parallelism)
• 2.檢查是否有足夠的并行資源(PARALLEL_SERVERS_TARGET目前環境預設值16)–不能超過PARALLEL_MAX_SERVERS大小

• a.如果有(可用并行資源大于目前SQL所需的)，并且前面沒有正在執行的SQL，那麼就會執行該SQL
• b.如果沒有(可用并行資源小于目前SQL所需的)，則會排隊等待有足夠的資源來執行該SQL。

如果發生語句排隊等待現象，那麼會以等待事件：resmgr:pq queued表現出來。

2 使用hint管理Parallel Statement Queuing

在PARALLEL_DEGREE_POLICY 為AUTO情況下，使用NO_STATEMENT_QUEUING 和STATEMENT_QUEUING來影響語句是否使用Parallel Statement Queuing隊列。

2.1 NO_STATEMENT_QUEUING

使用NO_STATEMENT_QUEUING會使語句繞過parallel statement queue。但是這可能會超過PARALLEL_SERVERS_TARGET指定的最大并行執行數。

如：

SELECT /*+ NO_STATEMENT_QUEUING */ last_name, department_name
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;      

2.2 STATEMENT_QUEUING

STATEMENT_QUEUING HINT讓SQL語句使用parallel statement queue，但是隻有在可以在并行程序足夠的時才運作。在啟用隊列之前，可用的并行執行伺服器的數量等于在使用的并行執行伺服器數量和系統中允許的最大數量之間的差，是有PARALLEL_SERVERS_TARGET參數定義的。

如：

SELECT /*+ STATEMENT_QUEUING */ last_name, department_name
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;      

3 PARALLEL_SERVERS_TARGET設定

PARALLEL_SERVERS_TARGET參數預設值不是固定不變的，而是有公式計算而來的。

公式：PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2

如：目前我的環境

PARALLEL_THREADS_PER_CPU = 2

SQL> show parameter PARALLEL_THREADS_PER_CPU
NAME       TYPE VALUE
------------------------------------ ----------------------
parallel_threads_per_cpu       integer  2


CPU_COUNT = 1

SQL> show parameter CPU_COUNT
NAME       TYPE VALUE
------------------------------------ ----------------------
cpu_count      integer  1      

concurrent_parallel_users參數值分為三種情況：

• 1.如果設定了MEMORY_TARGET或SGA_TARGET，那麼concurrent_parallel_users=4
• 2.如果沒有設定MEMORY_TARGET或SGA_TARGET，并且也設定了PGA_AGGREGATE_TARGE的大小，那麼concurrent_parallel_users=2
• 3.如果沒有設定PGA_AGGREGATE_TARGE的大小，那麼concurrent_parallel_users=1

目前環境：屬于第一種情況，設定了MEMORY_TARGET，因為沒有設定PGA_AGGREGATE_TARGET。是以不滿足第二章情況

SQL> show parameter MEMORY_TARGET
NAME       TYPE     VALUE
------------------------------------ ---------------------- ------------------------------
memory_target      big integer      1G

SQL> show parameter SGA_TARGET
NAME       TYPE     VALUE
------------------------------------ ---------------------- ------------------------------
sga_target       big integer      0

SQL> show parameter PGA_AGGREGATE_TARGET
NAME       TYPE     VALUE
------------------------------------ ---------------------- ------------------------------
pga_aggregate_target       big integer      0

SQL> show parameter PARALLEL_SERVERS_TARGET
NAME       TYPE     VALUE
------------------------------------ ---------------------- ------------------------------
parallel_servers_target      integer      16      

是以，PARALLEL_SERVERS_TARGET= 2 * 1 *4 * 2 = 16，和查詢出來的結果一緻。

3.1 修改參數值

SQL> alter system set parallel_servers_target = 17 scope = spfile;
System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size      8801008 bytes
Variable Size  1023411472 bytes
Database Buffers     37748736 bytes
Redo Buffers      3780608 bytes
Database mounted.
Database opened.

SQL> show parameter PARALLEL_SERVERS_TARGET
NAME       TYPE     VALUE
------------------------------------ ---------------------- ------------------------------
parallel_servers_target      integer      17