There is no difference between NOPARALLEL and PARALLEL 1 - those options are stored the same way and behave the same way. This is a documentation bug because Oracle will never use two parallel execution servers for PARALLEL 1. We can test this situation by looking at V$PX_PROCESS and by understanding the producer/consumer model of parallelism.
How to Test Parallelism
There are many ways to measure the amount of parallelism, such as the execution plan or looking at GV$SQL.USERS_EXECUTING. But one of the best ways is to use the view GV$PX_PROCESS. The following query will show all the parallel servers currently being used:
select *
from gv$px_process
where status <> 'AVAILABLE';
Producer/Consumer Model
The Using Parallel Execution chapter of the VLDB and Partitioning Guide is worth reading if you want to fully understand Oracle parallelism. In particular, read the Producer/Consumer Model section of the manual to understand when Oracle will double the number of parallel servers.
In short - Each operation is executed in parallel separately, but the operations need to feed data into each other. A full table scan may use 4 parallel servers to read the data but the group by or order by operations need another 4 parallel servers to hash or sort the data. While the degree of parallelism is 4, the number of parallel servers is 8. This is what the SQL Language Reference means by the sentence "Each parallel thread may use one or two parallel execution servers."
Oracle doesn't just randomly double the number of servers. The doubling only happens for certain operations like an ORDER BY, which lets us test precisely when Oracle is enabling parallelism. The below tests demonstrate that Oracle will not double 1 parallel thread to 2 parallel servers.
Tests
Create these three tables:
create table table_noparallel noparallel as select level a from dual connect by level <= 1000000;
create table table_parallel_1 parallel 1 as select level a from dual connect by level <= 1000000;
create table table_parallel_2 parallel 2 as select level a from dual connect by level <= 1000000;
Run the below queries, and while they are running use a separate session to run the previous query against GV$PX_PROCESS. It may be helpful to use an IDE here, because you only have to retrieve the first N rows and keep the cursor open to count as using the parallel servers.
--0 rows:
select * from table_noparallel;
--0 rows:
select * from table_noparallel order by 1;
--0 rows:
select * from table_parallel_1;
--0 rows:
select * from table_parallel_1 order by 1;
--2 "IN USE":
select * from table_parallel_2;
--4 "IN USE":
select * from table_parallel_2 order by 1;
Notice that the NOPARALLEL and the PARALLEL 1 table work exactly the same way and neither of them use any parallel servers. But the PARALLEL 2 table will cause the number of parallel execution servers to double when the results are ordered.
Why is PARALLEL 1 Even Allowed?
Why doesn't Oracle just force the PARALLEL clause to only accept numbers larger than one and avoid this ambiguity? After all, the compiler already enforces a limit; the clause PARALLEL 0 will raise the error "ORA-12813: value for PARALLEL or DEGREE must be greater than 0".
I would guess that allowing a numeric value to mean "no parallelism" can make some code simpler. For example, I've written programs where the DOP was calculated and passed as a variable. If only numbers are used, the dynamic SQL is as simple as:
v_sql := 'create or replace table test1(a number) parallel ' || v_dop;
If we had to use NOPARALLEL, the code gets a bit uglier:
if v_dop = 1 then
v_sql := 'create or replace table test1(a number) noparallel';
else
v_sql := 'create or replace table test1(a number) parallel ' || v_dop;
end if;
