Oracle: Auto DOP and PARALLEL_SERVERS_TARGET
Check the parallelism parameters and size PARALLEL_SERVERS_TARGET.
With automatic degree of parallelism (Auto DOP) and parallel statement queuing, introduced in Oracle 11g Release 2, the parallel server limit is controlled by a few parameters. This note shows how to check them and size PARALLEL_SERVERS_TARGET.
Check the parameters
SHOW PARAMETER PARALLEL_DEGREE_LIMIT
SHOW PARAMETER PARALLEL_THREADS_PER_CPU
SHOW PARAMETER CPU_COUNT
Size PARALLEL_SERVERS_TARGET
PARALLEL_SERVERS_TARGET = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2
Example with 2 threads per CPU, 16 CPUs and 1 concurrent parallel user: 2 x 16 x 1 x 2 = 64.
ALTER SYSTEM SET PARALLEL_SERVERS_TARGET = 64;
Monitor parallel activity
COLUMN name format a50
COLUMN VALUE format 999,999
SELECT NAME, VALUE
FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%'
OR UPPER (NAME) LIKE '%PX%'
;