C1-TXNSQ - Performance Issue (Doc ID 2283550.1)

Last updated on JULY 17, 2017

Applies to:

Oracle Financial Services Revenue Management and Billing - Version 2.4.0.1.0 and later
Information in this document applies to any platform.

Goal

Following SQL's are changing execution plans resulting in performance discrepancies. With identical data, the batch sometimes ends in less than 5 minutes and at times for the same data it takes a good 40+ minutes.

1. SELECT TCL.SUMMARY_ID,RC_SEQ,DESCR_ON_BILL,CURRENCY_CD, APP_IN_SUMM_SW,DST_ID,AGG_PARM_GRP_ID,TCLH.CHAR_TYPE_CD,TCLH.CHAR_VAL,ADHOC_CHAR_VAL FROM (SELECT SUMMARY_ID, RUN_GRP_ID FROM CI_TXN_DTL_PRITM_SUMMARY WHERE SUMMARY_ID >=? AND SUMMARY_ID <= ? AND RUN_GRP_ID = ? AND STATUS = ' ' AND TXN_RATING_CRITERIA = ? AND SW_PRM_CIS_DIVISION =? GROUP BY SUMMARY_ID, RUN_GRP_ID) S,CI_TXN_CALC_LN TCL,CI_TXN_CALC_LN_CHAR TCLH WHERE S.SUMMARY_ID = TCL.SUMMARY_ID AND S.RUN_GRP_ID = TCL.RUN_GRP_ID AND TCL.TXN_CALC_ID = TCLH.TXN_CALC_ID AND TCL.SEQNO = TCLH.SEQNO AND TCL.CALC_ONLY_SW = ? AND TCLH.CHAR_ENTITY_FLG = ? AND TCL.AGG_PARM_GRP_ID <> 1 GROUP BY TCL.SUMMARY_ID,RC_SEQ,TCL.SEQNO, DESCR_ON_BILL,CURRENCY_CD, APP_IN_SUMM_SW,DST_ID,AGG_PARM_GRP_ID,TCLH.CHAR_TYPE_CD,TCLH.CHAR_VAL,ADHOC_CHAR_VAL ORDER BY TCL.SUMMARY_ID,RC_SEQ,TCL.SEQNO, DESCR_ON_BILL,CURRENCY_CD, APP_IN_SUMM_SW,DST_ID,TCLH.CHAR_TYPE_CD,TCLH.CHAR_VAL,ADHOC_CHAR_VAL

2. SELECT TCL.SUMMARY_ID,RC_SEQ,DESCR_ON_BILL,SUM(CALC_AMT) AS CALC_AMT, SUM(PRECS_CALC_AMT) AS PRECS_CALC_AMT, CURRENCY_CD, APP_IN_SUMM_SW,DST_ID,AGG_PARM_GRP_ID FROM (SELECT SUMMARY_ID, RUN_GRP_ID FROM CI_TXN_DTL_PRITM_SUMMARY WHERE SUMMARY_ID >= ? AND SUMMARY_ID <= ? AND RUN_GRP_ID = ? AND STATUS = ' ' AND TXN_RATING_CRITERIA = ? AND SW_PRM_CIS_DIVISION =? GROUP BY SUMMARY_ID, RUN_GRP_ID) S,CI_TXN_CALC_LN TCL WHERE S.SUMMARY_ID = TCL.SUMMARY_ID AND S.RUN_GRP_ID = TCL.RUN_GRP_ID AND TCL.CALC_ONLY_SW = ? GROUP BY TCL.SUMMARY_ID,RC_SEQ,SEQNO,DESCR_ON_BILL,CURRENCY_CD, APP_IN_SUMM_SW, DST_ID,AGG_PARM_GRP_ID ORDER BY TCL.SUMMARY_ID,RC_SEQ,SEQNO,DESCR_ON_BILL,CURRENCY_CD, APP_IN_SUMM_SW,DST_ID,AGG_PARM_GRP_ID
 

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