Parallel execution is an Oracle feature that allows one process to split the job it needs to do in several subprocesses. This job, implies I/O and CPU operations.
This feature is controlled by several parameters at instance level and the DEGREE value at segments definition level.
By default, including 12.1.0, Oracle has defined parameter PARALLEL_DEGREE_POLICY = MANUAL and by default all segments are created with Degree of Parallelism (DoP) set to 1.
It means that, Oracle will execute all SQL in serial, by default
When Parallel Execution is needed, a specific DoP can be manually set using three different ways:
1.- Set a fixed DoP at segment level:
ALTER TABLES SALES PARALLEL 6;
In this case, queries accessing the SALES table via full scan use a requested DoP of 6.
2.-Use PARALLEL hint in SQL
SQL>ALTER TABLE SALES PARALLEL 1; SQL>SELECT /*+ PARALLEL(6) */ count(*) from SALES;
In this case, this query uses a request DoP of 6.
3.-“Force” the session to run in parallel
SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 6;
For above cases, the execution plan is the same
Parallel Execution Details (DOP=6 , Servers Allocated=6) ============================================================================================================================================ | Name | Type | Server# | Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Cell | Wait Events | | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Offload | (sample #) | ============================================================================================================================================ | PX Coordinator | QC | | 0.57 | 0.01 | | 0.00 | 0.56 | 5 | | . | NaN% | | | p000 | Set 1 | 1 | 0.26 | 0.07 | 0.19 | | 0.00 | 15041 | 130 | 117MB | 98.03% | | | p001 | Set 1 | 2 | 0.28 | 0.07 | 0.20 | | 0.01 | 16198 | 141 | 126MB | 98.03% | | | p002 | Set 1 | 3 | 0.27 | 0.07 | 0.19 | | 0.00 | 16198 | 140 | 126MB | 98.00% | | | p003 | Set 1 | 4 | 0.27 | 0.07 | 0.20 | | | 16198 | 140 | 126MB | 97.94% | | | p004 | Set 1 | 5 | 0.27 | 0.07 | 0.20 | | 0.00 | 15041 | 130 | 117MB | 98.02% | | | p005 | Set 1 | 6 | 0.27 | 0.07 | 0.20 | | | 15124 | 140 | 118MB | 98.05% | | ============================================================================================================================================ SQL Plan Monitoring Details (Plan Hash Value=2278008498) =============================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) | =============================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 1 | | | | | | | | 1 | SORT AGGREGATE | | 1 | | 1 | +1 | 1 | 1 | | | | | | | | 2 | PX COORDINATOR | | | | 2 | +0 | 7 | 6 | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 1 | +1 | 6 | 6 | | | | | | | | 4 | SORT AGGREGATE | | 1 | | 1 | +1 | 6 | 6 | | | | | | | | 5 | PX BLOCK ITERATOR | | 490K | 4711 | 1 | +1 | 6 | 490K | | | | | | | | 6 | TABLE ACCESS STORAGE FULL | SALES | 490K | 4711 | 1 | +1 | 82 | 490K | 821 | 730MB | 98.01% | 18M | | | ===============================================================================================================================================================================
But the question is, when do I need to execute a sentence in parallel?
The answer always depends on the environment you are playing in.
Parallelism is the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time. This work can be broken down in two different types: I/O work and CPU work.
Here is when EXADATA environments are different. When Full Scan is used in EXADATA, Smart Scan goes into action, using all cell nodes and doing all I/O in parallel.
So, does it has sense to use parallel when the work for a SQL happen at I/O?
The answer for this is no, because there is a cost associated with coordinating the parallel execution servers and the cost of this coordination may outweigh the benefits of parallelism.
So, under this premise, in EXADATA, parallelism has sense when the amount of CPU used by the SQL statement is high enough to get benefit from it.
This implies than the better option for enabling parallelism is using the PARALLEL hint into your SQL and use it only when it is really needed.
One last point, Tanel Poder explains here that above three options will not FORCE the parallel execution, but rather just reduces optimizer cost estimates for full scans.