Question: What are
the parameter for Oracle parallel query?
Answer: The Oracle parallel query
parameters vary widely by release of Oracle. Here is a
list of the parallel query parameters in Oracle 12c;
parallel_threads_per_cpu x active_instances
parallel_threads_per_cpu x (2 if
pga_aggregate_target > 0; otherwise 1) x 5
4 x cpu_count
x parallel_threads_per_cpu x active_instance
Here are details on the parallel
parameter: (12c and beyond)
The parallel_degree_level is a
percentage, with a default of 100 and setting it to a value
of ten (10%) decreases the calculated value to 10% while
increasing it to 200 (200%) doubles the calculated
degree of parallelism. The auto automatic degree of
parallelism calculations have gotten progressively better
over the last couple of years, but the parallel_degree_level
parameter is a throttle that can be used to pre-compute the
optimal parallel query degree.
This specifies the
minimum number of query server processes for an instance. This is also
the number of query server processes Oracle creates when the instance
Parallel_max_servers specifies the
maximum number of parallel query servers or parallel recovery
processes for an instance. Oracle will increase the number of query
servers as demand requires from the number created at instance startup
up to this value. The same value should be used for all instances in a
parallel server environment.
Proper setting of the PARALLEL_MAX_SERVERS
parameter ensures that the number of query servers in use will not
cause a memory resource shortage during periods of peak database use.
If PARALLEL_MAX_SERVERS is set too low, some
queries may not have a query server available to them during query
Setting PARALLEL_MAX_SERVERS too high leads to
memory resource shortages during peak periods, which can degrade
performance. For each instance to which you do not want to apply the
parallel query option, set this initialization parameter to zero.
If you have reached the limit of
PARALLEL_MAX_SERVERS on an instance and you attempt to query a GV$
view, one additional parallel server process will be spawned for this
purpose. This extra process will serve any subsequent GV$ queries
until expiration of the PARALLEL_SERVER_IDLE_TIME, at which point the
process will terminate. The extra process is not available for any
parallel operation other than GV$ queries.
Note that if PARALLEL_MAX_SERVERS is set to
zero for an instance, then no additional parallel server process will
be allocated to accommodate a GV$ query.
FALSE, defaults to FALSE, when set to TRUE, enables an adaptive
algorithm designed to improve performance in multi-user environments
that use Parallel Query(PQ). It does this by automatically reducing
the requested degree of parallelism based on the current number of
active PQ users on the system. The effective degree of parallelism
will be based on the degree of parallelism from the table or hint
divided by the total number of PQ users. The algorithm assumes that
the degree of parallelism provided has been tuned for optimal
performance in a single user environment.
In Oracle 11g Release 2 and
beyond, the following new parallel query parameters are
Let's take a
close look at these important enhancements to Oracle
parallel query in 11g Release 2.
parallel_degree_policy parameter is related to the
amount of table data residing in the data buffer cache.
Using parallel_degree_policy allows Oracle to bypass
direct path reads when Oracle determines that lots of the
table data blocks already reside in the data buffer cache.
In traditional 32-bit systems (limited
by on ly a few gig of RAM for the SGA), direct path reads
(which bypass the SGA were always faster than reading a
large table through the data buffer.
However, with the advent of 64-bit servers with
dozens of gigabytes for the
large tables are often be fully cached, negating the need to
always perform direct path reads for parallel large-table
Harrison conducted some benchmark tests of
parallel_degree_policy and we see details on how
parallel_degree_policy evaluates the caching of large
If PARALLEL_DEGREE_POLICY is set to
AUTO then Oracle might perform buffered IO instead of direct
path IO. . . The documentation says that the optimizer
decides whether or not to use direct path depending on the
size of the table and the buffer cache and the likelihood
that some data might be in memory.
parameter only allows parallel query to be invoked against
large tables or indexes, those where the num_rows suggests
that it will take more than nn seconds to scan the table.
The default for
parallel_min_time_threshold is 30 (seconds), but you can
now adjust this threshold according to your optimal
definition of what constitutes a â€œlarge tableâ€.
This parameter appears to be related to the
parameter seta a limit on the maximum degree of parallelism.
The default is cpu_count*2.
parameter prohibits "parallel parallelism", a case where
parallel queries on a RAC node are limited only to the local
For earlier releases, here are the parallel query parameters
the amount of parallelism that the optimizer uses in its cost
functions. The default of 0 means that the optimizer chooses the best
serial plan. A value of 100 means that the optimizer uses each
object's degree of parallelism in computing the cost of a full table
scan operation. Low values favor indexes, and high values favor table
Cost-based optimization will always be used
for any query that references an object with a nonzero degree of
parallelism. For such queries a RULE hint or optimizer mode or goal
will be ignored. Use of a FIRST_ROWS hint or optimizer mode will
override a nonzero setting of OPTIMIZER_PERCENT_PARALLEL.
This specifies the
minimum percent of threads required for parallel query. Setting this
parameter ensures that a parallel query will not be executed
sequentially if adequate resources are not available. The default
value of 0 means that this parameter is not used.
If too few query slaves are available, an
error message is displayed and the query is not executed. Consider the
PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
In a system with 20 instances up and running,
the system would have a maximum of 200 query slaves available. If 190
slaves are already in use and a new user wants to run a query with 40
slaves (for example, degree 2 instances 20), an error message would be
returned because 20 instances (that is, 50% of 40) are not available.
the amount of idle time after which Oracle terminates a process for
parallel operations (parallel query, parallel DML, or parallel DDL).
This value is expressed in minutes. The parameter must be set to 1 or
greater for the query processes to terminate. 0 means the processes
are never terminated.
Specifies the size of messages for parallel execution (Parallel Query, PDML, Parallel Recovery, replication). The default value should be
adequate for most applications. Typical values are 2148 or 4096 bytes.
Larger values would require a larger shared pool.
The parallel_min_message_pool parameter defaults to
(cpus*parallel_max_servers*1.5*(OS message buffer size) or cpus*5*1.5*(OS
message size)). Specifies the minimum permanent amount of memory which
will be allocated from the SHARED POOL, to be used for messages in
This memory is allocated at startup time if
PARALLEL_MIN_SERVERS is set to a non-zero value, or when the server is
first allocated. Setting this parameter is most effective when
PARALLEL_MIN_SERVERS is set to a non-zero value, because the memory
will be allocated in a contiguous section.
This parameter should only be set if the
default formula is known to be significantly inaccurate. setting this
parameter too high will lead to a shortage of memory for the shared
pool; setting it too low will lead to costlier memory allocation when
doing parallel execution. This parameter cannot be set to a number
higher than 90% of the shared pool.
to FALSE and allows you to improve performance in certain cases involving
hash and merge joins. When set to TRUE, if you are joining a very
large join result set with a very small result set (size being
measured in bytes, rather than number of rows), the optimizer has the
option of broadcasting the row sources of the small result set, such
that a single table queue will send all of the small set's rows to
each of the parallel servers which are processing the rows of the
larger set. The result is enhanced performance.
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.