Databases Reference
In-Depth Information
There's more...
In the example there is a huge improvement in performance when using parallel queries on a
machine with more than one CPU/core.
The hint in step 11 query suggests, to the database engine, the use of degree of parallelism
of two. There will be two parallel processing streams, but the number of processes (or
threads) involved will be probably more than two. This is because more steps are involved in
answering the query (fetching data, sorting, returning results to the client).
To answer parallel requests there is a pool of parallel processes, which can range between
PARALLEL_MIN_SERVERS
and
PARALLEL_MAX_SERVERS
parameters.
If a request for a query (with a degree of parallelism that exceeds the number of parallel
processes available in the pool) arrives, the request may be executed with a lower degree of
parallelism—or even serially. From Oracle Database 11
g
R2, the request may be delayed until
a sufficient number of parallel processes will be available.
Starting Oracle Database 11
g
, when setting the parameter
PARALLEL_DEGREE_POLICY
to
AUTO
, the degree of parallelism is automatically determined by the database, depending
on the size of the objects involved in the query and the available resources. Setting this
parameter is recommended only in data warehouse environments.
Parallel query and I/O
We have seen how simple it is to request the parallel execution of a query. However,
the benefits of parallel SQL execution can be observed only when there is sufficient I/O
throughput and a reduced contention on I/O devices. For some systems it also depends on
network bandwidth. If we have a 16 core CPU and only one disk, and we use a degree of
parallelism of 16, then we expect a dip in performance due to contention on disk segments.
In such situations it's important that the accessed segments are spread on multiple disks to
obtain maximum performance from parallel SQL execution.
When to use parallel SQL
Using a parallel SQL isn't always a good idea. In OLTP environments parallel executions cause
too many locks and consume more resources—limiting the scalability of the application.
Moreover, these systems are often very well used because there are many concurrent
sessions working, so the parallelism is in use by multiple concurrent transactions.
As a rule of thumb, it's better to use parallel SQL for long running queries (batch working,
reporting). For short-lived ones, the overhead needed to coordinate the parallel slave
processes frustrates the performance gain obtained from parallel execution.
