All Loyalty Transactions View Slow As Running Extra Query (Doc ID 1970792.1)

Last updated on FEBRUARY 05, 2019

Applies to:

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

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