Allocation Appears to be Hanging for Partitioned Ledger Stat Table for New Fiscal Year Data (Doc ID 2233653.1)

Last updated on FEBRUARY 14, 2017

Applies to:

Oracle Financial Services Profitability Management - Version 6.1.1.1 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Profitability Management (PFT)
Performance
Slow running allocations

Symptoms

On PFT 6.1.1.1, when running allocations for the new period and new fiscal year, several allocations do not appear to be running and are either hanging or failed.

ERROR
Allocation process appears to be hanging: ofspa OFSA_201761_EXEC_20170131_1 Allocation Engine Task1 20170131 201761 PFTUSER NULL.

Check of the database shows the create global temporary table statements are hanging. Same is found in the PFT debug log:
Module Logging OFS Access module data: CREATE
CREATE GLOBAL TEMPORARY TABLE GLBL12251TMP ON COMMIT PRESERVE
ROWS AS
SELECT *
FROM LEDGER_STAT a
WHERE a.FINANCIAL_ELEM_ID IN (SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE (node_03=10400)
AND sys_id_num=200151)
and a.ORG_UNIT_ID IN (SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num = 200153
and (OFSA_IDT_ROLLUP.NODE_06 IN (
select REV_HIERARCHY_FILTER.LEAF_NODE
from REV_HIERARCHY_FILTER
where REV_HIERARCHY_FILTER.sys_id_num = 296533
and REV_HIERARCHY_FILTER.hierarchy_level = 9)
OR OFSA_IDT_ROLLUP.NODE_05 IN (
select REV_HIERARCHY_FILTER.LEAF_NODE
from REV_HIERARCHY_FILTER
where REV_HIERARCHY_FILTER.sys_id_num = 296533
and REV_HIERARCHY_FILTER.hierarchy_level = 10) ))
and a.GL_ACCOUNT_ID IN (SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE (node_10=358990)
AND sys_id_num=200159)
and (((a.CONSOLIDATION_CD = '100')))
and a.year_s IN (2017)
AND a.CONSOLIDATION_CD = 100


am_log_file.log shows Signal 9 errors:

Log Message here 1: Fri Feb 3 09:17:35 2017||SEVERE||GetChildStatus : Child Terminated abnormally. Signal number = 9Log Message here 1: .Log Message here 1: Fri Feb 3 09:17:35 2017||SEVERE||GetChildStatus : Child Terminated abnormally. Signal number = 9Log Message here 1: .Fri Feb 3 09:17:35 2017||INFO||GlobalAccess::Unlock_Reply : Success
Log Message here 1: Fri Feb 3 09:17:35 2017||SEVERE||Sigchild Handler : Child process Returned failure

The allocations run without issue in lower environment with prior period dec 2016 data.

Have tried several times to restart the services and instance without allocations completing. These allocations have not been changed since 2014.

The issue can be reproduced at will with the following steps:
1. Run production 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