CODEWITHSUNDEEP
oracle-databaseperformanceparallel-processingoracle12csql-execution-plan
jera
jera

Reputation: 322

Query does not use specified parallel degree

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

Upvotes: 1

Views: 2514

Answers (2)

APC
APC

Reputation: 146209

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

Upvotes: 3

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

It work like expected on my side:

SQL> create table t1 (id number);

Table created.

SQL> alter table t1 parallel 2;

Table altered.

SQL> explain plan for select * from t1;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2494645258

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  PX COORDINATOR      |          |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR |          |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1       |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 2 because of table property

and here are the parameters I have (all defaults)

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
containers_parallel_degree           integer     65535
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_max_servers                 integer     40
parallel_min_percent                 integer     0
parallel_min_servers                 integer     4

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_time_threshold          string      AUTO
parallel_servers_target              integer     16
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
SQL>

Upvotes: 0

Related Questions