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

Only when there is a need for multiple slave sets (e.g., where statements have ORDER BY or GROUP BY conditions or
statements that have multi-table joins) does the consumer come into operation.
We expand the previous query with an additional clause of ORDER BY operation:
SELECT *
FROM product
ORDER BY product_name;
If this query were executed in a regular non-parallel mode, it would perform a full table scan against the PRODUCT
table, followed by a sorting of the retrieved rows by PRODUCT_NAME . On the other hand, if the column PRODUCT_NAME
does not have an index associated with it, and if the DOP is set to two, that is, to execute this query in four parallel
operations, then each of the two operations (scan and sort) is performed concurrently using its set of parallel
execution servers.
Figure 8-3 represents a pictorial view of the query executed in parallel. The query is parallelized at two stages, the
scan phase and the sort phase of the operation, with DOP of two. However, if you look at the combined execution, it
is divided into four parallel execution servers. This is because it is an inter-operation parallelism where a parent and
child operator can be performed at the same time. It should be noted from Figure 8-3 that the two parallel execution
tiers are related to each other in the sense that all the parallel execution servers involved in the scan operation
send rows to the servers performing the sort operation. For example, if a row scanned by a parallel execution server
contains a value of the PRODUCT_NAME column between A and G, that row gets sent to the first ORDER BY parallel
execution server. When the scan operation is complete, the sorting processes can return the sorted results to QC,
which then returns the complete query results to the user.
How is the parallel degree limit or DOP determined? The parallel execution coordinator may enlist two or more
of the instance's parallel execution servers to process an SQL statement. The number of parallel execution servers
associated with a single operation is known as the DOP. The default DOP is computed by the optimizer and based on
the system configuration; typically it's the
PARALLEL_THREADS_PER_CPU * CPU_COUNT ( number of CPU cores ) * ACTIVE_INSTANCE_COUNT ( number of active
instances )
Methods of Applying Parallelism
Parallelism can be applied in several kinds of operations: in DDL statements, in DML statements, or in simple direct
SQL query statements. While there are several applications for the use of parallelism, there are also many methods in
which they could be applied.
As an Attribute of a Table or Index
DOP can be defined during a table or index creation or through an ALTER operation when attributes are modified.
For example
ALTER TABLE PRODUCTS PARALLEL;
will set the default DOP for the PRODUCTS table; or the degree could be set to a specific value, such as
ALTER TABLE PRODUCTS DEGREE 12;