And delete could use the parallel execution process

Chapter 8 ■ parallel Query tuning

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.

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.

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.

Chapter 8 ■ parallel Query tuning

This statement must be the first statement of a transaction. If any transaction has not been rolled back or committed before enabling PDML, the following error will be raised:

This is the reason that enabling PDML must be the first statement in a transaction. The coordinator processes (created when connecting to the database) will also create a separate transaction. These processes will not be able to see the data changed by the slave processes until it is committed; hence, the ORA-12838 error is raised when trying to view or modify the table.

Parallel Recovery

•� Setting the PARALLEL clause in the RECOVER DATABASE statement supports media recovery. Parallel media recovery is controlled by the RECOVER_PARALLEL command that selects a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter. For example, if parallel recovery is performed with PARALLEL 4 and only one data file needs recovery, then four spawned processes read blocks from the data file to apply.

■Note parallel recovery is discussed in detail in Chapter 10.