Note that this parameter has been deprecated. In 11g release 2 and
beyond, see the parallel_degree_policy
parameter.
Oracle parallel query (OPQ) is a great way to speed full
full-scan operations, but Oracle parallelism can be problematic if it is not
implemented properly.
Oracle provides a general method for implementing parallelism with the
parallel_automatic_tuning=true parameter setting, but there are cases when
using PAT can actually degrade your Oracle performance. The
Oracle docs note:
"With parallel automatic tuning enabled, Oracle determines parameter
settings for each environment based on the number of CPUs on your system and
the value set for PARALLEL_THREADS_PER_CPU.
The default values Oracle sets for parallel execution processing when
PARALLEL_AUTOMATIC_TUNING is TRUE are usually optimal for most environments.
In most cases, Oracle's automatically derived settings are at least as
effective as manually derived settings."
I disagree.
The parallel_automatic_tuning parameter feature was created for DSS
and Data Warehouse environments using the all_rows optimizer mode on servers
with many CPU processors to ensure that the common full table scans are
parallelized to an optimal DEGREE.
However, parallel_automatic_tuning is not always appropriate for OLTP
and online systems, where setting parallel_automatic_tuning may change
the SQL optimizers perception of the costs of full scan operations, causing
indexes not to be used.
For non-warehouse environments, I do not recommend setting
parallel_automatic_tuning, or turning on parallel query at the system or
table level. I get the best performance my manually adding the "parallel"
hint to query where I want the full can to be done in parallel.
and include the URL for the page.
