Parallel Operations and Services in Oracle RAC

Yesterday, one of the members on Oracle Community forum posted that his query was not executing in parallel.
The query he used:

SELECT /*+ PARALLEL(r) */
r.c1 , r.c2
FROM table1 r

This was his output from show parameter parallel

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     4096
parallel_force_local                 boolean     FALSE
parallel_instance_group              string      op015pdb01
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     192
parallel_min_percent                 integer     0
parallel_min_servers                 integer     8
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_servers_target              integer     192
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     64

It turned out that the database he was using is Oracle RAC and what I found interesting is that parallel_instance_group was set to op015pdb01.
The docs clearly say that:

If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a service or group that does not exist, then the operation runs serially. No parallelism is used.

I’ve tested this on my enviorement, and as the documentation say I wasn’t able to execute the query in parallel.

SQL> show parameter parallel_instance_group;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
parallel_instance_group              string      ora1

SQL> show parameter instance_groups;

NAME                                 TYPE        VALUE
------------------------ ----------- --------------------
instance_groups                      string

SQL> explain plan for select /*+ parallel(r) */ object_id from table1 r;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 963482612

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 61519 |   781K|   283   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TABLE1 | 61519 |   781K|   283   (1)| 00:00:04 |
----------------------------------------------------------------------------

As you can see I got full table scan.
Now lets clear the parallel_instance_group and see what happens.

SQL> alter session set parallel_instance_group='';

Session altered.

SQL> explain plan for select /*+ parallel(r) */ object_id from table1 r;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1574891232

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 61519 |   781K|   283   (1)| 00:00:04 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 61519 |   781K|   283   (1)| 00:00:04 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 61519 |   781K|   283   (1)| 00:00:04 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| TABLE1   | 61519 |   781K|   283   (1)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Always, check your parameters in order to be sure that you haven’t overrode the default behavior :).

You can find the discussion here https://community.oracle.com/message/12499025

Kind Regards,
Ivica

Leave a Reply