My Oracle Support Banner

Query S_BLSAFL2 causing a performance hit for time out (Doc ID 2859923.1)

Last updated on APRIL 11, 2022

Applies to:

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

Symptoms

On RMB v2.9.0.0.0, DBA Observations for Query S_BLSAFL2 - possible cause for a performance hit.

The team wonders if SUBSTR on RHS { bill_id = substr(:2,1,12)} is needed for this environment.

SELECT 'X' FROM DUAL WHERE EXISTS (SELECT 'X' FROM CI_FT FT WHERE FT.SA_ID = :1 AND SUBSTR(FT.BILL_ID, 1, 12) = :2 AND FT.FT_TYPE_FLG IN ('AD' , 'AX' , 'BS' , 'BX') AND FT.FREEZE_SW = 'Y' AND FT.SHOW_ON_BILL_SW ='Y' AND SUBSTR(FT.MATCH_EVT_ID , 1 , 1) <> ' ' AND EXISTS (SELECT 'X' FROM CI_FT FT2 WHERE FT2.MATCH_EVT_ID = FT.MATCH_EVT_ID AND FT2.PARENT_ID = :3 AND FT2.SA_ID = FT.SA_ID)) /* SQL for program 'com.splwg.ccb.domain.payment.service.ProgramCIPPMBSL_Impl', SQL name 'S_BLSAFL2' */

ACTUAL BEHAVIOR
----------------------
Time out on screen.

EXPECTED BEHAVIOR
------------------------
User should be able to search the payment successfully.

STEPS
----------------
• Try to search payments from various options:
o Via payment event
o Via Account

• Try to search payment on different type of contracts:
o Normal service agreement
o Excess credit service agreement
 
• Try to search payment with different Status:
o Frozen
o Cancel
o Freezable
o Incomplete
o Error

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
Cause
Solution
References


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