You can modify several Oracle Database settings to improve the performance of your P6 Professional database. Run the scripts as described below after you create the database.
Grant access to the V_$TRANSACTION view
V_$TRANSACTION is a system view in Oracle that lists the active transactions in the system.
GET_SAFETY DATE (a procedure in the Project Management schema) accesses this view to get the oldest start time of an active transaction. If the schema owner does not have privileges to this view, then it returns a safety date using the USESSION table. V_$Transaction tunes performance during a refresh action. USESSION records the login time of the logged in user, whereas data in the V_$Transaction view is recorded at a system level. The logged in user could be logged in for more than an hour (as seen from the USESSION table), but the V_$Transaction view has the current transaction datetime, regardless of the time the user logged in.
Note: Access to the V_$TRANSACTION view was built into the procedure to tune performance with refreshing operations; however, third-party functions could impact performance with database refresh operations that use an older time in the V_$TRANSACTION view.
To grant access to this view, connect to Oracle as SYS. Go to the \database\scripts\common folder and run the run_as_sys.sql script. Alternatively, run the following GRANT statement:
grant select on v_$transaction to admuser;
Gather statistics for cost-based optimization
Oracle 10g and later supports only cost-based optimization, which relies on accurate statistics to determine the optimal access path for a query. To gather the appropriate statistics for the optimizer, which improves database performance, go to the \database\scripts\common folder and run the orpm_stats_gather.sql script.