11gr2 : Parallel Hint

Its more than 3 years 11g has released, still looking or finding something new every day or other.

Today its the Parallel Hint that I came across from the documentation.

Many has already blogged about this, still I am writing the same for my readers and

Until 10g, the parallel hint behaviour is like below for example parallel (t,5)

• object t access will be parallel, but if the optimizer not choose that and choosen any index , the referenced index will not use parallel
• object level parallelism need to invoked seperately, for example parallel(t,5), parallel (emp_pk, 3) etc

But from 11g1  and 11g2 onwards, a statement level parallel hint has been introduced to override both

From Documentation:-

Beginning with Oracle Database 11g Release 2 (11.2.0.1), the PARALLEL and NO_PARALLEL hints are statement-level hints and supersede the earlier object-level hints: PARALLEL_INDEX, NO_PARALLEL_INDEX, and previously specified PARALLEL and NO_PARALLEL hints. For PARALLEL, if you specify integer, then that degree of parallelism will be used for the statement. If you omit integer, then the database computes the degree of parallelism. All the access paths that can use parallelism will use the specified or computed degree of parallelism.

For a statement-level PARALLEL hint:

• PARALLEL: The statement always is run parallel, and the database computes the degree of parallelism, which can be 2 or greater.

• PARALLEL (DEFAULT): The same as PARALLEL. The DEFAULT keyword is included for completeness.

• 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.

• PARALLEL (MANUAL): The optimizer is forced to use the parallel settings of the objects in the statement.

• PARALLEL (integer): The optimizer uses the degree of parallelism specified by integer.

If you observe above , there is no object reference in the hint,

In the following example, the optimizer calculates the degree of parallelism. The statement always runs in parallel.

• SELECT /* PARALLEL */ LAST_NAME FROM EMPLOYEES
• SELECT /* PARALLEL */ LAST_NAME FROM EMPLOYEES WHERE DEPT_ID=10;

The above statements and the objects involved in it, tables/indexes will always run in parallel without using the object level parallel hints like FULL (EMP), Parallel (EMP,2), PARALLEL_INDEX(DEPT_IDX,2) etc.

Hope this helps