Very Slow Performance of Allocations After New Server Install (Doc ID 556900.1)

Last updated on JANUARY 15, 2016

Applies to:

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

Symptoms

After installing Oracle Financial Services Applications on a new server, in Performance Analyzer version 4.5.39, all Ledger Stat allocations are taking much longer to run.  For example, an allocation that ran in 5 minutes is now taking over 2 hours. A tkprof'd SQL trace on one of the Allocations shows the following INSERT statement is doing a full table scan:

  insert into ledger_stat (month_01,ytd_01,month_02,ytd_02,month_03,ytd_03,
  month_04,ytd_04,month_05,ytd_05,month_06,ytd_06,month_07,ytd_07,month_08,
  ytd_08,month_09,ytd_09,month_10,ytd_10,month_11,ytd_11,month_12,ytd_12,
  identity_code,year_s,accum_type_cd,consolidation_cd,FINANCIAL_ELEM_ID,
  ORG_UNIT_ID,GL_ACCOUNT_ID,COMMON_COA_ID,PROD_ID,iso_currency_cd) select 
  sum(month_01),sum(ytd_01),sum(month_02),sum(ytd_02),sum(month_03),
  sum(ytd_03),sum(month_04),sum(ytd_04),sum(month_05),sum(ytd_05),
  sum(month_06),sum(ytd_06),sum(month_07),sum(ytd_07),sum(month_08),
  sum(ytd_08),sum(month_09),sum(ytd_09),sum(month_10),sum(ytd_10),
  sum(month_11),sum(ytd_11),sum(month_12),sum(ytd_12),identity_code,year_s,
  accum_type_cd,consolidation_cd,FINANCIAL_ELEM_ID,ORG_UNIT_ID,GL_ACCOUNT_ID,
  COMMON_COA_ID,PROD_ID,iso_currency_cd from ALLOC_182808_6bd5 where 
  (identity_code,year_s,accum_type_cd,consolidation_cd,FINANCIAL_ELEM_ID,
  ORG_UNIT_ID,GL_ACCOUNT_ID,COMMON_COA_ID,PROD_ID) not in (select 
  identity_code,year_s,accum_type_cd,consolidation_cd,FINANCIAL_ELEM_ID,
  ORG_UNIT_ID,GL_ACCOUNT_ID,COMMON_COA_ID,PROD_ID from ledger_stat) group by 
  identity_code,year_s,accum_type_cd,consolidation_cd,FINANCIAL_ELEM_ID,
  ORG_UNIT_ID,GL_ACCOUNT_ID,COMMON_COA_ID,PROD_ID,iso_currency_cd


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.01          0          1          0           0
Execute      1   1387.31    1360.64      17602   46679519          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2   1387.33    1360.66      17602   46679520          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 60  (EB)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   HASH (GROUP BY)
      0    FILTER
      0     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'ALLOC_182808_6BD5' 
                (TABLE)
      0     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'LEDGER_STAT' (TABLE)

You review the index on the temporary table ALLOC_182808_6bd5 by querying the OFSA_STP table:

select description from ofsa_stp where timestamp > 'insert yesterday's date'
and taskname = 'CREATE INDEX';

The unique index generated for the temporary table looks incorrect.  For example:

create index ALLOC_182808_6bd5 on ALLOC_182808_6bd5 (COMMON_COA_ID, CONSOLIDATION_CD, FINANCIAL_ELEM_ID, GL_ACCOUNT_ID, IDENTITY_CODE, ISO_CURRENCY_CD, ORG_UNIT_ID, PROD_ID, YEAR_s)

Normally, the temporary table index begins with IDENTITY_CODE, YEAR_S, ACCUM_TYPE_CD, etc.  In this case, the temporary table index has COMMON_COA_ID as the first column.

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