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.
