Re: ENABLE_PARALLEL_DML hint in 11g

Oh. That is very interesting tip. I'll definitely read it.

On Sat, 6 Nov 2021, 11:38 Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:

>
> 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