How can we use use parallel (10) hint in Oracle Merge statement

Question

How can we use parallel (10) hints in the oracle merge statement? , I am a bit new to hints and would like to know if this can be done for merge statements?

Answer 1

In very-very short:

First of all, parallel execution must be enabled on the server. You need to check the following 3 parameters:

select name,value 
from v$parameter 
where name in (
 'parallel_degree_policy'
,'parallel_servers_target'
,'parallel_max_servers'
);

• PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes, so it must be >0
• PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available.
• PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled. Values:

  • MANUAL: Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release 2 (11.2). This is the default.

  • LIMITED: Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the PARALLEL clause. Tables and indexes that have a degree of parallelism specified will use that degree of parallelism.

  • AUTO: Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.

Then you can check your session parameters:

select id,name,sql_feature,isdefault,value 
from v$ses_optimizer_env e 
where name like '%parallel%'
  and sid=userenv('sid');

        ID NAME                                     SQL_FEATURE  ISDEFAUL VALUE
---------- ---------------------------------------- ------------ -------- -------------------------
         2 parallel_execution_enabled               QKSFM_CBO    YES      true
        13 parallel_threads_per_cpu                 QKSFM_CBO    YES      2
        35 parallel_query_mode                      QKSFM_ALL    YES      enabled
        36 parallel_dml_mode                        QKSFM_ALL    YES      disabled
        37 parallel_ddl_mode                        QKSFM_ALL    YES      enabled
       245 parallel_degree_policy                   QKSFM_PQ     YES      manual
       246 parallel_degree                          QKSFM_PQ     YES      0
       247 parallel_min_time_threshold              QKSFM_PQ     YES      10
       256 parallel_query_default_dop               QKSFM_PQ     YES      0
       272 parallel_degree_limit                    QKSFM_PQ     YES      65535
       273 parallel_force_local                     QKSFM_PQ     YES      false
       274 parallel_max_degree                      QKSFM_PQ     YES      16
       289 parallel_autodop                         QKSFM_PQ     YES      0
       290 parallel_ddldml                          QKSFM_PQ     YES      0
       331 parallel_dblink                          QKSFM_PQ     YES      0

Here you can notice three xxx_mode parameters: parallel_ddl_mode, parallel_dml_mode and parallel_query_mode, which allows related parallel operations on session level and can be enabled or disable using alter session enable/disable/force, for example alter session force parallel dml parallel 16 - enable parallel dml with DOP=16:

SQL> alter session force parallel dml parallel 16;

Session altered.

SQL> select id,name,sql_feature,isdefault,value 
  2  from v$ses_optimizer_env e
  3  where name like 'parallel_dml%'
  4    and sid=userenv('sid');

        ID NAME                      SQL_FEATURE ISDEFAULT  VALUE
---------- ------------------------- ----------- ---------- -------------------------
         4 parallel_dml_forced_dop   QKSFM_CBO   NO         16
        36 parallel_dml_mode         QKSFM_ALL   NO         forced

Hint parallel has 2 forms:

• statement level parallel(DoP)

• and object level parallel(alias DoP)

In fact, this hint

1. enables parallel execution in case of parallel_degree_policy parameter=MANUAL
2. reduces a cost of parallel operations to make parallel plans more preferable (it doesn't force parallel execution - your plan still will be serial if its' cost cheaper then parallel one), and
3. specifies DoP (Degree of Parallelism).

Next, different plan steps can have different DOP, or even run serially: for example, different objects (indexes or tables or their partitions) can have different parallel options (alter table xxx parallel 8) or "read" operations (row-sources) can be parallelized, but "change" (like UPDATE or MERGE) operations are serial.

For example, with parallel_degree_policy=manual and disabled parallel dml:

SQL> explain plan for update/*+ parallel(4) */ t set b=a+1 where a >1;

Plan hash value: 1378397380

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |  9999 | 79992 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  UPDATE               | T        |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  9999 | 79992 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  9999 | 79992 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| T        |  9999 | 79992 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("A">1)

Note
-----
   - Degree of Parallelism is 4 because of hint

You can see than FTS (Full table scan) is parallel, but UPDATE is serial. On newer actually supported Oracle versions and you can see one more note - PDML is disabled in current session.

And if you enable parallel dml using alter session enable parallel dml or alter session force parallel dml parallel N (on actual oracle version you can use also enable_parallel_dml hint, it was backported to 11.2.0.4, but documented only since 12.1, so I wouldn't suggest to use it until at least 12.1):

SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for update/*+ parallel(4) */ t set b=a+1 where a >1;

Plan hash value: 2037160838

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |  9999 | 79992 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |  9999 | 79992 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | T        |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |          |  9999 | 79992 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| T        |  9999 | 79992 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("A">1)

Note
-----
   - Degree of Parallelism is 4 because of hint

As you can see, now UPDATE is parallel too.

So, it depends on your current database, table and sessions parameters and what exactly you want to get and: enable parallel operations in case of parallel_degree_policy=manual, or enable parallel dml, or limit/force required DOP, etc

A bit more details for start:

• 19.1.2.7 Hints for Parallel Execution

• Using Parallel Execution

• How Parallel Execution Works

Answer 2

How? Like this:

SQL> alter session enable parallel dml;

Session altered.

SQL> merge /*+ parallel(10) */ into emp e
  2    using dept d
  3    on (d.deptno = e.deptno)
  4    when matched then update set
  5      e.comm = e.comm * 1;

14 rows merged.

SQL>

The PARALLEL hint will enable read-parallelism, but to also enable write-parallelism you will need to either run the above ALTER SESSION command or use the hint /*+ ENABLE_PARALLEL_DML */.