Search notes:
Oracle: Default Degree of Parallelism for tables
create table tq84_L (id number, val varchar2(10));
create table tq84_R (id number, val varchar2(10));
Display the execution plan for a select statement that joins both tables:
explain plan for
select
*
from
tq84_L l join tq84_R r on l.id = r.id;
select * from table(dbms_xplan.display(format => 'basic'));
--
-- -------------------------------------
-- | Id | Operation | Name |
-- -------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH JOIN | |
-- | 2 | TABLE ACCESS FULL| TQ84_L |
-- | 3 | TABLE ACCESS FULL| TQ84_R |
-- -------------------------------------
Modify default DOP for one table…
alter table tq84_L parallel 4;
… and show the plan for the same query again. The
PX COORDINATOR operation indicates that the query is executed in parallel.
TQ84_L is accessed with the parallel
PX BLOCK operation:
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 |
| 3 | HASH JOIN BUFFERED | |
| 4 | PX RECEIVE | |
| 5 | PX SEND HYBRID HASH | :TQ10000 |
| 6 | STATISTICS COLLECTOR | |
| 7 | PX BLOCK ITERATOR | |
| 8 | TABLE ACCESS FULL | TQ84_L |
| 9 | PX RECEIVE | |
| 10 | PX SEND HYBRID HASH | :TQ10001 |
| 11 | PX SELECTOR | |
| 12 | TABLE ACCESS FULL | TQ84_R |
-----------------------------------------------
Modify the other table's default DOP as well …
alter table tq84_R parallel 4;
… and display the plan again. This time, also TQ84_R is accessed with the parallel PX BLOCK operation:
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 |
| 3 | HASH JOIN BUFFERED | |
| 4 | PX RECEIVE | |
| 5 | PX SEND HYBRID HASH | :TQ10000 |
| 6 | STATISTICS COLLECTOR | |
| 7 | PX BLOCK ITERATOR | |
| 8 | TABLE ACCESS FULL | TQ84_L |
| 9 | PX RECEIVE | |
| 10 | PX SEND HYBRID HASH | :TQ10001 |
| 11 | PX BLOCK ITERATOR | |
| 12 | TABLE ACCESS FULL | TQ84_R |
-----------------------------------------------
Cleaning up:
drop table tq84_L;
drop table tq84_R;