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.
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
DISPLAY_SEQUENCE > :B3 AND ELEMENT_VERSION_ID <> :B2 AND
PARENT_STRUCTURE_VERSION_ID = :B1 AND OBJECT_TYPE = 'PA_TASKS'
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.
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