Oracle 11gR2中的自動並行度

Oracle 11gR2中的自動並行度

在Oracle 11.2.0.2中只有I/O統計資料被收集才能使用自動並行度。當parallel_degree_policy被設定為auto時，Oracle資料庫將會基於執行計畫中操作的成本和硬體特性來判斷是否使用並行，當在語句層級使用parallel或parallel(auto)暗示不管parallel_degree_policy設定為何值都會使用自動並行。

IO Calibration
硬體特性包括IO Calibration統計資料，因此這些統計資料必須被收集否則Oracle資料庫將不會使用自動並行這個功能。下面的執行計畫是在沒有收集IO Calibration統計資料時產生的，在執行計畫的note部分可以看到"skipped because of IO calibrate statistics are missing"這樣的資訊
SQL> set long 900
SQL> set linesize 900
SQL> set autotrace traceonly explain
SQL> select /*+ parallel */ * from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time    |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |          |    14 |  1218 |    2  (0)| 00:00:01 |        |      |            |
|  1 |  PX COORDINATOR      |          |      |      |            |          |        |      |            |
|  2 |  PX SEND QC (RANDOM)| :TQ10000 |    14 |  1218 |    2  (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|  3 |    PX BLOCK ITERATOR |          |    14 |  1218 |    2  (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  4 |    TABLE ACCESS FULL| EMP      |    14 |  1218 |    2  (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
  - dynamic sampling used for this statement (level=2)
  - automatic DOP: skipped because of IO calibrate statistics are missing

 

Oracle提供了PL/SQL包dbms_resource_manager.calibrate_io來收集IO Calibration的統計資料。收集IO Calibration統計資料的期間由num_disks變數與RAC中節點數決定的。
SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS        CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
NOT AVAILABLE

SET SERVEROUTPUT ON
DECLARE
  lat INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
    --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
  dbms_output.put_line('max_mbps = ' || mbps);
END;
/

注意DBMS_RESOURCE_MANAGER.CALIBRATE_IO的前兩個參數分別為num_disks,max_latency是輸入變數，並且有三個輸出變數。

num_disks:為了獲得最精確的結果，最好提供資料庫所使用的真實物理磁碟數。如果是使用ASM來管理資料庫檔案，那麼就是指儲存資料的磁碟組，那麼只有儲存資料的磁碟組中的物理磁碟作為num_disks變數值，不包含FRA磁碟組中的物理磁碟。

latency:對資料庫塊IO操作允許的最大延遲
SQL> set long 900
SQL> set linesize 900
SQL> SET SERVEROUTPUT ON
DECLARE
SQL>  2    lat INTEGER;
  3    iops INTEGER;
  4    mbps INTEGER;
  5  BEGIN
  6      --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
  7      DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
  8    DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  9    DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
 10    dbms_output.put_line('max_mbps = ' || mbps);
 11  END;
 12  /
max_iops = 390
latency = 9
max_mbps = 112

PL/SQL procedure successfully completed.

為了驗證是否IO Calibration統計資訊收整合功，在執行dbms_resource_manager.calibrate_io後查詢v$io_calibration_status
SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS        CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY        13-APR-16 10.12.58.413 PM

再次執行看是否能使用自動並行度
SQL> set autotrace traceonly explain
SQL> select /*+ parallel */ * from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time    |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |          |    14 |  532 |    2  (0)| 00:00:01 |        |      |            |
|  1 |  PX COORDINATOR      |          |      |      |            |          |        |      |            |
|  2 |  PX SEND QC (RANDOM)| :TQ10000 |    14 |  532 |    2  (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|  3 |    PX BLOCK ITERATOR |          |    14 |  532 |    2  (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  4 |    TABLE ACCESS FULL| EMP      |    14 |  532 |    2  (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
  - automatic DOP: Computed Degree of Parallelism is 2

可以看到在收集IO Calibration統計資訊後，執行計畫使用自動並行度。

當使用自動並行度，可以還需要調整一些調整參數。parallel_servers_target參數應該總是比parallel_max_servers參數值小，parallel_servers_target總是處於parallel_max_servers的75%到50%。如果開始看到大量並行度下降，那麼應該使用這兩個參靈敏的差距增大。