What is the Difference in using FORCE, ENABLE, DISABLE keyword in PARALLEL QUERY/DML/DDL

Question

What is the difference in following statements

ALTER SESSION FORCE   PARALLEL QUERY;
ALTER SESSION ENABLE  PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL DML;

Which one is more suggested for the optimization point of view. Initially, I was using DISABLE, later on I tested with ENABLE which performed better and now FORCE is doing better. Is there any chance that FORCE will backfire in any case.

Solution

Please refer to http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2013.htm

PARALLEL DML | DDL | QUERY

The PARALLEL parameter determines whether all subsequent DML, DDL, or query statements in the session will be considered for parallel execution. This clause enables you to override the degree of parallelism of tables during the current session without changing the tables themselves. Uncommitted transactions must either be committed or rolled back prior to executing this clause for DML.

ENABLE Clause

Specify ENABLE to execute subsequent statements in the session in parallel. This is the default for DDL and query statements. • DML: DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified.

• DDL: DDL statements are executed in parallel mode if a parallel clause is specified.

• QUERY: Queries are executed in parallel mode if a parallel hint or a parallel clause is specified.

Restriction on the ENABLE clause You cannot specify the optional PARALLEL integer with ENABLE.

DISABLE Clause

Specify DISABLE to execute subsequent statements in the session serially. This is the default for DML statements. • DML: DML statements are executed serially.

• DDL: DDL statements are executed serially.

• QUERY: Queries are executed serially.

Restriction on the DISABLE clause You cannot specify the optional PARALLEL integer with DISABLE.

FORCE Clause

FORCE forces parallel execution of subsequent statements in the session. If no parallel clause or hint is specified, then a default degree of parallelism is used. This clause overrides any parallel_clause specified in subsequent statements in the session but is overridden by a parallel hint. • DML: Provided no parallel DML restrictions are violated, subsequent DML statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause.

• DDL: Subsequent DDL statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause. Resulting database objects will have associated with them the prevailing degree of parallelism.

Specifying FORCE DDL automatically causes all tables created in this session to be created with a default level of parallelism. The effect is the same as if you had specified the parallel_clause (with the default degree) in the CREATE TABLE statement.

• QUERY: Subsequent queries are executed with the default degree of parallelism, unless a degree is specified in this clause.

PARALLEL integer Specify an integer to explicitly specify a degree of parallelism: • For FORCE DDL, the degree overrides any parallel clause in subsequent DDL statements.

• For FORCE DML and QUERY, the degree overrides the degree currently stored for the table in the data dictionary.

• A degree specified in a statement through a hint will override the degree being forced.

The following types of DML operations are not parallelized regardless of this clause: • Operations on cluster tables

• Operations with embedded functions that either write or read database or package states

• Operations on tables with triggers that could fire

• Operations on tables or schema objects containing object types, or LONG or LOB data types