C1-GLASN Running Slow Possibly for FT Id Bind as BigInteger

(Doc ID 2318861.1)

Last updated on OCTOBER 24, 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.

Symptoms

ORMB v2.4.0.1.0 against FW v4.2.0.3.0 BT - Batch:
C1-GLASN running slow due to FT Id bind as BigInteger.

Infrastructure details:
> Application config
- 1 cache and 6 job threadpool workers on 2 submitter nodes ukdc1-oc-rbb11[1-2]
- 1 cache and 8 job threadpool workers on 6 worker nodes ukdc1-oc-rbb11[3-8]
- Each of the 8 application nodes have 16 CPU and 94 GB of RAM
- 4 threads per threadpool worker

>JVM settings: com.ouaf.batch.jvmoptions=-Xms4096m -Xmx8192m -XX:MaxPermSize=1024m -verbose:gc

>Database
- EXA with 3 nodes (2 active and 1 standby) for ORMB
- 24 CPU and 251GB memory per node

ACTUAL BEHAVIOR  
---------------------------------
C1-GLASN batch is running very slow in production. AWR Report has highlighted a problematic query as the top SQL for elapsed time and analysis showed that the problem is due to binding of BigInteger on FT_ID. FT_ID column in the tables is defined as CHAR(12) but the SQL is comparing these to numbers, and so the wrong indexes are getting used with a bad execution plan using the wrong driving table order.

SQL_ID  4700857mpw3nb, child number 0
-------------------------------------
SELECT FT.FT_ID, FTGL.DST_ID , DST.GL_CONST_ALG_CD, FTGL.GL_SEQ_NBR
FROM CI_FT FT , CI_FT_GL FTGL, CI_DST_CODE DST  WHERE FT.FT_ID >=:1
AND FT.FT_ID <=:2  AND  FTGL.GL_ACCT =:3  AND FTGL.F
T_ID = FT.FT_ID AND
FTGL.DST_ID = DST.DST_ID

EXPECTED BEHAVIOR
---------------------------------------
Performance issue should not occur for any specific SQL scripts.
We would like to know if it is intended that FT ids are being binded as Number/BigInteger in the java code, where it is possibly causing the bad performance.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Batch C1-GLASN

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
We would like to request for a prompt fix to be provided as this issue is currently occurring in PRODUCTION and greatly impacting our batch jobs from finishing within the expected timeframe.

Changes

 

Cause

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