Automatic parallelism in Oracle 11gR2

Automatic parallelism in Oracle 11gR2

In Oracle 11.2.0.2, automatic parallelism can be used only when I/O statistics are collected. When parallel_degree_policy is set to auto, the Oracle database determines whether to use Parallelism Based on the cost and hardware features of the operation in the execution plan. When parallel or parallel (auto) is used at the statement level) it indicates that automatic parallelism is used regardless of the value set by parallel_degree_policy.

IO Calibration
Hardware features include IO Calibration statistics, so these statistics must be collected; otherwise, Oracle databases will not use the automatic parallelism feature. The following execution plan is generated when I/O Calibration statistics are not collected. The note section of the execution plan shows information such as "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

Bytes --------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes --------------------------------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------------------------------

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

 

Oracle provides the PL/SQL package dbms_resource_manager.calibrate_io to collect statistics of IO Calibration. The duration of collecting IO Calibration statistics is determined by the num_disks variable and the node number in 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;
/

Note that the first two parameters of DBMS_RESOURCE_MANAGER.CALIBRATE_IO are num_disks, max_latency is the input variable, and there are three output variables.

Num_disks: it is best to provide the actual number of physical disks used by the database to obtain the most accurate results. If you use ASM to manage database files, it refers to the disk group that stores data. Only the physical disk in the disk group that stores data serves as the num_disks variable value and does not contain the physical disk in the FRA disk group.

Latency: Maximum latency allowed for database block I/O operations
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 = 1, 390
Latency = 9
Max _ Mbps = 112

PL/SQL procedure successfully completed.

To verify whether the IO Calibration statistics are collected successfully, run dbms_resource_manager.calibrate_io to query v $ io_calibration_status.
SQL> select * from V $ IO_CALIBRATION_STATUS;

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

Execute again to check whether automatic parallelism can be used
SQL> set autotrace traceonly explain
SQL> select/* + parallel */* from emp;

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

Bytes --------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes --------------------------------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------------------------------

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

You can see that the execution plan uses automatic concurrency after collecting IO Calibration statistics.

When automatic parallelism is used, you can adjust some parameters. The parallel_servers_target parameter should always be smaller than the parallel_max_servers parameter value, and the parallel_servers_target parameter is always between 75% and 50% of parallel_max_servers. If we see a large decrease in parallelism, we should use these two parameters to increase the sensitivity gap.