ENABLE_PARALLEL_DML hint in 11g
From: Hameed, Amir <amir.hameed_at_sleepnumber.com>
Date: Sat, 6 Nov 2021 03:15:50 +0000
Message-ID: <CH0PR14MB50120F803F009914675840CCE08F9_at_CH0PR14MB5012.namprd14.prod.outlook.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'
;
Date: Sat, 6 Nov 2021 03:15:50 +0000
Message-ID: <CH0PR14MB50120F803F009914675840CCE08F9_at_CH0PR14MB5012.namprd14.prod.outlook.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-lReceived on Sat Nov 06 2021 - 04:15:50 CET