All Loyalty Transactions View Slow As Running Extra Query
(Doc ID 1970792.1)
Last updated on NOVEMBER 22, 2019
Applies to:
Siebel Loyalty Engine - Version 8.1.1.9 [23016] and laterInformation in this document applies to any platform.
Symptoms
When navigating to the LOY Transactions view, the application first runs a query to retrieve all transactions, then runs a second query to retrieve transactions with the visibility and settings applied. This can be see as follows:
- Sample database.
- Start Loyalty.
- Navigate to LOY Transactions screen.
- Log out.
- Review log. Will see two queries as follows:
First:
WHERE
T17.APPROVE_ID = T3.PAR_ROW_ID (+) AND
T17.DEST_ZONE_CD = T1.ROW_ID (+) AND
T17.ORIG_ZONE_CD = T13.ROW_ID (+) AND
T17.PTNR_USER_ID = T11.ROW_ID (+) AND
T17.ENROL_PROMO_ID = T4.ROW_ID (+) AND
T17.SUBMIT_TO_ID = T6.PAR_ROW_ID (+) AND
T17.POINT_TYPE_ID = T12.ROW_ID (+) AND
T17.MEMBER_ID = T5.ROW_ID (+) AND
T17.VOUCHER_ID = T8.ROW_ID (+) AND
T17.PROG_ID = T2.ROW_ID (+) AND
T17.PARTNER_ID = T14.PAR_ROW_ID (+) AND
T17.PROD_ID = T7.ROW_ID (+) AND
T17.SUBMIT_TO_ID = T16.PAR_ROW_ID (+) AND
T17.TRANSFER_MEM_ID = T15.ROW_ID (+) AND
T17.VOUCHER_TYPE_ID = T10.ROW_ID (+) AND
T17.ROW_ID = T9.PAR_ROW_ID (+)
ORDER BY
T17.TXN_NUM DESC
ObjMgrSqlLog Detail 4 0000000254d40eec:0 2015-02-06 00:57:37 Bind variable 1: ,,,SADMIN,0000000254d40eec:0,,LOY Transaction,LOY All Transactions View
ObjMgrSqlLog Debug 5 0000000254d40eec:0 2015-02-06 00:57:37 User search spec:
ObjMgrSqlLog Debug 5 0000000254d40eec:0 2015-02-06 00:57:37 User sort spec:Transaction Number(DESCENDING)
ObjMgrSqlLog Debug 5 0000000254d40eec:0 2015-02-06 00:57:37 System sort spec:
Second:
WHERE
T20.APPROVE_ID = T4.PAR_ROW_ID (+) AND
T20.DEST_ZONE_CD = T2.ROW_ID (+) AND
T20.ORIG_ZONE_CD = T16.ROW_ID (+) AND
T20.PTNR_USER_ID = T14.ROW_ID (+) AND
T20.ENROL_PROMO_ID = T6.ROW_ID (+) AND
T20.SUBMIT_TO_ID = T9.PAR_ROW_ID (+) AND
T20.POINT_TYPE_ID = T15.ROW_ID (+) AND
T20.MEMBER_ID = T8.ROW_ID (+) AND
T20.VOUCHER_ID = T11.ROW_ID (+) AND
T20.PROG_ID = T3.ROW_ID (+) AND
T20.PARTNER_ID = T17.PAR_ROW_ID (+) AND
T20.LOC_ID = T7.ROW_ID (+) AND
T20.PROD_ID = T10.ROW_ID (+) AND
T20.SUBMIT_TO_ID = T19.PAR_ROW_ID (+) AND
T20.TRANSFER_MEM_ID = T18.ROW_ID (+) AND
T20.VOUCHER_TYPE_ID = T13.ROW_ID (+) AND
T20.ROW_ID = T12.PAR_ROW_ID (+) AND
T1.BU_ID = :2 AND T20.ROW_ID = T1.TXN_ID AND
T1.BU_ID = T5.ROW_ID AND
(T1.TXN_NUM >= :3)
ORDER BY
T1.BU_ID DESC, T1.TXN_NUM DESC
ObjMgrSqlLog Detail 4 0000000254d40eec:0 2015-02-06 00:57:38 Bind variable 1:,,,SADMIN,0000000254d40eec:0,,LOY Transaction,LOY All Transactions View
ObjMgrSqlLog Detail 4 0000000254d40eec:0 2015-02-06 00:57:38 Bind variable 2:<RowId>
ObjMgrSqlLog Detail 4 0000000254d40eec:0 2015-02-06 00:57:38 Bind variable 3:
ObjMgrSqlLog Debug 5 0000000254d40eec:0 2015-02-06 00:57:38 User search spec:
ObjMgrSqlLog Debug 5 0000000254d40eec:0 2015-02-06 00:57:38 Named search[Sort Search Optimization]: 'Organization.TXN_NUM' >= ''
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |