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