Oracle Database Hints Parallel Hint
Example
Statement-level parallel hints are the easiest:
SELECT /*+ PARALLEL(8) */ first_name, last_name FROM employee emp;
Object-level parallel hints give more control but are more prone to errors; developers often forget to use the alias instead of the object name, or they forget to include some objects.
SELECT /*+ PARALLEL(emp,8) */ first_name, last_name FROM employee emp;
SELECT /*+ PARALLEL(table_alias,Degree of Parallelism) */ FROM table_name table_alias;
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.
In practice, parallel execution depends on many other factors and does not scale linearly. This is especially true for small run times where the parallel overhead may be larger than the gains from running in multiple parallel servers.
