Configuring Proxy Resident Connection Pooling in Oracle Connection Manager in Traffic Director Mode

10.2.7 Configuring Proxy Resident Connection Pooling in Oracle Connection Manager in Traffic Director Mode

Oracle Connection Manager in Traffic Director Mode (CMAN-TDM) supports Proxy Resident Connection Pooling (PRCP). You can configure PRCP either for each database service (default setting) or for an entire PDB.

Optionally, you can enable implicit connection pooling with PRCP.

Configure PRCP in either per-service or per-PDB mode.

Per-service PRCP:

This is the default PRCP configuration.

The <session_pool> parameters in the oraaccess.xml file (available in the TNS_ADMIN directory of the CMAN-TDM server) enable you to configure per-service PRCP and specify the associated connection parameters.

You can specify the following <session_pool> parameters in the <default_parameters> section or in the <config_descriptions> section. When specified in the <default_parameters> section, the setting applies to all connection pools in the application.

Parameter	Description
`<enable>`	You must set `<enable>` to `true` (default setting) to make the session pool configuration effective. This is a mandatory parameter, that means, if `<session_pool>` is configured, then `<enable>` must also be configured.
`<min_size>`	Minimum number of connections in the pool. The default value is `0`. CMAN-TDM is a heterogeneous pool scenario, so all other values are ignored.
`<max_size>`	Maximum number of connections in the pool. This is a mandatory parameter, that means, if `<session_pool>` is configured, then `<max_size>` must also be configured. The default value is `0`.
`<increment>`	Amount of increase in the number of connections in the pool as the pool expands. The default value is `1`.
`<inactivity_timeout>`	Maximum time in seconds for which a connection stays idle in the pool, after which it is terminated. The default value is `0`. It means that there is no limit.
`<max_use_session>`	Maximum number of times a connection can be retrieved and released to the pool. The default value is `0`. It means that there is no limit.
`<max_life_time_session>`	Time, in seconds, a connection must stay after it has been created in the pool. The default value is `0`. It means that there is no limit.

The oraaccess.xml file allows you to configure a connection pool for each required connection service.

The following example shows two groups of connection parameters associated with its respective config_alias, the sales_config and the hr_config, where each connection string that the application uses is mapped with its respective config_alias, thus providing two proxy resident connection pools.

<oraaccess xmlns="http://xmlns.example.com/oci/oraaccess"
            xmlns:oci="http://xmlns.example.com/oci/oraaccess"
            schemaLocation="http://xmlns.example.com/oci/oraaccess
            http://xmlns.example.com/oci/oraaccess.xsd">
  <default_parameters>
  </default_parameters>
    <!-- 
         Create configuration descriptions, which are  
         groups of connection parameters associated with
         a config_alias. 
    --> 
  <config_descriptions>
    <config_description>
      <config_alias> sales_config </config_alias> 
      <parameters>
         <session_pool>
            <enable>true</enable>
            <min_size> 10 </min_size>
            <max_size> 100 </max_size>
            <increment> 5 </increment>
         </session_pool>
      </parameters>
    </config_description>
    <config_description>
      <config_alias> hr_config </config_alias> 
      <parameters>
         <session_pool>
           <enable>true</enable>       
          <max_size> 10 </max_size>
         </session_pool>
      </parameters>
    </config_description>
  </config_descriptions>
  <!--   
         Now map the connection string used by the application 
         with a config_alias.  
  --> 
  <connection_configs>
    <connection_config>
      <connection_string>sales.us.example.com</connection_string> 
      <config_alias>sales_config</config_alias> 
    </connection_config>
    <connection_config>
      <connection_string>hr.us.example.com</connection_string> 
      <config_alias>hr_config</config_alias> 
    </connection_config>
  </connection_configs>
</oraaccess>

Per-PDB PRCP:

Instead of creating dedicated pools for all requested services, you can configure a single multi-service PDB pool to support connections across all the services registered with that PDB. Based on the Oracle Compute Unit (OCPU) count allocated to your PDB and the specified connection factor, PRCP dynamically computes the maximum pool size.
1. On the database server, set the sqlnet.ora parameter TCP.ALLOWED_PROXIES to specify one or more CMAN instances (IP addresses or host names) that you want to allow for fetching the OCPU count.
  For example:
```
TCP.ALLOWED_PROXIES=(10.1.1.1/24,cmanhost1.example.com)
```
2. On the CMAN-TDM host, set the cman.ora parameter TDM_PERPDB_PRCP_CONNFACTOR to the required connection factor value.
  
  Any value equal to or greater than 1 enables per-PDB PRCP.
  For example:
```
TDM_PERPDB_PRCP_CONNFACTOR=10
```
  Note:
  Ensure that you set the connection factor value within the maximum connections limit defined by the cman.ora parameter MAX_CONNECTIONS.
3. (Optional) To monitor the behavior of these pools, you can query the dynamic database view V$TDM_STATS. To do so, first enable statistics upload by setting the cman.ora parameter TDM_STATS_FREQUENCY to the required time interval value.
  
  Any value equal to or greater than 1 (up to the defined maximum value) enables statistics upload.
  
  For example:
```
TDM_STATS_FREQUENCY=300
```
  Based on the specified frequency, CMAN-TDM fetches the data from each CMAN-TDM gateway and uploads to PDB.
4. (Optional) You can specify the associated connection parameters in the <default_parameters> section of the oraaccess.xml file.
  
  For example:
```
<default_parameters>
  <events>true</events>  
  <session_pool>
    <enable>true</enable>
    <max_size> 5 </max_size>    
    <max_life_time_session> 50 </max_life_time_session>
    <max_use_session> 5 </max_use_session>
    <inactivity_timeout> 5 </inactivity_timeout>
  </session_pool>
</default_parameters>
```
  If you specify the <default_parameters> section with <enable> set to true, then all the specified parameters are effective except for the <max_size> parameter. PRCP automatically derives the maximum size of a pool (using TDM_PERPDB_PRCP_CONNFACTOR and OCPU count). This derived maximum size value overrides the <session_pool> <max_size> parameter configured in the oraaccess.xml file.
  
  If you do not specify the <default_parameters> section or if you specify it with <enable> set to false, then all other parameters in the section are ignored.
  
  The config_alias, which maps each service name used by the application to the connection parameters, does not function because the pool is not limited to a single service in the per-PDB PRCP mode.

(Optional) To enable implicit connection pooling with either per-service or per-PDB PRCP, on the client side, set the POOL_BOUNDARY parameter in the tnsnames.ora file, Easy Connect syntax, or directly as part of the command-line connect string.
Implicit connection pooling automatically performs session mapping or unmapping based on the session state. This can help maximize the pooled server usage and reduce the server resource usage.
Specify one of these time boundaries to release the session back to the PRCP pool:
- STATEMENT: To release the session when the session is implicitly stateless.
  For example:
  inst1= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (SERVER=POOLED) (POOL_BOUNDARY=STATEMENT)) )
- TRANSACTION: To release the session when a transaction ends implicitly or explicitly, or when a transaction is not available and the session is stateless.
  For example:
  inst1= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (SERVER=POOLED) (POOL_BOUNDARY=TRANSACTION)) )
The release to the pool closes any active cursors, temporary tables, and temporary LOBs.

Note:
You must set the POOL_BOUNDARY parameter along with the SERVER=POOLED setting. Otherwise, implicit connection pooling is disabled and the POOL_BOUNDARY directive is ignored.