Setting Up Persist Connection Pools

A persist connection pool is a database property used for specific types of queries such as queries used to support Marketing.

In some queries, all of the logical query cannot be sent to the transactional database because that database might not support all of the functions in the query. This issue might be solved by temporarily constructing a physical table in the database and rewriting the Oracle BI Server query to reference the new temporary physical table.

You can use the persist connection pool in the following situations:

• Populate stored procedures. Use to rewrite the Logical SQL result set to a managed table. Typically used by Oracle's Siebel Marketing Server to write segmentation cache result sets.

• Perform a generalized subquery. Stores a nonfunction subquery in a temporary table, and then rewrites the original subquery result against this table. Reduces data movement between the Oracle BI Server and the database, supports unlimited IN list values, and might result in improved performance.

  In these situations, the user issuing the Logical SQL query must have been granted the Populate privilege on the target database.

The persist connection pool functionality designates a connection pool with write-back capabilities for processing this type of query. You can assign one connection pool in a single database as a persist connection pool. If this functionality is enabled, the user name specified in the connection pool must have the privileges to create DDL (Data Definition Language) and DML (Data Manipulation Language) in the database.

To assign a persist connection pool:

1. In the Physical layer of the Administration Tool, double-click the database object for which you want to assign a persist connection pool.
2. In the Database dialog, click the General tab.
3. In the Persist connection pool area, click Clear.

   The database name is replaced by not assigned in the Persist connection pool field.

4. If there are multiple connection pools, in the Browse dialog, select the appropriate connection pool, and then click OK.
   The selected connection pool name appears in the Persist connection pool field.
5. (Optional) click the Connection Pools tab to set write-back properties.
6. In the connection pool list, double-click the connection pool.
7. In the Connection Pool dialog, click the Write Back tab.
   See Setting Connection Pool Properties in the Write Back Tab for more information.
8. Click OK, then click OK again to save the persist connection pool.

Removing the Persist Connection Pool Property

Use these steps to remove the Persist Connection Pool property.

To remove a persist connection pool:

1. In the Physical layer of the Administration Tool, double-click the database object that contains the persist connection pool you want to remove.
2. In the Database dialog, click the General tab
3. In the Persist connection pool area, click Clear.
   The database name is replaced by not assigned in the Persist connection pool field.
4. Click OK.

About Setting the Buffer Size and Transaction Boundary

If each row size in a result set is 1 KB and the buffer size is 20 KB, then the maximum array size is 20 KB.

If there are 120 rows, there are 6 batches with each batch size limited to 20 rows.

If you set Transaction boundary to 3, the server commits twice. The first time, the server commits after row 60 (3 * 20). The second time, the server commits after row 120. If there is a failure when the server commits, the server only rolls back the current transaction. For example, if there are two commits and the first commit succeeds but the second commit fails, the server only rolls back the second commit.

For optimum performance, consider setting the buffer size to 128 and the transaction boundary to 1000.