My Oracle Support Banner

Instrument to Ledger Stat Allocation with Match Source & Driver Runs Slow or Hangs (Doc ID 1325551.1)

Last updated on DECEMBER 04, 2019

Applies to:

Oracle Financial Services Profitability Management - Version 5.2 to 5.2.2 [Release 5]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Checked for relevance on 20-Aug-2015.

Symptoms

In Oracle Financial Services Profitability Management (OFSPM) 5.2, you have an Instrument or Transaction table to Ledger Stat Dynamic Driver Allocation using the "Match Source & Driver" option that runs for over 24 hours.  The Allocation has a design like the following:

Source: Table: Mortgages Transaction Summary, Column: Expense
*
Driver: Table: Participant Account, Column: Avg Balance

Debit: LEDGER_STAT with the following set:

Financial Element: 170001
Organizational Unit: <Match Source & Driver>
General Ledger Account: <Same as Driver>
Common Chart of Accounts: <Match Source & Driver>
Product: 0

After running a SQL Trace with tkprof against a limited run of the allocation, the following SQL appears to be the problem as it is executed over and over against many distinct combinations of ORG_UNIT_ID and COMMON_COA_ID.

select SUM(a.AVG_BOOK_BAL)
from PRTCPT_ACCT a
where a.ORG_UNIT_ID = :ORG_UNIT_ID
and a.COMMON_COA_ID = :COMMON_COA_ID
and a.as_of_date='12/ 31/ 1999'

   call  count      cpu    elapsed       disk      query         current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0     0 0
Execute  10901     0.69       0.88          0          0     0 0
Fetch    10900    15.46    1664.82     233535     382777     0 10900
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total    21802    16.16    1665.71     233535     382777     0 10900

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88

Rows    Row Source Operation
------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=67 pr=50 pw=0 time=0 us)
     81  NESTED LOOPS SEMI (cr=67 pr=50 pw=0 time=483200 us cost=16 size=228 card=4)
     81   TABLE ACCESS BY INDEX ROWID PRTCPT_ACCT (cr=62 pr=50 pw=0 time=483120 us cost=8 size=328 card=8)
     81    INDEX RANGE SCAN PRTCPT_ACCT_N2 (cr=5 pr=0 pw=0 time=0 us cost=4 size=0 card=8)(object id 107092)
     3    INDEX RANGE SCAN OFSA_IDT_ROLLUP_N1 (cr=5 pr=0 pw=0 time=0 us cost=1 size=32 card=2)(object id 107098)


Elapsed times include waiting on following events:
Event waited on                          Times  Max. Wait   Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client                21801    0.00     0.01
SQL*Net message from client              21801    0.03     7.12
db file sequential read                 234075    0.37  1655.00

You need to have the allocation run much faster to meet processing cycle deadlines.

Steps to Reproduce:

  1. Go to Profitability Management
  2. Go to Rule Specification > Allocation Specification
  3. Create an Instrument table to Ledger Stat Dynamic Driver Allocation using <Match Source & Driver>

The Allocation runs for more than 24 hours.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.