Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 257

There are two wait events associated with statement queuing during parallel execution. A statement that is
ready to be selected (top of the queue) for execution waits on the event PX QUEUING: statement queue . All other
statements in the queue are waiting on enq: JX - SQL statement queue .

Parallel DDL
All DDL operations, with the exception of tables with object or large objects (LOB) columns (data types), can use the
parallel execution process. Examples of DDL statements are.

CREATE TABLE AS SELECT
CREATE INDEX
ALTER INDEX REBUILD (can be parallelized)
ALTER TABLE MOVE (or SPLIT in the case of partitioned tables)

The PARALLEL option can be set at table or index level by using the CREATE or ALTER operation as shown following:

CREATE TABLE PRODUCT PARALLEL (degree x instances y)

where x is the number of slaves to use per instance, per slave set, and y is the number of instances to be used to
parallelize across. Each slave set gets x slaves per instance. If the IPQ is in use, there will be xy slaves per slave set. If
the query being run is complex enough to require producer and consumer slave sets, the actual requirements are 2x,
or, in the IPQ case, 2xy.

■ Setting the DOp for a table or index will force the use of cost-based optimizer (CBO) even in the complete
absence of statistics.

Note

Parallel DML
Parallel DML (PDML) is most often used to speed up DML operations against large objects (e.g., overnight batch
updates). The extra statement required to enable PDML is documented (in Oracle documentation) as being too much of
an overhead for OLTP transactions. This feature was not intended to be used for small transactions, as the costs involved
in calculating how data is to be partitioned amongst parallel execution slaves could actually decrease performance.
All DML operations ( INSERT , UPDATE , and DELETE ) could use the parallel execution process. PDML uses parallel
execution mechanisms to speed up or scale up large DML operations against large database tables and indexes.
INSERT INTO. . .SELECT FROM statements that operate against multiple tables as part of single DML operation can
also use parallel operations.
Behavior of locks placed on the tables during DML operations is different between serial and PDML operations;
therefore, it is required that PDML be explicitly enabled by issuing the following statement:

ALTER SESSION ENABLE PARALLEL DML;

Once this statement has been issued, any DML activity against a table with the parallel attribute will occur in
parallel if no PDML restrictions are violated.