Database Reference
In-Depth Information
The
p000
,
p001
,
p002
, and
p003
processes are known as
parallel execution
servers
, sometimes also referred to
as
parallel query (PQ) slaves
. Each of these parallel execution servers is a separate session connected as if it were a
dedicated server process. Each one is responsible for scanning a nonoverlapping region of
BIG_TABLE
, aggregating
their results subsets, and sending back their output to the coordinating server—the original session's server
process—which will aggregate the subresults into the final answer.
We can see this in an explain plan. Using a
BIG_TABLE
with 10 million rows in it (see the “Setting Up Your
Environment” section at the beginning of the topic for details on creating a
BIG_TABLE
), we'll walk through enabling
a parallel query for that table and discover how we can see parallel query in action. This example was performed on a
four-CPU machine with default values for all parallel parameters on Oracle 12
c
Release 1; that is, this is an out-of-the-box
installation where only necessary parameters were set, including
MEMORY_TARGET
(set to 4GB),
CONTROL_FILES
,
DB_BLOCK_SIZE
(set to 8KB), and
PGA_AGGREGATE_TARGET
(set to 512MB). Initially, we would expect to see the
following plan:
EODA@ORA12CR1> explain plan for select count(status) from big_table;
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display(null, null,
2 'TYPICAL -ROWS -BYTES -COST'));
---------------------------------------------------
| Id | Operation | Name | Time |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 00:00:03 |
| 1 | SORT AGGREGATE | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 00:00:03 |
---------------------------------------------------
■
Different releases of oracle have different default settings for various parallel features—sometimes radically
different settings. Do not be surprised if you test some of these examples on older releases and see different output as a
result of that.
Note
That is a typical
serial
plan. No parallelism is involved because we did not request parallel query to be enabled,
and by default it will not be.
We may enable parallel query in a variety of ways, including use of a hint directly in the query or by altering the
table to enable the consideration of parallel execution paths (which is the option we use here).
We can specifically dictate the degree of parallelism to be considered in execution paths against this table. For
example, we can tell Oracle, “We would like you to use parallel degree 4 when creating execution plans against this
table.” This translates into the following code:
EODA@ORA12CR1> alter table big_table parallel 4;
Table altered.
I prefer to just tell Oracle, “Please consider parallel execution, but you figure out the appropriate degree of
parallelism based on the current system workload and the query itself.” That is, let the degree of parallelism vary
over time as the workload on the system increases and decreases. If we have plenty of free resources, the degree of
parallelism will go up; in times of limited available resources, the degree of parallelism will go down. Rather than
overload the machine with a fixed degree of parallelism, this approach allows Oracle to dynamically increase or
decrease the amount of concurrent resources required by the query.
