What's difference between parallel (4) and parallel 4 with and without brackets? For eg :
select /*+parallel(4) */ * from table_name;select /*+parallel 4 */ * from table_name;
Add a comment
|
2 Answers
The first is proper syntax, the second is not.
See here: https://riptutorial.com/oracle/example/4853/parallel-hint
-
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. Oct 10, 2020 at 4:40
-
@yashsingh You should also compare duration and cost to the unhinted query.– AMtwoOct 10, 2020 at 6:19
-
-
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 (the4is ignored). I don't know why you are looking at cost. Oct 11, 2020 at 9:21
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.
answered Oct 11, 2020 at 14:49
-
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. Oct 12, 2020 at 7:57