Skip to Main Content
  • Questions
  • Degree of Parallelism PARALLEL vs. PARALLEL (AUTO) Definition and Calculation 19c EE

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: May 12, 2022 - 11:57 pm UTC

Last updated: June 13, 2022 - 12:35 pm UTC

Version: 19c EE

Viewed 1000+ times

You Asked

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.


and Connor said...

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

Rating

  (3 ratings)

Comments

spacecordteather123, May 30, 2022 - 4:18 pm UTC

Thank you for taking the time to answer this question.

On my relatively new journey of trying to understand Oracle you've been my guide. On this metaphorical journey, the Oracle documentation has been like a two-dimensional map - it's absolutely essential, but there's often more to the story. Your forum responses, YouTube channel, and podcast provide an equally essential perspective that helps shape my three-dimensional understanding of the Oracle database (or perhaps even four-dimensional when you discuss the historical context of past challenges and the resulting new features). Big thank you to you and the Oracle organization for providing this invaluable content.

===
Back to the question.

"PARALLEL: ... When parallelism is not feasible, the statement runs serially." I was incorrectly reading that as "When parallelism is not optimal, ...", so with my poor reading of the docs it seemed contradictory to me. Thank you for helping correct and clarify my understanding.
Connor McDonald
May 31, 2022 - 1:56 am UTC

glad we could help

Questions about Parallel(default)

Rajeshwaran Jeyabal, June 02, 2022 - 5:33 am UTC

select /*+ parallel(default) */ * from t;
I ended up with degree of 12



Team,

Parallel and Parallel (auto) seems to be good enough from "notes" section in the plan, however parallel(default) is still unclear.

How did you conclude that it ended up with degree of 12 from your case having small data set on table "T" ?

PS: If incase for large data set on "T" while the query is progressing, we can easily have it queried using V$PX_SESSION for a given QCSID and get the DOP.

demo@PDB1> create table t as select * from all_users;

Table created.

demo@PDB1> set autotrace traceonly exp
demo@PDB1> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |    38 |  1938 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS STORAGE FULL| T    |    38 |  1938 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

demo@PDB1> select /*+ parallel */ * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    38 |  1938 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR              |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000 |    38 |  1938 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR         |          |    38 |  1938 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS STORAGE FULL| T        |    38 |  1938 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   0 -  STATEMENT
         U -  parallel

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

demo@PDB1> select /*+ parallel(auto) */ * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |    38 |  1938 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS STORAGE FULL| T    |    38 |  1938 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

demo@PDB1> select /*+ parallel(default) */ * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    38 |  1938 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR              |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000 |    38 |  1938 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR         |          |    38 |  1938 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS STORAGE FULL| T        |    38 |  1938 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------

demo@PDB1> set autotrace off
demo@PDB1>
demo@PDB1> select banner_full from v$version;

BANNER_FULL
-------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

Connor McDonald
June 07, 2022 - 3:09 am UTC

You can check PX stats to which numbers incremented

Questions about Parallel(default)

Rajeshwaran Jeyabal, June 08, 2022 - 1:33 pm UTC

Thanks, but which PX Stats value should i need to check here?
demo@XEPDB1> select name from v$statname where name like 'PX%' order by 1 ;

NAME
------------------------------
PX local messages recv'd
PX local messages sent
PX remote messages recv'd
PX remote messages sent

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.