Slow Performance of Allocations Run Against View (Doc ID 2112235.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Financial Services Profitability Management - Version 6.1 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

In Oracle Financial Services Profitability Management (PFT) 6.1.0.3, several Static Driver Allocations are taking over 10 hours each to run.  The Allocations merge data into LEDGER_STAT from a custom view.  The view creation statement includes a join on DIM_ORG_UNIT_B to populate the ORG_UNIT_ID column.  The Allocations include a Hierarchy Filter on the ORG_UNIT_ID column.

The view has a processing key defined on ID_NUMBER and IDENTITY_CODE. The base table contains the required unique index on ID_NUMBER and IDENTITY_CODE.

The explain plan of the MERGE INTO statement shows a full table scan is done against the view.  It is not using the available indexes.

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