PURPOSE
-------
To explain how to disable Parallel Execution on Session/System level
SCOPE & APPLICATION
-------------------
DBA's and Support Analysts
Disable Parallel Execution on Session Level
--------------------------------------------
To disable PX on session level you can use the
following three commands together.
1.) ALTER SESSION DISABLE PARALLEL DML;
2.) ALTER SESSION DISABLE PARALLEL DDL;
3.) ALTER SESSION DISABLE PARALLEL QUERY;
A SQL statement with a parallel hint would run in parallel if you use
"ALTER SESSION DISABLE PARALLEL DML" that is expected.
Hints overrides alter session settings.
Disable Parallel Execution on System Level
------------------------------------------
To disable PX on the database level you have two possibilities.
1.) Set PARALLEL_MIN_SERVERS = 0
PARALLEL_MAX_SERVERS = 0
PARALLEL_AUTOMATIC_TUNING = FALSE;
and restart the database.
In 10g and higher versions you can change this parameter with a ALTER SYSTEM command and
you do not have to restart the database.
PARALLEL_AUTOMATIC_TUNING is deprecated in 10.1 and the default is FALSE.
2.) Without restarting the database.
Set the degree of all tables and indices to 1.
You can use the command
ALTER TABLE <TABLE_NAME> PARALLEL 1;
ALTER INDEX <INDEX_NAME> PARALLEL 1,
In the second case you can not be 100% sure that a statement runs in parallel.
All statement which have parallel hints are still run in parallel, because
the PARALLEL hint overrides the degree of the objects.