RP VARIANCE RULE QUERY PERFORMANCE (Doc ID 2803846.1)

Last updated on SEPTEMBER 08, 2021

Applies to:

Symptoms

The RP variance rule dynamic package is having the below query

BEGIN
 SELECT A.AC_STAT_DORMANT
 INTO L_AC_DORMANT
 FROM STTMS_ACCOUNT_BALANCE A, STTMS_CUST_ACCOUNT B
WHERE B.CUST_NO=P_CUSTOMER_NO AND A.CUST_AC_BRN = B.BRANCH_CODE AND A.CUST_AC_BRN=GLOBAL.CURRENT_BRANCH AND ROWNUM = 1;
EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
DEBUG.PR_DEBUG('CO','FAILED WHILE SELECTING DATA FROM STTMS_ACCOUNT_BALANCE FOR CUSTOMER ' || P_CUSTOMER_NO);
END;

Package copks_rule_gen is used for generating dynamic package based on RP rule, above query is coming in that dynamic package.
The above query does not have the account number in where condition due to which the cost of query is high.

Cause

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