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
|
3 Answers
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.
-
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– yash singh2020-10-12 07:57:58 +00:00Commented Oct 12, 2020 at 7:57
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.yash singh– yash singh2020-10-10 04:40:59 +00:00Commented Oct 10, 2020 at 4:40
-
@yashsingh You should also compare duration and cost to the unhinted query.anon– anon2020-10-10 06:19:39 +00:00Commented Oct 10, 2020 at 6:19
-
@amtwo: duration in unhinted query is more than 10 hrs.yash singh– yash singh2020-10-11 06:21:23 +00:00Commented 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 (the4is ignored). I don't know why you are looking at cost.William Robertson– William Robertson2020-10-11 09:21:53 +00:00Commented Oct 11, 2020 at 9:21
But doubt is what to use and best for update the data into tables like 10 milions of records. Degree of parallelism how to choose means what basis..its not clear
