Oracle 11g introduced automatic DOP i.e. Degree of Parallelilsm for a statement is automatically computed by the optimizer if the parameter PARALLEL_DEGREE_POLICY = AUTO.
Before deciding onthe DOP, optimizer first checks if the statement needs to be parallelized. For this the parameter parallel_min_time_threshold is used. This parameter can be set to an integer which refers to time in seconds. If the statement can be executed serially within the time specified by this parameter, it will not be parallelized i.e. it will execute serially. But if serial execution takes more than the time specified by this parameter, statement is executed in parallel.
Let’s demonstrate ….
– Set parallel_min_time_threshold = 10 so that a statement will be parallellized if
its serial execution takes more than 10 secs
SYS>alter system set parallel_degree_policy=auto;
alter system set parallel_min_time_threshold = 10;
sho parameter parallel;
NAME TYPE VALUE
———————————— ———– —————
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 20
parallel_min_percent integer 0
NAME TYPE VALUE
———————————— ———– —————
parallel_min_servers integer 0
parallel_min_time_threshold string 10
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 6
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
– Find out the execution plan for the following statement
– Note that execution plan uses parallel servers (automatic DOP: Computed Degree of Parallelism is 2) as sh.sales is a huge table and the query will take more than 10 secs if executed serially
SYS>set line 500
explain plan for
select * from sh.sales
order by 1,2,3,4;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
—————————————————————————————————————————————–
Plan hash value: 2055439529
—————————————————————————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
—————————————————————————————————————————————–
| 0 | SELECT STATEMENT | | 918K| 25M| | 297 (11)| 00:00:04 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 918K| 25M| | 297 (11)| 00:00:04 | | | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 918K| 25M| 42M| 297 (11)| 00:00:04 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 918K| 25M| | 274 (3)| 00:00:04 | | | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 918K| 25M| | 274 (3)| 00:00:04 | | | Q1,00 | P->P | RANGE |
PLAN_TABLE_OUTPUT
—————————————————————————————————————————————–
| 6 | PX BLOCK ITERATOR | | 918K| 25M| | 274 (3)| 00:00:04 | 1 | 28 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| SALES | 918K| 25M| | 274 (3)| 00:00:04 | 1 | 28 | Q1,00 | PCWP | |
—————————————————————————————————————————————–
Note
—–
– automatic DOP: Computed Degree of Parallelism is 2
– Now let us set set parallel_min_time_threshold=3600 so that a query will be parallellized if its serial
execution takes more than 1 hour (3600 secs)
SQL> alter system set parallel_min_time_threshold=3600;
sho parameter parallel
NAME TYPE VALUE
———————————— ———– ——————————
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 20
parallel_min_percent integer 0
NAME TYPE VALUE
———————————— ———– ——————————
parallel_min_servers integer 0
parallel_min_time_threshold string 3600
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 6
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
-- Check that serial execution plan (utomatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold) is generated as serial execution of the statement will take less than 1 hour
SQL> set line 500
explain plan for
select * from sh.sales
order by 1,2,3,4;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
——————————————————————————————————–
Plan hash value: 3803407550
——————————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
——————————————————————————————————
| 0 | SELECT STATEMENT | | 918K| 25M| | 7826 (1)| 00:01:34 | | |
| 1 | SORT ORDER BY | | 918K| 25M| 42M| 7826 (1)| 00:01:34 | | |
| 2 | PARTITION RANGE ALL| | 918K| 25M| | 494 (3)| 00:00:06 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 918K| 25M| | 494 (3)| 00:00:06 | 1 | 28 |
——————————————————————————————————
PLAN_TABLE_OUTPUT
——————————————————————————————————–
Note
—–
– automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
References:
-——————————————————————————————-
Related links: