Product Generated SQL Are Not Optimized And Executes Too Many Times (Doc ID 2256260.1)

Last updated on AUGUST 18, 2017

Applies to:

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

Goal

Tuning of queries:


ggvcxxqyuku7b - select * from ( select billablech0_.BILLABLE_CHG_ID as col_0_0_ from CI_BILL_CHG billablech0_ where (billablech0_.SA_ID=:1 )and(billablech0_.START_DT<=:2 )and(billablech0_.BILLABLE_CHG_STAT=:3 )and((billablech0_.RECURRING_FLG=' ' )or(billablech0_.RECURRING_FLG is null ))and(not exists(select billsegmen1_.BSEG_ID from CI_BSEG_CALC billsegmen1_, CI_BSEG billsegmen2_ where (billablech0_.SA_ID=billsegmen2_.SA_ID )and(billsegmen1_.BSEG_ID=billsegmen2_.BSEG_ID )and(billsegmen1_.BILLABLE_CHG_ID=billablech0_.BILLABLE_CHG_ID )and(billsegmen2_.BSEG_STAT_FLG<>:4 )))and(billablech0_.ADHOC_BILL_SW<>'Y'/*NonRecurring*/) ) where rownum <= :5

aykj5kmxdu7jg - SELECT A.INIT_PRICEITEM_CD, A.RS_CD, A.PRICE_CURRENCY_CD, A.PRICE_ASGN_ID, PCH.ADHOC_CHAR_VAL FROM ( SELECT AG.*, PKG_RMB_PRICEASSIGN.FN_GET_AGRD_CRAWLING_LEVEL( AG.PARTY_TYPE_FLG, :1 , AG.PER_LEVEL) CRW_LVL FROM CI_PRC_AGRD_VW AG UNION ALL SELECT PL.*, PKG_RMB_PRICEASSIGN.FN_GET_PLST_CRAWLING_LEVEL( PL.PARTY_TYPE_FLG, :2 , PL.PER_LEVEL) CRW_LVL FROM CI_PRC_PL_VW PL UNION ALL SELECT IH.*, PKG_RMB_PRICEASSIGN.FN_GET_PLST_CRAWLING_LEVEL( IH.PARTY_TYPE_FLG, :3 , IH.PER_LEVEL) CRW_LVL FROM CI_PRC_INH_PL_VW IH ) A, CI_PRICEITEM_CHAR PCH WHERE A.ACCT_ID = :4 AND A.PA_TYPE_FLAG = 'POST' AND A.EFF_START_DT <= :5 AND A.BUN_SCR = 1 AND ((A.EFF_END_DT IS NULL) OR (A.EFF_END_DT >= :6 )) AND PCH.PRICEITEM_CD = A.INIT_PRICEITEM_CD AND PCH.CHAR_TYPE_CD = :7 AND PCH.EFFDT = (SELECT MAX(X.EFFDT) FROM CI_PRICEITEM_CHAR X WHERE X.PRICEITEM_CD = PCH.PRICEITEM_CD AND X.CHAR_TYPE_CD = PCH.CHAR_TYPE_CD AND X.EFFDT <= :8 ) ORDER BY A.CRW_LVL ASC, A.PER_LEVEL ASC, A.PL_PRIORITY ASC, A.PL _LEVEL ASC, A.PA_START_DT DESC, A.PA_USAGE_FLG DESC, PCH.ADHOC_CHAR_VAL ASC, A.FIN_PRICEITEM_CD ASC /* SQL for COBOL program 'CIPBSPAX', SQL name 'S_PRITEM'
 

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