PARALLEL_ADAPTIVE_MULTI_USER

Property	Description
Parameter type	Boolean
Default value	true
Modifiable	ALTER SYSTEM
Range of values	true | false

PARALLEL_ADAPTIVE_MULTI_USER, when set to true, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.

The algorithm assumes that the system has been tuned for optimal performance in a single-user environment.

Tables and hints use the default degree of parallelism.

Note:

1. On a 17 CPU machine, for example, the default degree of parallelism could be set to 32. If one user issues a parallel query, that user get a degree of 32, effectively using all of the CPUs and memory in the system. When a second user enters the system, that user gets a degree of 16. As the number of users on the system increases, this algorithm will continue to reduce the degree until the users are running using degree 1, when there are 32 users on the system. Over time, the behavior of the system as a whole will be improved.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘PARALLEL_ADAPTIVE_MULTI_USER’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE		ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
parallel_adaptive_multi_user	TRUE	TRUE	FALSE	IMMEDIATE	TRUE		FALSE	enable adaptive setting of degree for multiple user streams

Oracle initializatoin parameters