Re: ENABLE_PARALLEL_DML hint in 11g

Another little tip for performance of a MERGE command. Particularly relevant if it's an UPDATE-only.
Don't reference the underlying tables, replace each table (in the INTO and USING clauses) with an inline view referencing only the columns you need from the two tables, so:

merge into (select column list from table1) tab1 using (select column list from table2) tab2 on tab1.cola = tab2.cola .... etc,

See https://jonathanlewis.wordpress.com/2016/06/06/merge-precision/ for a longer explanation

Regards
Jonathan Lewis

On Sat, 6 Nov 2021 at 08:39, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> BTW: you should not use parallel(LOG,4) but just parallel(4).
>
>
> Am 06.11.2021 um 04:20 schrieb Sayan Malakshinov:
>
> Hi Amir,
>
> ENABLE_PARALLEL_DML was introduced in oracle 12.1, so it doesn't work on
> 11.2.0.4
>
>
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE
> http://orasql.org
>
> сб, 6 нояб. 2021 г., 6:16 Hameed, Amir <amir.hameed_at_sleepnumber.com>:
>
>> Hi,
>>
>> The database version is 11.2.0.4. I am trying to modify a MERGE statement
>> by creating a SQL baseline and introducing parallelism through hints as
>> shown below:
>>
>>
>>
>> -------------------------------------
>>
>> MERGE /*+ enable_parallel_dml parallel(LOG,4) */ INTO CN_NOTIFY_LOG_ALL
>>
>> LOG USING CN_PROCESS_BATCHES_ALL BATCH ON ( BATCH.PHYSICAL_BATCH_ID =
>>
>> :B1 AND BATCH.ORG_ID = LOG.ORG_ID AND BATCH.SALESREP_ID =
>>
>> LOG.SALESREP_ID AND LOG.PERIOD_ID BETWEEN BATCH.PERIOD_ID AND
>>
>> BATCH.END_PERIOD_ID AND LOG.START_DATE >= BATCH.START_DATE) WHEN
>>
>> MATCHED THEN UPDATE SET LOG.STATUS = 'COMPLETE' WHERE LOG.STATUS =
>>
>> 'INCOMPLETE'
>>
>> ;
>>
>>
>>
>> When the statement is run through SQL*Plus via a script, parallelism is
>> working. However, it is not working when adding the above hints by using
>> the SQL baseline method. Is there any special required for SQL baselines
>> when parallelizing DML operations?
>>
>>
>>
>> Thank,
>>
>> Amir
>>
>
>

--
http://www.freelists.org/webpage/oracle-l