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.
See Setting Connection Pool Properties in the Write Back Tab.
Use these steps to remove the Persist Connection Pool property.
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.