In my Oracle 12c database I want a statement to be executed with parallel degree 2 without the use of a hint. Note: this is a sample table so there is no improvement in cost or time.
Execution Plan with parallelism 1
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2671887276
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------
| 0 | SELECT STATEMENT | | 1 | 674 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EVENT | 1 | 674 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EVENT_PK | 1 | | 1 (0)| 00:00:01 |
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EVENT_PK"='zjmtzhjrth')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Execution plan with hint /*+parallel(2) */ where DoP works fine
PLAN_TABLE_OUTPUT
---------------
Plan hash value: 2851389777
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------
| 0 | SELECT STATEMENT | | 1 | 674 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 674 | 2 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | TABLE ACCESS BY INDEX ROWID | EVENT | 1 | 674 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 1 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND HASH (BLOCK ADDRESS)| :TQ10000 | 1 | | 1 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 7 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 8 | INDEX UNIQUE SCAN | EVENT_PK | 1 | | 1 (0)| 00:00:01 | Q1,00 | SCWP | |
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("EVENT_PK"='zjmtzhjrth')
Note
-----
- Degree of Parallelism is 2 because of hint
Then I executed the following statements
alter system set parallel_degree_policy=MANUAL;
alter table event parallel 2;
But when I execute the statement without the hint, it doesn't use parallelism. It doesn't even give me the Note about the DoP in the execution plan.
PLAN_TABLE_OUTPUT
----------------
Plan hash value: 2671887276
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 674 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EVENT | 1 | 674 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EVENT_PK | 1 | | 1 (0)| 00:00:01 |
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EVENT_PK"='zjmtzhjrth')
Can anyone tell my why this is not working?
Regarding the questions in the comments:
PARALLEL_DEGREE_LIMIT=CPU
When I set PARALLEL_DEGREE_POLICY back to AUTO it gives me the note again:
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
The statement I issued for my tests is
select * from event where event_pk = 'swdfklwe';
Following Cyrille's comment I tried every combination of selected columns and columns in the where clause. The statement just won't use DoP 2 when an index unique scan is used.
select event_pk, result form event where event_pk = 'swdfklwe'
select event_pk form event where event_pk = 'swdfklwe'
select event_pk, result form event where event_pk = 'swdfklwe' and result = 0
select event_pk form event where event_pk = 'swdfklwe' and result = 0
Parallel execution is for speeding up queries which traverse a large number of records. It divided the total set of records to be searched into smaller sets and processes multiple sets concurrently. This trades off increased consumption of system resource - primarily CPU - for a reduced total response time.
Your table has a unique index on the searched column. So there can be only one record which matches 'EVENT_PK"='zjmtzhjrth'. There is no way parallelism can make that faster.
The optimizer has chosen the most efficient access path to retrieve one row. Be happy that it has.
Why wouldn't