Statement-level PARALLEL Hint

Chris Antognini's picture

From version 11.2 onward, the PARALLEL hint supports two syntaxes: object-level and statement-level. The object-level syntax, which is the only one available up to version 11.1, overrides the DOP associated to a tables. The statement-level syntax can not only override the PARALLEL_DEGREE_POLICY initialization parameter at the SQL statement level, but also force the utilization of parallel processing.

The statement-level PARALLEL hint supports the following values:

  • PARALLEL(DEFAULT) forces the default DOP. Note that the default DOP is determined by multiplying CPU_COUNT with PARALLEL_THREADS_PER_CPU and the number of instances participating to the execution.
  • PARALLEL(MANUAL) activates manual DOP. The SQL statement is executed in parallel only if at least one of the accessed objects has the PARALLEL table/index property set to DEFAULT or to a value greater than one. The maximum DOP that the query optimizer can select is limited by the default DOP.
  • PARALLEL(AUTO) activates automatic DOP (but it doesn’t activate parallel statement queuing and in-memory parallel execution). The query optimizer determines the DOP and, therefore, determines whether the SQL statement runs in parallel or not.
  • PARALLEL works almost the same as PARALLEL(AUTO). As far as I know, the only difference between the two is that with PARALLEL the initialization parameter PARALLEL_MIN_TIME_THRESHOLD is not considered by the query optimizer. As a result, PARALLEL forces the utilization of parallel processing with a DOP determined by the query optimizer.
  • PARALLEL(N) forces the DOP to the integer value specified as parameter (n).

Let’s have a look to an example for each case. Note that for every one of them the execution plan as well as the “Automatic degree of parallelism (AUTODOP)” section of a query optimizer trace file generated by 12.1.0.2 is shown.

  • Setup test environment (note that I fake the object statistics just because I want to avoid to create a large object) and enable query optimizer trace:
SQL> ALTER SESSION SET parallel_degree_policy = manual;

SQL> ALTER SESSION SET parallel_degree_limit = 3;

SQL> ALTER SESSION SET parallel_min_time_threshold = 42;

SQL> CREATE TABLE t PARALLEL 3 PCTFREE 0 AS
  2  SELECT rownum AS id, rpad('*',84,'*') AS pad
  3  FROM dual
  4  CONNECT BY level <= 10000;

SQL> execute dbms_stats.gather_table_stats(user, 't')

SQL> DECLARE
  2    l_numrows PLS_INTEGER;
  3    l_numblks PLS_INTEGER;
  4    l_avgrlen PLS_INTEGER;
  5  BEGIN
  6    dbms_stats.get_table_stats(
  7      ownname => user,
  8      tabname => 'T',
  9      numrows => l_numrows,
 10      numblks => l_numblks,
 11      avgrlen => l_avgrlen
 12    );
 13    -- artificially increase the size of the table
 14    dbms_stats.set_table_stats(
 15      ownname => user,
 16      tabname => 'T',
 17      numrows => l_numrows*10000,
 18      numblks => l_numblks*10000,
 19      avgrlen => l_avgrlen
 20    );
 21  END;
 22  /

SQL> ALTER SESSION SET events 'trace[SQL_Optimizer.*]';
  • PARALLEL(DEFAULT): the query runs in parallel with the default DOP (4 on my test database)
SQL> EXPLAIN PLAN FOR SELECT /*+ parallel(default) */ count(*) FROM t;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +note'));

PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 3126468333

-------------------------------------------
| Id  | Operation              | Name     |
-------------------------------------------
|   0 | SELECT STATEMENT       |          |
|   1 |  SORT AGGREGATE        |          |
|   2 |   PX COORDINATOR       |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |
|   4 |     SORT AGGREGATE     |          |
|   5 |      PX BLOCK ITERATOR |          |
|   6 |       TABLE ACCESS FULL| T        |
-------------------------------------------

**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: hinted.
  • PARALLEL(MANUAL): the query runs in parallel with the DOP specified by the table property (3)
SQL> EXPLAIN PLAN FOR SELECT /*+ parallel(manual) */ count(*) FROM t;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +note'));

PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 3126468333

-------------------------------------------
| Id  | Operation              | Name     |
-------------------------------------------
|   0 | SELECT STATEMENT       |          |
|   1 |  SORT AGGREGATE        |          |
|   2 |   PX COORDINATOR       |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |
|   4 |     SORT AGGREGATE     |          |
|   5 |      PX BLOCK ITERATOR |          |
|   6 |       TABLE ACCESS FULL| T        |
-------------------------------------------

Note
-----
   - Degree of Parallelism is 3 because of table property

**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: hinted.
kkopqSetForceParallelProperties: Hint:yes
Query: compute:yes forced:no forceDop:0
kkopqSetDopReason: Reason why we chose this DOP is: table property.
table property forces parallelism

Global Manual DOP: 2 - Rounded?: no
  • PARALLEL(AUTO): the query doesn’t run in parallel because the estimated run time is lower than the threshold set with PARALLEL_MIN_TIME_THRESHOLD
SQL> EXPLAIN PLAN FOR SELECT /*+ parallel(auto) */ count(*) FROM t;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +note'));

PLAN_TABLE_OUTPUT
-----------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is enabled for this statement in hint mode.
kkopqSetForceParallelProperties: Hint:yes
Query: compute:yes forced:no forceDop:0
Calibration statistics is enabled.
Start with a serial pass, cost the DOP to use
  • PARALLEL: the query runs in parallel with the DOP determined by the query optimizer (3)
SQL> EXPLAIN PLAN FOR SELECT /*+ parallel */ count(*) FROM t;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +note'));

PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 3126468333

-------------------------------------------
| Id  | Operation              | Name     |
-------------------------------------------
|   0 | SELECT STATEMENT       |          |
|   1 |  SORT AGGREGATE        |          |
|   2 |   PX COORDINATOR       |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |
|   4 |     SORT AGGREGATE     |          |
|   5 |      PX BLOCK ITERATOR |          |
|   6 |       TABLE ACCESS FULL| T        |
-------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 3 because of degree limit

**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is enabled for this statement in hint mode.
kkopqSetForceParallelProperties: Hint:yes
Query: compute:yes forced:yes forceDop:32767
Calibration statistics is enabled.
Start with a serial pass, cost the DOP to use
  • PARALLEL(N): the query runs in parallel with the DOP specified by the hint (4)
SQL> EXPLAIN PLAN FOR SELECT /*+ parallel(4) */ count(*) FROM t;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +note'));

PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 3126468333

-------------------------------------------
| Id  | Operation              | Name     |
-------------------------------------------
|   0 | SELECT STATEMENT       |          |
|   1 |  SORT AGGREGATE        |          |
|   2 |   PX COORDINATOR       |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |
|   4 |     SORT AGGREGATE     |          |
|   5 |      PX BLOCK ITERATOR |          |
|   6 |       TABLE ACCESS FULL| T        |
-------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of hint

**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: hinted.
kkopqSetForceParallelProperties: Hint:yes
Query: compute:no forced:yes forceDop:4
kkopqSetDopReason: Reason why we chose this DOP is: hint.
hint forces parallelism with dop=4

Global Manual DOP: 3 - Rounded?: no

In summary, the statement-level PARALLEL hint can be used to enable different behaviors. And, of course, it’s up to you to selected the one that fulfills your expectations.

To prevent automated spam submissions leave this field empty.