RE: [EXTERNAL] Re: ENABLE_PARALLEL_DML hint in 11g

From: Hameed, Amir <amir.hameed_at_sleepnumber.com>
Date: Sat, 6 Nov 2021 04:04:19 +0000
Message-ID: <CH0PR14MB5012DA61BFDF5F8F7619AE1BE08F9_at_CH0PR14MB5012.namprd14.prod.outlook.com>

Thank you, Sayan! It seems that I misunderstood that it worked in 11.2.0.4 (https://twitter.com/JLOracle/status/566042653309927424)

Thanks,
Amir
From: Sayan Malakshinov <xt.and.r_at_gmail.com> Sent: Friday, November 5, 2021 11:20 PM
To: Hameed, Amir <amir.hameed_at_sleepnumber.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: [EXTERNAL] Re: ENABLE_PARALLEL_DML hint in 11g

CAUTION: External source

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<https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=04%7C01%7Camir.hameed%40sleepnumber.com%7C19554eeae5f344958ef108d9a0d45b41%7Ced8aabd514de49829fb6d6528851af5e%7C0%7C0%7C637717656210686511%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=8WHvd9WuF2L6KxpXIQ4qFGLQO8lhL%2B7%2B%2BfucPXP97oQ%3D&reserved=0>

ÓÂ, 6 ÎÏÑÂ. 2021 Ç., 6:16 Hameed, Amir <amir.hameed_at_sleepnumber.com<mailto: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

Received on Sat Nov 06 2021 - 05:04:19 CET