Calculation Performance Due to Multiple Calls to Same UDQ in the Formula Expression (Doc ID 2619769.1)

Last updated on APRIL 28, 2023

Applies to:

Symptoms

Review Calculation performance and the longest running jobs are from child Calculation Phase 2 ( CALCPHASE2 ) processes.
The long running sql query is from the User Defined Query ( UDQ ) used in the measure formula expression.

STEPS

1. Create Value Set
    Setup and Maintenance > Manage Incentive Value Sets
         Value Set Code = CHECK_MOBILE_ACT
         Module = Incentive Compensation
         Value Type = Table
         Value Data Type = Number
         From Clause = cn_tp_transactions_all
         Value Column Name = 1
         WHERE Clause =
                     ATTRIBUTE81 = :1 /*Varchar2*/
              AND TRANSACTION_TYPE= 'ACT'
              AND ATTRIBUTE10 =:2 /*Varchar2*/
              AND OBJECT_STATUS = 'CREDITED'
              AND rownum < 2
2. Add to lookup code
    Setup and Maintenance > Manage Incentive Lookups
         Lookup Type = ORA_CN_USR_DEFN_EXPRSN_VLE_SET
         Lookup Code = CHECK_MOBILE_ACT
3. Create Expression
    Compensation Plans > Manage Expression > Create
          Rate Table Rate * Credit.Credit Amount * ( DECODE ( CHECK_MOBILE_ACT ( Credit.ID_CHIAVE_LINEA , 'LIS443' ) , 1 , 1 , 0 ) + DECODE ( CHECK_MOBILE_ACT ( Credit.ID_CHIAVE_LINEA , 'LIS431' ) , 1 , 1 , 0 ) + DECODE ( CHECK_MOBILE_ACT ( Credit.ID_CHIAVE_LINEA , 'LIS445' ) , 1 , 1 , 0 ) ) * TO_NUMBER ( DECODE ( NVL ( Measure.AT5 (Cluster) , 0 ) , 0 , 0 , 1 ) ) * CHOICE ( IS_EQUAL ( NVL ( CHECK_PIANI_ESCLUSI ( Credit.PIANO_TARIFFARIO , '20190111' , Credit.SOURCE_EVENT_DATE ) , 0 ) , 0 ) , 1 , 0 )
4.  Create Performance Measure and use the expression
5.  Add Performance Measure to plan component with Calculation Phase = 2
6.  Load volume transactions and Calculate
7.  Review calculation performance timing

Cause

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