Connection Pools

Connection Pools – What it is and Best Practices 

The Connection pool is an object in the Physical layer that describes access to the data source. It contains information about the connection between the Oracle BI Server and that data source.

The Physical layer in the Administration Tool contains at least one connection pool for each database. When you create the Physical layer by importing a schema for a data source, the connection pool is created automatically. You can configure multiple connection pools for a database.
Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database. For each connection pool, you must specify the maximum number of concurrent connections allowed. After this limit is reached, the connection request waits until a connection becomes available. Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool.

Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some tips to consider while defining connection pool.

• Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage
• Create a separate connection pool for execution of session variables
• Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables
• If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.