1

What's difference between parallel (4) and parallel 4 with and without brackets? For eg :

  1. select /*+parallel(4) */ * from table_name;
  2. select /*+parallel 4 */ * from table_name;

2 Answers 2

3

The first is proper syntax, the second is not.

See here: https://riptutorial.com/oracle/example/4853/parallel-hint

4
  • No, both are correct statements. But when I use 2nd statement, the COST of query decreases and it really helps. But, when I use parallel(4) in query it takes too much time and also increases COST.
    – yash singh
    Oct 10, 2020 at 4:40
  • @yashsingh You should also compare duration and cost to the unhinted query.
    – AMtwo
    Oct 10, 2020 at 6:19
  • @amtwo: duration in unhinted query is more than 10 hrs.
    – yash singh
    Oct 11, 2020 at 6:21
  • 2
    @yash no, the documentation clearly states that brackets are required. Therefore, /*+ parallel 4 */ means use the automatically generated degree of parallelism, which is probably much higher (the 4 is ignored). I don't know why you are looking at cost. Oct 11, 2020 at 9:21
2

1./*+ parallel(4) */ means you ask the optimizer to use 4 as 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 hint, 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 first example shown that Degree of Parallelism is 4 because of hint, so degree here used was 4.

In the second example, Note section contains automatic DOP: Computed Degree of Parallelism is 2, so degree here used was 2, which explains the difference in cost you experienced.

1
  • thanks, but the problem is that there are 70 million records in table and we are fetching these records from production db. Now, process start at 12 am and has to be completed by 8 am in the morning. Any suggestions, how to optimize the query.
    – yash singh
    Oct 12, 2020 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.