Poor Performance Collections Form - Dunning History (Doc ID 2281356.1)

Last updated on JUNE 30, 2017

Applies to:

Oracle Advanced Collections - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Collections Performance

The dunning history form query is performing very poor due to a full table scan on IEX_DUNNINGS.

The following SQL statement is causing the performance problem:

SELECT DUNNING_ID COL1, DUNNING_ID COL2, STAGE COL3, DELINQUENCY_ID COL4,
PARTY_CUST_ID COL5, PARENT_DUNNING_ID COL6, CREATION_DATE COL7, STATUS COL8,
STATUS_MEANING COL9, OBJECT_TYPE COL10, DUNNING_LEVEL COL11,
DUNNING_LEVEL_MEANING COL12, BUCKET_LINE COL13, DUNNING_METHOD COL14,
DUNNING_METHOD_MEANING COL15, OBJECT_TYPE_MEANING COL16, TEMPLATE_NAME
COL17, FFM_REQUEST_ID COL18, FFM_STATUS COL19, AMOUNT_DUE_REMAINING COL20,
TRX_NUMBER COL21, TERMS_SEQUENCE_NUMBER COL22, DUE_DATE COL23,
CUST_TRX_CLASS_NAME COL24, PURCHASE_ORDER COL25, SALES_ORDER COL26,
CONS_BILLING_NUMBER COL27, OPERATING_UNIT COL28, FINANCIAL_CHARGE COL29,
INTEREST_AMT COL30, LETTER_NAME COL31, OBJECT_ID COL32, DUNNING_OBJECT_ID
COL33, DUNNING_PLAN_ID COL34, DUNNING_PLAN_NAME COL35, CONTACT_DESTINATION
COL36, CONTACT_PARTY_ID COL37, CONTACT_PARTY_NAME COL38, CUSTOMER_TRX_ID
COL39, ORG_ID COL40, DUNNING_ERROR COL41, DUNNING_MODE COL42,
CONFIRMATION_MODE COL43, DOCUMENT_TYPE_CODE COL44, DOCUMENT_TYPE_MEANING
COL45, ADDT_STATUS COL46, PAYMENT_SCHEDULE_ID COL47
FROM
IEX_TRX_DUNNINGS_BALI_V WHERE PAYMENT_SCHEDULE_ID = :B_PS_ID ORDER BY COL1
ASC,COL3 ASC

 

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