The Oracle PARALLEL hint instructs the optimizer to use a specified number of concurrent servers (degree of parallelism, or DOP) for a SQL operation, potentially improving performance for data-intensive tasks by dividing the work among multiple processes.
The number of parallel execution servers associated with a single operation is known as the degree of parallelism (DOP).
Let’s say a query takes 100 seconds to execute without using parallel hint. If we change DOP to 2 for same query, then ideally the same query with parallel hint will take 50 second. Similarly using DOP as 4 will take 25 seconds.
PARALLEL :-
This hint tells the optimizer to run the query in PARALLEL with the degree of parallelism set for the object.
SQL> SELECT degree FROM user_tables WHERE table_name = 'EMP';
DEGREE
----------------------------------------
1
SQL> SELECT /*+ PARALLEL */ sal FROM emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - PARALLEL
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
PARALLEL(<DOP>) :-
This hint tells the optimizer to run the query in PARALLEL with the specified degree of parallelism.
SQL> SELECT /*+ PARALLEL(6) */ sal FROM emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 6 because of hint
SQL> SELECT /*+ PARALLEL(emp,6) */ sal FROM emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 6 because of table property
PARALLEL(AUTO) :-
The database computes the degree of parallelism which can be 1 or greater.If the computed degree of parallelism is 1 then the statement runs serially.
SQL> SELECT /*+ PARALLEL(AUTO) */ ename FROM emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
PARALLEL(MANUAL) :-
The optimizer is forced to use the parallel settings of the object in the statement.
SQL> SELECT /*+ PARALLEL(MANUAL) */ ename FROM emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> ALTER TABLE emp PARALLEL 4;
Table altered.
SQL> SELECT /*+ PARALLEL(MANUAL) */ ename FROM emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 84 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 84 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 84 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of table property
PARALLEL_INDEX (<table_name>,<index_name>,<DOP|DEFAULT>) :-
This hint tells the optimizer to use the specified # of concurrent servers to parallelize index-range scan,full scan,fast full scan for partioned indexes.
NO_PARALLEL_INDEX (<table_name>,<index_name>,<DOP|DEFAULT>) :-
This hint tells the optimizer to NOT use the specified # of concurrent servers to parallelize index-range scan,full scan,fast full scan for partioned indexes.
NO_PARALLEL :-
This hint overrides any parallel execution for a statement.
SQL> ALTER TABLE sales PARALLEL 8;
Table altered.
SQL> SELECT * FROM sales;
Execution Plan
----------------------------------------------------------
Plan hash value: 2592042707
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 23M| 159 (2)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000K| 23M| 159 (2)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1000K| 23M| 159 (2)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| SALES | 1000K| 23M| 159 (2)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 8 because of table property
SQL> SELECT /*+ NO_PARALLEL(sales) */ * FROM sales;
Execution Plan
----------------------------------------------------------
Plan hash value: 781590677
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 23M| 1146 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SALES | 1000K| 23M| 1146 (2)| 00:00:01 |
---------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - NO_PARALLEL(sales)
SQL> SELECT name,value FROM gv$sysstat WHERE UPPER(name) LIKE '%PARALLEL OPERATIONS%' OR UPPER(name) LIKE '%PARALLELIZED%' OR UPPER(name) LIKE '%PX%';
NAME VALUE
---------------------------------------------------------------- ----------
queries parallelized 7
DML statements parallelized 0
DDL statements parallelized 1
DFO trees parallelized 12
Parallel operations not downgraded 12
Parallel operations downgraded to serial 0
Parallel operations downgraded 75 to 99 pct 0
Parallel operations downgraded 50 to 75 pct 0
Parallel operations downgraded 25 to 50 pct 0
Parallel operations downgraded 1 to 25 pct 0
PX local messages sent 3215
PX local messages recv'd 3215
PX remote messages sent 0
PX remote messages recv'd 0
14 rows selected.
