What Are Connection Pools?

The semantic model's physical layer contains at least one connection pool for each database. These connection pools are configured to enhance the execution of commands between the Oracle Analytics query engine and the semantic model's database data source.

A connection pool is automatically created when you import tables into a database object in the physical layer. You can add and configure multiple connection pools for each database. Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database. Oracle recommends that you create a dedicated connection pool for initialization blocks. See About Connection Pools for Initialization Blocks.

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. Test and consult with the database administrator to make sure the data source can handle the number of connections specified in the connection pool. Also, if the data sources have a charge back system based on the number of connections, you might want to limit the number of concurrent connections to keep the charge-back costs down.

In addition to the potential load and costs associated with the database resources, the Oracle Analytics query engine allocates shared memory for each connection upon server startup. This raises the number of connections and increases the Oracle Analytics query engine memory usage.