Loyalty Engine Startup Slows Down Because Of Promotion Attribute Volumes

(Doc ID 2202741.1)

Last updated on MARCH 02, 2017

Applies to:

Siebel Loyalty Engine - Version 8.1.1.14.18 [IP2014] to 16.6 [IP2016] [Release V8 to V16]
Information in this document applies to any platform.

Goal

Customer saw a performance issue on start up due to the volume of attribute records in the following SQL:

 

SELECT /*+ ALL_ROWS */
T2.CONFLICT_ID,
T2.LAST_UPD,
T2.CREATED,
T2.LAST_UPD_BY,
T2.CREATED_BY,
T2.MODIFICATION_NUM,
T2.ROW_ID,
T2.DISPLAY_NAME,
T2.INTERNAL_NAME,
T2.OBJECT_CD,
T2.PICK_APPLET,
T2.PICK_FIELD,
T2.PICK_FIELD_NAME,
T2.PICK_LIST,
T2.ATTR_TYPE_CD,
T2.DATA_TYPE_CD,
T2.DEFAULT_VAL,
T1.BUSCOMP_NAME,
T2.PROGRAM_ID,
T2.PROMOTION_ID,
T2.READONLY_FLG,
T2.X_ATTR_LOCAL_NAME,
:1
FROM
SIEBEL.S_PICKLIST T1,
SIEBEL.S_LOY_ATTRDEFN T2
WHERE
T2.PICK_LIST = T1.NAME (+) AND
(T2.OBJECT_CD = :2) AND
(T2.ACTIVE_FLG = :3)
ORDER BY
T2.PROGRAM_ID, T2.PROMOTION_ID, T2.ROW_ID
ObjMgrSqlLog Detail 4 0007e7ed580701e8:0 2016-10-19 15:34:18 Bind variable 1: mcrm_LoyEngineBatch_Tr,E81P11SRVS01,491782147,SADMIN,0007e7ed580701e8:0,,LOY Promotion Bucket Attribute Definition,
ObjMgrSqlLog Detail 4 0007e7ed580701e8:0 2016-10-19 15:34:18 Bind variable 2: Promotion
ObjMgrSqlLog Detail 4 0007e7ed580701e8:0 2016-10-19 15:34:18 Bind variable 3: Y
ObjMgrSqlLog Detail 4 0007e7ed580701e8:0 2016-10-19 15:34:18
***** SQL Statement Prepare Time for SQL Cursor with ID 458FCB88: 0.000 seconds *****

ObjMgrSqlLog SqlTag 2 0007e7ed580701e8:0 2016-10-19 15:34:18 Begin: Execute SqlObj 'LOY Promotion Bucket Attribute Definition' at 45bec078 with SqlTag=mcrm_LoyEngineBatch_Tr,E81P11SRVS01,491782147,SADMIN,0007e7ed580701e8:0,,LOY Promotion Bucket Attribute Definition,
SQLParseAndExecute Bind Vars 4 0007e7ed580701e8:0 2016-10-19 15:34:18 1: mcrm_LoyEngineBatch_Tr,E81P11SRVS01,491782147,SADMIN,0007e7ed580701e8:0,,LOY Promotion Bucket Attribute Definition,
SQLParseAndExecute Bind Vars 4 0007e7ed580701e8:0 2016-10-19 15:34:18 2: Promotion
SQLParseAndExecute Bind Vars 4 0007e7ed580701e8:0 2016-10-19 15:34:18 3: Y
DBCLog DBCLogDetail 4 0007e7ed580701e8:0 2016-10-19 15:34:18 Siebel Workload Tagging :: Unable to set tagging information into MODULE attribute
ObjMgrSqlLog Detail 4 0007e7ed580701e8:0 2016-10-19 15:38:47
***** SQL Statement Execute Time for SQL Cursor with ID 458FCB88: 269.651 seconds *****

 

This shows that just attributes with active - Y are loaded. Promotion attributes set to inactive are still loaded and if there are thousands of these, the performance on startup only is an issue.
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms