Search

OakieTags

Who's online

There are currently 0 users and 37 guests online.

Recent comments

Statement-level PARALLEL Hint

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.