What is the difference between NOPARALLEL and PARALLEL 1 in Oracle?

Question

What is the difference between NOPARALLEL and PARALLEL 1? If I create three tables like so:

CREATE TABLE t0 (i NUMBER) NOPARALLEL;
CREATE TABLE t1 (i NUMBER) PARALLEL 1;
CREATE TABLE t2 (i NUMBER) PARALLEL 2;

They show up in the data dictionary as

SELECT table_name, degree FROM user_tables WHERE table_name IN ('T0','T1','T2');

TABLE_NAME  DEGREE
T0               1 <==
T1               1 <==
T2               2

The documentation, however, states quite clearly

NOPARALLEL: Specify NOPARALLEL for serial execution. This is the default.

PARALLEL integer: Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers.

So, NOPARALLEL is definitely serial, while PARALLEL 1 uses one thread, which may use one or two parallel servers??? But how can Oracle distinguish between both of them when the data dictionary stores the same value 1 for both?

BTW, the CREATE TABLE sys.tab$ statment in ?/rdbms/admin/dcore.bsq has the comment

/* 
 * Legal values for degree, instances: 
 *     NULL (used to represent 1 on disk/dictionary and implies noparallel), or
 *     2 thru EB2MAXVAL-1 (user supplied values), or
 *     EB2MAXVAL (implies use default value) 
 */
degree        number,      /* number of parallel query slaves per instance */
instances     number,        /* number of OPS instances for parallel query */

Answer 1

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;