Wednesday, December 22, 2021

What is the difference between "force parallel" and "enable parallel" used in the "alter session" statement in Oracle?

What is the difference between these two statements?
ALTER SESSION ENABLE PARALLEL DML | DDL | QUERY;
and
ALTER SESSION FORCE PARALLEL DDL | DML | QUERY;
Answer:

The difference here lays in the details: the ENABLE statement merely enables parallelization using a concrete parallel directive or parallel hint. If this is not specified, Oracle will execute the statements sequenctually. The FORCE statement will parallelize everything it can with the default DOP (degree of parallelism), without you having to state anyting about this in your DML | DDL or query statements.

If the default DOP isn't good enough for you (for example during an index rebuild), you can force your session to use a DOP higher than the default, like this:
ALTER SESSION FORCE PARALLEL DDL | DML | QUERY PARALLEL 32;
This will override any other DOP in the same session and use 32 parallel workers.

Alter session in 19c is documentet here
The concept of forcing/enabling parallelization is explained here

No comments:

Post a Comment