ORACLE 11G: AUTOMATIC DOP – PARALLEL THRESHOLD

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:
Returning The Actual DOP When Using Oracle’s Auto DOP
-——————————————————————————————-
Related links:
 


Your comments and suggestions are welcome!