I have two questions both referring to the documentation in Oracle 19c SQL Language Reference PARALLEL hint "For a statement-level PARALLEL hint" section.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-D25225CE-2DCE-4D9F-8E82-401839690A6E 1) PARALLEL and PARALLEL (AUTO) have the exact same definition, but the two examples that immediately follow in the documentation describe different behavior. Namely, PARALLEL will always run in parallel while the preceding definition says a statement with PARALLLEL hint may run serially. Please clarify this seeming contradiction.
2) PARALLEL and PARALLEL (AUTO) state "...the computed degree of parallelism,...", but do not specify the computation. I was suspecting it is DOP = PARALLEL_THREADS_PER_CPU * CPU_COUNT (for single instance), but the definition of PARALLEL (DEFAULT) provides that calculation. It would be misleading for all three PARALLEL, PARALLEL (AUTO), and PARALLEL (DEFAULT) to use the same calculation but only to define the calculation for one. Hence, I now suspect the calculation for PARALLEL and PARALLEL (AUTO) is something else. What is the calculation(s) used for DOP for PARALLEL and PARALLEL (AUTO) hint?
Thank you in advance.
PARALLEL will pretty much guarantee a parallel operation as long as its possible, eg, some things cannot be done in parallel. But no matter how "silly" using parallel is, it will be still be used if possible, eg
SQL> create table t as select * from scott.emp; -- tiny table
Table created.
SQL> select /*+ parallel */ * from t;
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
We still made a decision to use a *small* amount of parallelism because the table was so small. <<<=== see later on DEFAULT
In contrast, PARALLEL(AUTO) is more about using parallel when it makes sense to do so, which basically means, the query is expected to be a heavy duty one
SQL> select /*+ parallel(auto) */ * from t;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
In this case, we (correctly) decided that no parallel should be done.
For parallel(default) there is not a computation that consider the *object*, just a computation based on the various parallel parameters. For example, on my system, when I ran
select /*+ parallel(default) */ * from t;
I ended up with degree of 12