Beyond Data Access and Join Optimization - Troubleshooting Oracle Performance - page 611

■ 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