Database Reference
In-Depth Information
To check whether parallel DDL statements are enabled or disabled at the session level, you can execute a query
like the following (the
pddl_status
column is set to either
ENABLED
,
DISABLED
, or
FORCED
):
SELECT pddl_status
FROM v$session
WHERE sid = sys_context('userenv','sid')
The following sections show, for the three main types of DDL statements that can be executed in parallel, several
examples based on the
px_ddl.sql
script.
CTAS Statements
A CTAS statement is composed of two operations that process data: the query used to retrieve data from the source
tables and the insert into the target table. Each part can be executed either serially or in parallel independently of the
other. However, if parallel processing is used, it's common to parallelize both operations. The following execution
plans illustrate this:
•
Parallelization of insert
: Only operations 2 through 4 are executed in parallel. The query
coordinator scans table
t1
and distributes its content to the slave processes using the round-
robin method. Since the query coordinator communicates with several slave processes,
the relationship between the operations is serial to parallel (
S->P
). A set of slave processes
receives the data and performs the insert (operation
LOAD AS SELECT
) in parallel:
CREATE TABLE t2 PARALLEL 2 AS SELECT /*+ no_parallel(t1) */ * FROM t1
-------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T2 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND ROUND-ROBIN| :TQ10000 | | S->P | RND-ROBIN |
| 6 | TABLE ACCESS FULL | T1 | | | |
-------------------------------------------------------------------------
•
Parallelization of query
: Only operations 3 through 5 are executed in parallel. The slave
processes scan table
t1
in parallel based on block range granules and send its content to the
query coordinator, which is the reason for the parallel to serial (
P->S
) relationship. The query
coordinator executes the insert (operation
LOAD AS SELECT
):
CREATE TABLE t2 NOPARALLEL AS SELECT /*+ parallel(t1 2) */ * FROM t1
------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | LOAD AS SELECT | T2 | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
