What is the use of parallel hint in oracle?

1./*+ parallel(4) */ means you ask the optimizer to use 4 as a degree of parallelism.



SQL> explain plan for select /*+ parallel(4) */ * from t1;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'basic,cost,note'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------

Plan hash value: 2494645258

------------------------------------------------------

| Id  | Operation            | Name     | Cost (%CPU)|

------------------------------------------------------

|   0 | SELECT STATEMENT     |          |    90   (2)|

|   1 |  PX COORDINATOR      |          |            |

|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    90   (2)|

|   3 |    PX BLOCK ITERATOR |          |    90   (2)|

|   4 |     TABLE ACCESS FULL| T1       |    90   (2)|

------------------------------------------------------

Note

-----

   - Degree of Parallelism is 4 because of hint

15 rows selected.

SQL>

2./*+ parallel 4 */ means you ask the optimizer to use parallel execution, but you do not specify the degree, you let the database automatically decide the degree of parallelism. 4 is not part of the parallel hint in oracle, it is simply a comment, could be anything there.

SQL> explain plan for select /*+ parallel 4 */ * from t1;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'basic,cost,note'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------

Plan hash value: 2494645258

------------------------------------------------------

| Id  | Operation            | Name     | Cost (%CPU)|

------------------------------------------------------

|   0 | SELECT STATEMENT     |          |   179   (0)|

|   1 |  PX COORDINATOR      |          |            |

|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   179   (0)|

|   3 |    PX BLOCK ITERATOR |          |   179   (0)|

|   4 |     TABLE ACCESS FULL| T1       |   179   (0)|

------------------------------------------------------

Note

-----

   - automatic DOP: Computed Degree of Parallelism is 2

15 rows selected. 

SQL>Notice how the Note section in the first example shows that the Degree of Parallelism is 4 because of the hint, so the degree here used was 4. In the second example, the Note section contains automatic DOP: Computed Degree of Parallelism is 2, so the degree here used was 2, which explains the difference in cost you experienced.