Automatic Degree of Parallelism in 11.2.0.2 (文件 ID 1269321.1)

automatic DOP: skipped because of IO calibrate statistics are missing
這個意味著沒有對資料庫ASM使用的卷盤進行IO校驗。進行IO校驗之後，同樣的SQL不加修改跑起來，自動併發是45個，負載分佈在3個節點上，執行時間是15分鐘左右。
看來，這個IO校驗還是蠻有效果的。

APPLIES TO:

PURPOSE

In 11.2.0.2 Automatic Degree of Parallelism can only be used if  I/O statistics are gathered.

SCOPE

For DBAs 

The AutoDOP is not a feature to use more parallelism. It is a feature that restricts the parallel to maximize throughput,
so it is expected that with AutoDOP not all queries will run in parallel and the ones that do run in parallel may not run with full parallelism, as this is the technical specifications of the feature.

Ask Questions, Get Help, And Share Your Experiences With This Article

DETAILS

When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether a statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics.

AutoDOP is used when PARALLEL or PARALLEL(AUTO) statement level hint is used regardless of the value of the PARALLEL_DEGREE_POLICY (see documentation).

IO Calibration

If I/O calibration has not been run to gather the required statistics, the explain plan includes the following text in its notes section:  ": skipped because of IO calibrate statistics are missing"

explain plan for 
select /*+ parallel */ * from emp;

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 
 ----- 
 - dynamic sampling used for this statement (level=2) 
 - automatic DOP: skipped because of IO calibrate statistics are missing

The Oracle PL/SQL package DBMS_RESOURCE_MANAGER.CALIBRATE_IO is used to execute the calibration. The duration of the calibration is dictated by the NUM_DISKS variable as well as the number of nodes in the RAC cluster.

SET SERVEROUTPUT ON
DECLARE
   lat INTEGER;
   iops INTEGER;
   mbps INTEGER;
BEGIN
    --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (28, 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;
/

NUM_DISKS - To get the most accurate results, its best to provide the actual number of physical disks that are used for this database. The Storage Administrator can provide this value. Keep in mind that when ASM is used to manage the database files, say in the DATA diskgroup, then only physical disks that make up the DATA diskgroup should be used for the NUM_DISKS variable; i.e.; do not include the disks from the FRA diskgroup. In the example above the DATA diskgroup is made up of 28 physicals (presented as 4 LUNs or ASM disks)

LATENCY– Maximum tolerable latency in milliseconds for database-block-sized IO requests.

You find more information about CALIBRATE_IO in Note: 727062.1  Configuring and using Calibrate I/O.

In order to verify whether the calibration run was successful, query V$IO_CALIBRATION_STATUS after you executed DBMS_RESOURCE_MANAGER.CALIBRATE_IO call.

select * from V$IO_CALIBRATION_STATUS;

STATUS   CALIBRATION_TIME
-------- ----------------------------- 
READY    25-NOV-10 08.53.08.536

The execution plan now shows that the feature automatic degree of parallelism can be used:

explain plan for 
select /*+ parallel */ * from emp;

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 
----- 
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 2 

 There is known issue with DBMS_RESOURCE_MANAGER.CALIBRATE_IO.

Note: 10180307.8 DBRM DBMS_RESOUCE_MANAGER.CALIBRATE_IO REPORTS VERY HIGH MAX_PMBPS If CALIBRATE_IO can not be used you can set the relevant value manual:

 You have to restart the database after this change.

Tuning Parameters

When you use AutoDOP, you may want to adjust some tuning parameters.  See Document 1549214.1 Setup, Monitor, And Tune Parallelism In The Database for information about these parameters.   The parallel_servers_target should always be smaller than parallel_max_servers, with parallel_servers_target anywhere from 75% to 50% of parallel_max_servers.  If you start seeing a lot of DOP downgrades, you should make the distance between the values for these two parameters greater.