Transaction to Instrument Allocation Hangs on Update (Doc ID 1076106.1)

Last updated on MARCH 29, 2010

Applies to:

Oracle Profitability Manager - Version: 4.5 to 4.5.39 - Release: 4.5 to 4.5
Information in this document applies to any platform.
Oracle Financial Services Applications (OFSA) 4.5
Oracle Performance Analyzer - Version: 4.5 to 4.5.39

Symptoms

In Oracle Performance Analyzer (PA) 4.5, when running a Batch ID, a transaction table to instrument table Allocation ID is hanging.  The Allocation has run for over 12 hours and has not updated any records. The Allocation hangs on the following SQL statement:

update DEPOSITS b
   SET
     (identity_code_chg,
     OD_FEES
     ) =
       (SELECT 5831,
         GREATEST (
           -999999999999,
           LEAST (
             999999999999,
             DECODE (b.identity_code_chg, 5831, b.OD_FEES, 0)
             + SUM(NVL(COST,0) * 0.01130000) * 1
           )
       )
   FROM TRANSACTIONS a
  WHERE (((a.TRANSACTION_ID = 10111)
         OR (a.TRANSACTION_ID = 10112)
         or (a.TRANSACTION_ID = 10114)))
        AND a.as_of_date = '02/28/2010'
        AND (b.IDENTITY_CODE = a.IDENTITY_CODE
        AND b.ID_NUMBER = a.ID_NUMBER))
WHERE b.COMMON_COA_ID = :COMMON_COA_ID
   AND EXISTS
       (SELECT *
          FROM TRANSACTIONS a
         WHERE ((((a.TRANSACTION_ID = 10111)
               OR (a.TRANSACTION_ID = 10112)
               or (a.TRANSACTION_ID = 10114)))
             AND a.as_of_date = '02/28/2010')
             AND b.IDENTITY_CODE = a.IDENTITY_CODE
             AND b.ID_NUMBER = a.ID_NUMBER)
 AND b.as_of_date = '02/28/2010';


Both tables have the required indexes for transaction to instrument allocations as described in My Oracle Support <<Note 232394.1>> "Performance Problems With Transaction To Instrument Table Allocations".

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