Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Statement-level PARALLEL Hint

17 September 2016 Leave a Comment Written by Christian Antognini

UPDATE 2017-08-23: because of the confusing meaning of the term “force” (the meaning that most people give to it isn’t the one of the optimizer traces), I edited the text to make it more clear. Given the number of changes I completely removed the old text.

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 the PARALLEL_MIN_TIME_THRESHOLD initialization parameter.

The statement-level PARALLEL hint supports the following values:

  • PARALLEL(DEFAULT) activates manual DOP. The query optimizer uses the default DOP. It determines it by multiplying PARALLEL_THREADS_PER_CPU with the number of CPU available to the database instances participating to the execution.
  • PARALLEL(MANUAL) activates manual DOP. A parallel execution is considered 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.
  • PARALLEL works almost the same as PARALLEL(AUTO). As far as I know, the only difference between the two is that with PARALLEL the query optimizer does not honor the initialization parameter PARALLEL_MIN_TIME_THRESHOLD. The query optimizer determines the DOP.
  • PARALLEL(N) activates manual DOP. The query optimizer uses as DOP the integer value specified as parameter (n).

Important note: none of these values necessarily lead to a parallel execution plan. In fact, the query optimizer can always choose a cheaper serial execution plan.

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.*]';

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.

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

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

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

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

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.

11gR2, 12cR1, Parallel Processing, Query Optimizer
When CURSOR_SHARING=FORCE, Does Literal Replacement Always Take Place?
SQL Trace in Oracle Database Exadata Express Cloud Service

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.