The P6 Professional application experiences intermittent performance delays when opening projects, sometimes hanging at 52%. The issue is not consistent and at times opening the same project completes very quickly.
This is caused by insufficient cursors being available to handle the P6 operations. When a user is opening a project and the SQL statements executed by the P6 application require cursors that are not yet available, the system must wait for them to become available.
The Oracle DBA should increase the session_cached_cursors and open_cursors parameters. This can be done from any tool that can execute SQL statements (such as SQL*Plus or SQL Developer), using the following statements:
The open_cursors parameter should be higher than the session_cached_cursors parameter.
These changes require a database restart to take effect.
alter system set session_cached_cursors= XXX scope=spfile;
alter system set open_cursors= XXX scope=spfile;
(Set XXX to the number of cursors for Oracle Database to make available.)
After making these changes, monitor the performance of the Open Project function; if the issue continues, gather a new AWR report to determine if cursor contention is still among the top causes.