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

WHERE ol.ol_o_id = ord.o_id
AND ol.ol_w_id = ord.o_w_id
AND ol.ol_d_id = ord.o_d_id
GROUP BY ol_number,
ol_w_id,
ol_d_id;

There are various kinds of hints that could help the parallel execution behavior such as the following:

•

PARALLEL

•

NOPARALLEL

•

PQ_DISTRIBUTE

PARALLEL
The PARALLEL hint specifies the desired number of concurrent servers that can be used for parallel operation. The
hint applies to the SELECT , INSERT , UPDATE , and DELETE portions of a statement, as well as to the table scan portion.
When using this hint, the number of servers that can be used is twice the value in the PARALLEL hint clause, if sorting
or grouping operations also take place.
Hints are also useful to control the DOP by specifically requesting the DOP in the hint. For example, the following
query, if executed with default DOP, would execute 96 threads. However, if this needs to be controlled to say only 8
threads, the query could be hinted with DOP value of 8:

SELECT /*+ PARALLEL, 8 */ ol_w_id,
ol_d_id,
ol_number,
SUM(ol_amount),
SUM(ol_quantity)
FROM order_line ol,
orders ord
WHERE ol.ol_o_id = ord.o_id
AND ol.ol_w_id = ord.o_w_id
AND ol.ol_d_id = ord.o_d_id
GROUP BY ol_number,
ol_w_id,
ol_d_id

The following query 1 helps verify the requested DOP and what was actually allocated by the optimizer. As noticed
from the output following, the DOP requested is 8 and the actual DOP allocated is also 8. There could be situations
where the default DOP is used, but the actual DOP received is much lower. This could be because the optimizer was
not able to find a sufficient amount of resources to allocate the requested DOP:

select
px.inst_id INT,
decode(px.qcinst_id,NULL,username,' - '||lower(substr(s.program,length(s.program)-4,4) ) )
"Username",

1 Modified version of the SQL script found on Metalink Note #202219.1.