The connection pools create and open database connections when they are first needed. When you start the P6 EPPM server, it will allocate one or two connections for initialization routines. The server will allocate and open more connections when the pool cannot satisfy requests from the current pool. You can set the connection pool settings for:
- Regular Used for the regular connection pool. Regular is the most used connection pool and has 90%+ of database access.
- Long Running Used for the long running connection pool. Use this connection pool for services that will run for a long time.
- Transactional Used for the transactional connection pool. Use this connection pool when you have several types of objects saved at one time.
Note: Gantt applets will use the Regular pool when loading data and the Transactional pool when creating or updating data.
A P6 EPPM application operation requires a database connection, so it asks the connection pool for a database connection. If the connection pool contains a connection that is not currently leased, it returns that connection. If it doesn't have a connection available it will check to see if its current allocated count is below 50 (which is the default in the Maximum Connections setting). If the count is below 50, it will create a new database connection and open it, put it in the pool, and return it to the caller. The caller uses the connection, then releases it when they end the session.
If the pool could not allocate a connection because all 50 connections were leased, it will keep checking for the next 30 seconds (which is the default in the Lease Request Wait Timeout setting). If a connection becomes available within that 30 seconds, it will return that connection. If a connection does not become available, it will log the following error: “Error: Lease request timeout while waiting for a connection to become available. Database <dbname>” and the caller will have to try again at a later time.
To set your connection pool settings:
Notes:
- The defaults will provide optimal performance in most environments. Change them only if you are certain a change is needed. Contact My Oracle Support for more information.
- Open the Primavera P6 Administrator.
- In the Configurations drop-down list, select your configuration.
- In the sidebar select Database.
- Select your Instance from the drop-down list.
- Expand Connection Pool(aaa).
Note: Changes to these settings require you to restart the P6 server.
- Select Trace SQL to trace all SQL sent to the database.
The default is unselected.
- Select Renewable Leases if you want connection leases renewed when database statements are completed within the MaxLeaseDuration time period. When selected, the code can hold onto the connection as long as it needs, provided SQL statements finish within the MaxLeaseDuration period. When selected, the connection is revoked if no SQL statements are issued within the MaxLeaseDuration period or if one statement takes longer to run than that period.
Leave this field blank if you want each connection to be leased only for the MaxLeaseDuration period.
The default for Connection Pool (Regular) is false. The default for Long Running and Transactional is true.
- In the Resize Rate field, enter the timeout period when the system will close all of its unused connections. For example, if the resize rate is 4 minutes, then every 4 minutes the connection pool will attempt to close all of its unused connections.
The default value is 4m.
Enter a value between 4m and 12h.
- In the Maintenance Frequency field, enter how often maintenance will run to ensure leases have not exceeded the maximum duration.
The default value is 1m.
Enter a value between 10s and 1h.
- In the Lease Request Wait Timeout field, enter the amount of time a request for a database connection will wait before timing out.
If a request for a database connection times out, the user can reload the page and attempt to connect to the database again.
The default value is 30s.
Enter a value between 5s and 2h.
- In the Maximum Connections field, enter the maximum number of connections the server will have to the database.
The default for Regular and Transactional is 50 and the default for Long Running is 100.
Enter a value between 5 and 15000.
- In the Fetch Size field, enter how many rows to pull from the database and send to the application server.
The default value is 120.
- In the Renewable Free Limit field, enter the minimum number of connections that leases need to be renewed.
The default value is 3.
Enter a value between 3 and 5.
- In the Maximum Lease Durations field, enter the maximum time a database connection can be leased before it is revoked.
The default for Regular is 2m, with a range of 5s and 4h.
The default for Long Running is 30m, with a range of 5s and 6h.
Note: During the summarization process, queries may take a long time to run if the project has a high number of WBS nodes. If you receive timeout errors, set your Long Running maximum lease duration to at least 30 minutes.
The default for Transactional is 30m, with a range of 5s and 6h.
- Select Trace SQL to trace all SQL sent to the database.