Integration Interface Is Taking Very Long To Load Data (Doc ID 2278174.1)

Last updated on JUNE 19, 2017

Applies to:

Oracle Demantra Demand Management - Version 12.2.5.1 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.5.1 version, LoadData

ACTUAL BEHAVIOR
---------------
Customer is facing the issue of performance when they do an import of 6000
rows which takes about 30 minutes.
Number of Records in BIIO table: 6100
Number of Records in BIIO_ERR Table: 0
Start Time: Saturday, January 14, 2017 10:27:02 AM EST
End Time: Saturday, January 14, 2017 11:03:28 AM EST

As per AWR report:

FILE NAME: awrrpt_1_142_147.html
================================
Longest running SQL:
Module = JDBC Thin Client
BEGIN PROPORT_J (:1 ); END;
6,085.99 seconds (101 mins)
=> Import only took 36 mins so this must not be part of the Import.

Seconds longest:
Module = JDBC Thin Client
SELECT COUNT(O.OBJ#) FROM SYS....
=> same as above

Longest running SQL that was part of Import:
Module = DEM:JDBC THIN CLIENT
SELECT SDATE , (SELECT NVL(MIN...
607.38 secs (~10mins)

Second longest:
Module = DEM:JDBC THIN CLIENT
INSERT INTO BIIO_DPE_Mock_Cut_...
471.83 secs (7.86 mins)

See following SQL 8 times:
Module = DEM:JDBC THIN CLIENT
UPDATE SALES_DATA SET (EBS_BH_...
=> same SQL but each is tied to different T_UPDATE_LIST table
each takes from ~2 to ~3 mins

Customer is looking to improve the performance in the long running sql like
(from awr report) INSERT INTO BIIO_DPE_Mock_Cut_Over_Imp_ERR and ways that
they can achieve it. They would like to know how they can add parallel
process while doing the data validation and insert into BIIO_ERR table?
With their analysis they have identified a java class calls the validation
process which triggers a Insert and select statement. How can they modify the
java class and include parallel process."

 



EXPECTED BEHAVIOR
-----------------------
Expect to take less time.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Log into Workflow Manager.
2. Run an workflow with a transfer step to load 10K rows.
3. It is taking 1 hour.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot load data in the expected time. Customer is in a process of conversion and loading extract of production into UAT for mock cut over activity . The go-live is scheduled in 3rd week on Jan 2017 .

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