Tuning Oracle adapter database connections

The Oracle adapter -CACHE option defines a unique database connection for each SELECT statement. This connection is reserved solely for cache-based queries and is re-used only if another SELECT query matches identically. Therefore, the total number of Oracle connections that an existing map requires to run can increase when you add the -CACHE option to the adapter command.

For example, a map that doesn't use the -CACHE option uses one database connection for three unique SELECT queries. When you add the -CACHE option to each SELECT statement, the map requires a database connection for each unique SELECT statement (a total of three connections). Two instances of this map that run in parallel require six database connections (instead of two connections without the -CACHE option).

Opening a database connection adds to the total time to complete the query, but keeping a connection open consumes memory on the server. You need to tune the number of open database connections to maximize connection reuse and minimize the number of open but unused connections.

You can use the [Connection Manager] section of the install_dir/dtx.ini configuration file to limit the number of idle database connections. The SLimDB= keyword limits the number of idle database connections, but that limit can be exceeded during peak load. The optimal number of database connections is affected by the number of connections that each map requires and the number of running instances of each map.

To count the number of connections that a map requires, enable the -AUDIT adapter command with the global option (-AG) on the first database operation in the map. The -AG command audits all subsequent database operations in the map.

After a query runs and the result is cached, the total elapsed time of subsequent identical queries is reduced, whether the queries are in the same map or in other maps.