Performance Issue With Delete Statement From Incentive Compensation Calculation Process. (Doc ID 3026116.1)

Last updated on JUNE 06, 2024

Applies to:

Symptoms

Users are seeing a performance issue with the below query when attempting to do their OIC Calculation: 

SQL statement with SQL_ID 6duuxrtgdngk9.
DELETE /*+ index(line cn_commission_lines_n7) */
CN_COMMISSION_LINES_ALL LINE WHERE LINE.CREDITED_SALESREP_ID IN
(SELECT SALESREP_ID FROM CN_PROCESS_BATCHES_ALL WHERE
PHYSICAL_BATCH_ID = :B7 ) AND LINE.PROCESSED_PERIOD_ID BETWEEN :B6
AND :B5 AND LINE.PROCESSED_DATE BETWEEN :B4 AND :B3 AND LINE.STATUS
<> 'OBSOLETE' AND LINE.ORG_ID = :B2 AND ((:B1 ='FORECAST' AND
LINE.TRX_TYPE = 'FORECAST') OR (:B1 = 'COMMISSION' AND LINE.TRX_TYPE
NOT IN ('BONUS','FORECAST'))) AND ROWNUM < 10000

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.