Database Reference
In-Depth Information
This feature is very useful in ad hoc reporting and data warehousing environments, where queries initiated
by the users may not specify selective predicates and parallelism may be the only option to tune those queries.
It may not be feasible to tune those ad hoc queries individually either. In these scenarios, it may be prudent to use the
AutoDOP feature and let the optimizer choose optimal parallelism, but use Database Resource Manager or proper
parallel_max_servers to control query parallelism.
Version 12c introduces a new parameter,
parallel_degree_level
, and this parameter controls the
aggressiveness of default DOP calculations. It also controls the scaling factor for default DOP calculations. If you set
this parameter value to 50, then calculated default DOP will be multiplied by 0.50, reducing DOP to one-half of the
default DOP.
Parallel Statement Queuing
It is all too common for SQL statements to acquire fewer PX servers than the requested DOP in a production database.
Consider a SQL statement requesting a DOP of 32, but other SQL statements currently executing parallel statements
are using all PX servers except for four; then, this SQL statement will execute with a reduced parallelism of 4. Even if
more PX servers are available minutes after the start of parallel exectuion, the SQL statement will continue to execute
with a DOP of 4. A process with a normal run time of 1 hour might run for 8 hours (assuming a linear extrapolation)
and cause performance issues. Had that SQL statement delayed the execution for a few seconds, it could have
acquired all requested PX servers and might have completed the task in normal run time.
■
A short story about parallel execution. We had a client performance issue: a job intermittently ran longer than
normal. Our task was to identify the root cause, as the data was about the same every day. reviewing ASh data, we were
able to identify that a critical SQL statement in the program did not get enough PX servers on slower days. even though
the parallel_max_servers parameter was set to a much higher value, the SQL statement did not get enough PX servers.
Finally, we found the root cause to be that another process was scheduled at exactly the same second, consuming nearly
all available PX servers and starving the critical process. Whichever job allocated PX servers first got the PX servers,
and the other job could not get all of the required PX servers. The solution we used was to delay the non-critical job by a
minute to allow the critical process to acquire all PX servers. however, parallel statement queuing would have been an
even better solution for this problem.
Note
Prior to Database version 11.2, the parameter parallel_min_percent could be used (with little coding) to wait for
requested PX servers to be available. Since version 11.2, a waiting scheme for PX server availability can be achieved
more elegantly using the Parallel Statement Queuing feature. Essentially, if the requested number of PX servers are
not available, then the parallel execution is delayed and the process waits in a statement queue until the requested PX
servers are available.
If the parallel_degree_policy is set to AUTO, then the Parallel Statement Queuing feature is enabled. This feature
also can be enabled in a session by altering the
_parallel_statement_queuing
parameter to true.
alter session set "_parallel_statement_queuing"=true;
The
parallel_servers_target
parameter controls when the Parallel Statement Queuing feature becomes active.
If the number of PX servers currently executing in a cluster is greater than parallel_servers_target, then the feature
becomes active. Consider an example: if parallel_servers_target is set to 96 and the number of PX servers currently
active is 100, then this feature becomes active. If a SQL statement requests a parallel degree of 32, then the session will
