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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21754115/viewspace-1366391/，如需转载，请注明出处，否则将追究法律责任。