Database Reference
In-Depth Information
■
The
parallel_adaptive_multi_user
initialization parameter is relevant only in two cases. First, when using
manual degree of parallelism. Second, when using automatic degree of parallelism which is enabled by setting the
parallel_degree_policy
initialization parameter to
limited
.
Note
To illustrate the impact of the
parallel_adaptive_multi_user
initialization parameter, let's take a look at the
number of slave processes allocated when an increasing number of concurrent parallel operations are executed at
short intervals. For this purpose, the following shell script was used. Its purpose is to start 20 concurrent parallel
queries (each query runs for more than a dozen minutes) with a degree of parallelism of 16 (this is the default at the
table level) at intervals of 5 seconds:
sql="select * from t;"
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
do
sqlplus -s $user/$password <<<$sql &
sleep 5
done
Figure
15-9
summarizes the results measured on version 11.2. With the
parallel_adaptive_multi_user
initialization parameter set to
FALSE
, the number of allocated slave processes is proportional to the number of
executed parallel operations (in other words, each one runs with the same degree of parallelism) up to the limit
imposed by the default value of the
parallel_max_servers
initialization parameter (160 in this case). With the
parallel_adaptive_multi_user
initialization parameter set to
TRUE
, starting from nine concurrent parallel
operations, the degree of parallelism decreases, and therefore, fewer slave processes than requested are allocated.
Figure 15-9.
Impact of the
parallel_adaptive_multi_user
initialization parameter