R12: Project Financial Plan Performance Using API PA_BUDGET_PUB.BASELINE_BUDGET Very Low For Large Number Of Lines (Doc ID 1328670.1)

Last updated on MARCH 01, 2017

Applies to:

Oracle Project Foundation - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Runing the API APPS.PA_BUDGET_PUB.create_draft_budget and  PA_BUDGET_PUB.baseline_budget to create and Baseline the Financial Plan with Large Number of Budget Lines attached to the Fin Plan.

Performance is poor.

The following SQL is showing as poorly performing SQL
INSERT INTO PJI_FP_AGGR_PJP1_T ( WORKER_ID, RECORD_TYPE, PRG_LEVEL, LINE_TYPE, PROJECT_ID, PROJECT_ORG_ID, PROJECT_ORGANIZATION_ID, PROJECT_ELEMENT_ID, TIME_ID, PERIOD_TYPE_ID, CALENDAR_TYPE, RBS_AGGR_LEVEL, WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG, CURR_RECORD_TYPE_ID, CURRENCY_CODE, RBS_ELEMENT_ID, RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, PLAN_TYPE_CODE, RAW_COST, BRDN_COST, REVENUE, BILL_RAW_COST, BILL_BRDN_COST, BILL_LABOR_RAW_COST, BILL_LABOR_BRDN_COST, BILL_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST, ACT_REVENUE, ACT_LABOR_RAW_COST, ACT_EQUIP_RAW_COST, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_
..
..
PJP.PLAN_TYPE_ID, -4, PJP.PLAN_TYPE_ID, WBS_HDR.PLAN_TYPE_ID)), PJP.PLAN_TYPE_CODE ) PJP1_I, PA_PROJ_ELEM_VER_STRUCTURE SUP_FIN_VER, PA_PROJ_WORKPLAN_ATTR SUP_WPA WHERE PJP1_I.INSERT_FLAG = 'Y' AND PJP1_I.PROJECT_ID = SUP_FIN_VER.PROJECT_ID (+) AND PJP1_I.SUP_ID = SUP_FIN_VER.ELEMENT_VERSION_ID (+) AND 'STRUCTURE_WORKING' = SUP_FIN_VER.STATUS_CODE (+) AND PJP1_I.SUP_EMT_ID = SUP_WPA.PROJ_ELEMENT_ID (+) AND 'N' = SUP_WPA.WP_ENABLE_VERSION_FLAG (+) AND (PJP1_I.SUP_ID IS NULL OR (PJP1_I.SUP_ID IS NOT NULL AND (SUP_FIN_VER.PROJECT_ID IS NOT NULL OR SUP_WPA.PROJ_ELEMENT_ID IS NOT NULL)))

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 106 0.02 0.00 0 0 0 0
Execute 107 9574.62 11176.08 3844416 4645372 9297 7964
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 213 9574.64 11176.09 3844416 4645372 9297 7964

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