About this question
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;
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;
Log in to share your answer and help other learners.
Log in to answerBest Answer · By JanBask MS SQL Server Expert
Answered on Mar 15, 2023
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.
Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.
Step-by-step SQL Server guides from industry experts
Guides, tips and career advice on SQL Server from JanBask experts.
SQL Server Top 75 SSAS Interview Questions and Answers For Beginners & Experts
Prepare for your SSAS interview with our comprehensive guide featuring 75 top SQL Server Analysis Services interview questions…
SQL Server How to Become a SQL Database Administrator?
How to become a sql database administrator In 2025, discover what these professionals do, explore how much they earn and learn…
SQL Server OLAP vs OLTP: Key Differences, Architectures, Performance & Real-World Examples
Compare OLTP vs OLAP with clear definitions, architecture diagrams, real-world examples, and FAQs. Learn when to use each system…
SQL Server 70+ Most Asked SSIS Interview Questions for Freshers & Experienced
Prepare for your next SQL Server Integration Services (SSIS) interview with our top 70+ SSIS interview questions and answers.…