My Oracle Support Banner

R12 Performance Issue for Pa_project_pub.update_project API When Updating or Adding Tasks for a Project with a Large Number of Tasks (Doc ID 2501958.1)

Last updated on OCTOBER 31, 2019

Applies to:

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

Symptoms

When using the pa_project_pub.update_project API to update or add tasks for a project with a large number of tasks (5000 to 7000), the API can take up to 15 hours to complete (number of tasks = 3571).

Expensive query is:

UPDATE PA_PROJ_ELEMENT_VERSIONS SET DISPLAY_SEQUENCE = DISPLAY_SEQUENCE + 1
WHERE
DISPLAY_SEQUENCE > :B3 AND ELEMENT_VERSION_ID <> :B2 AND
 PARENT_STRUCTURE_VERSION_ID = :B1 AND OBJECT_TYPE = 'PA_TASKS'

Tried:

1. Added index: $alter index pa.PA_PROJ_ELEMENT_VERSIONS_N2 rebuild per 'Performance Issue When Downloading Budget To Excel For Financial Plan Using WebAdi (Doc ID 2021019.1)'.
2. Applied patch 18663578:R12.PJF.B Performance Problems When moving Tasks in Workplan (Doc ID 1674226.1)


After applying the patch and rebuilding the index the total execution time differs by 3-4 minutes when updating 620 tasks.  Users believe that this is not enough, because download volumes on the productioninstance reach 3500 tasks and work time is 15 hours.

Changes

 

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
Changes
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.