LLI Wiki — My Digital Knowledge Base

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%'
;

Notes

Mastodon LLIMastodon SocialFosstodon