Performance Issue With Collection Import Worker (Doc ID 1413921.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Quality - Version 11.5.10.2 and later
Information in this document applies to any platform.
***Checked for relevance on 05-JAN-2014***

Symptoms



Collection Import Worker import program is taking more than 2 hours to complete for small set of data.

1. Insert records in QA_RESULTS_INTERFACE to create ACCEPT records by the Quality
2. Run Collection Import Worker program.
We noticed below query is taking long time to process in Collection Import Worker program. It is doing full table scan on qa_sales_orders_lov_v. Any help on tuning this Oracle program would be helpful. It is impacting another downstream process.

INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN,
ERROR_MESSAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID,
:ERROR_COL_NAME,
:ERROR_INVALID_VALUE,
SYSDATE,
:USER_ID,
SYSDATE,
:USER_ID2,
:LAST_UPDATE_LOGIN,
:REQUEST_ID,
:PROGRAM_APPLICATION_ID,
:PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = :GROUP_ID AND QRI.PROCESS_STATUS = 2
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID =
QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN
(:c1, :c2, :c3, :c4, :c5))
AND QRI.SALES_ORDER IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM qa_sales_orders_lov_v SH
WHERE TO_CHAR(QRI.SALES_ORDER)= SH.ORDER_NUMBER)

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