Oracle Concepts -
Parallel Query Option (PQO)
Oracle Tips by Burleson Consulting
The Oracle Parallel Query Option
Introduced in later versions of Oracle7 the
parallel query option (PQO) allows multiple processes to
simultaneously fetch records and perform sorting operations. This
parallelization of operations can lead to impressive speed
improvements in a properly set up parallel environment.
The most important item to specify in a
parallel environment is the number of parallel query slaves. Too few
and you donít get the full benefits, too many and they end up
competing with each other for resources. Of course parallel operations
are of little or no benefit if your system doesnít have parallel
processors and your tables arenít spread across multiple disks in a
stripe set or partitioned. Along with the number of parallel query
slaves the degree of parallel (DOP) for the tables and indexes needs
to be set properly.
The maximum number of parallel query slaves
should generally be set to at least twice the number of CPUs or to
twice the number of disks that he object was spread across. The DOP
can be determined by forcing a full table scan for tables (use a where
1=2 in a select count(*) from the table) and then timing the response
for different DOP settings. These numbers are then adjusted up or down
depending on performance.
Parallel query settings
There are several initialization parameters
that pertain to parallel 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.
Openworld 2007, Oracle recommends never to turn-on the
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.
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.
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.
The shared pool
will have to be increased in size to accommodate the parallel query
message areas and IO queues. I suggest that the LARGE POOL be
designated to prevent PQO from causing shared pool problems.
The large pool will
automatically be configured at a minimum size of 600k if certain
initialization parameters are set in Oracle8 (as discussed in previous
lessons). I suggest manually setting the size.
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. Itís
only $19.95 when you buy it directly from the publisher