PARALLEL and NO_PARALLEL Hint

Introduction and Warning

Before discussing how to use the parallel hints, it is important to note, that these hints should be used sparingly, with caution and only when your system has enough IO and CPU resources to do so.   It is exceptionally easy to bring a system to its knees using these hints.  If you plan to use these hints, heed the warning and test before implementation.  That said, lets take a look at how to use them.

PARALLEL Hint Description

The PARALLEL hint in Oracle enables parallel execution by breaking down a large query or DML operation into smaller tasks, known as granules, which are processed concurrently by multiple parallel execution servers. A central coordinator oversees the process, assigning granules to servers, gathering intermediate results, and merging them into the final output. For instance, in a parallel query on a large table, Oracle divides the table into chunks (such as partitions or data blocks) and assigns each chunk to a different parallel server for processing. This method leverages multiple CPUs, memory, and I/O resources, significantly reducing execution time for operations like full table scans, joins, or aggregations. The degree of parallelism, which determines the number of parallel servers, can be explicitly specified in the hint.

Example

SELECT /*+ PARALLEL(myTab1, degree) */ col1 

  FROM myTab1

/

NO_PARALLEL Hint Description

The NO_PARALLEL hint explicitly disables parallel execution for a query or DML operation, overriding any parallelism settings that may be configured at the system, session, or object level. 

Example

SELECT /*+ NO_PARALLEL(myTab1) */ col1 

  FROM myTab1

/

Notes

• You can find more information on using parallel query in the documentation

• If you want to use PARALLEL Query for DML (delete, insert, merge, and update statements), then you will also need the following hint ENABLE_PARALLEL_DML

Published on 

Published 10th January 2025