Degree Of Parallelism Is Not LIMITED By PARALLEL_DEGREE_LIMIT (Doc ID 1308910.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later
Information in this document applies to any platform.

Symptoms

When parallel_Degree_policy is set to LIMITED, the parallel_degree_limit is
only affects when the queries that have objects decorated with degree
DEFAULT.

On the contrary, the older documentation was unclear since it did not mention DEFAULT
explicitly, in detail:


LIMITED

Enables automatic degree of parallelism for some statements but statement
queuing and in-memory Parallel Execution are disabled. Automatic degree of
parallelism is only applied to those statements that access tables or indexes
decorated explicitly with the PARALLEL clause. Tables and indexes that have a
degree of parallelism specified will use that degree of parallelism.

For example:

CREATE TABLE EMP
( EMP2NO NUMBER(4,0)
) parallel;

conn /as sysdba
grant dba to scott;
conn scott/tiger
create table emp1 as select * from emp;
insert into emp1 select * from emp1;
insert into emp1 select * from emp1;
insert into emp1 select * from emp1;
insert into emp1 select * from emp1;
insert into emp1 select * from emp1;
insert into emp1 select * from emp1;
insert into emp1 select * from emp1;
commit;
alter table emp1 parallel 8;
alter session set parallel_degree_policy = LIMITED;
alter session set parallel_degree_limit = 2;
show parameter parallel_degree_

NAME TYPE VALUE
------------------------------------ ----------- ------
parallel_degree_limit string 2
parallel_degree_policy string LIMITED

select count(*) from emp1;

COUNT(*)
----------
1792

select STATISTIC, LAST_QUERY
from v$pq_sesstat
where STATISTIC in ('Queries Parallelized',
'DFO Trees',
'Server Threads',
'Allocation Height');

STATISTIC LAST_QUERY
------------------------------ ----------
Queries Parallelized 1
DFO Trees 1
Server Threads 8
Allocation Height 8 <---- Correct result should be 2.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms