Oracle BI EE 10.1.3.4.1 – Persistent Connection Pool – Converting In-Memory BI Server Processing to Temporary Tables

One of the nice little features of the BI Server that is rarely used in a pure BI EE implementation is the Persistent Connection Pools. As you would probably know, BI Server adds one more layer of processing, if required, through its in-memory processing engine. For example, in the last blog entry i had shown how BI Server makes the in-memory stitch join to achieve standard non-transactional dimension member reporting. But sometimes too much of BI Server processing can slow down the queries considerably. Also, the most common issue with BI EE is, it can slow down the queries considerably when literals (when it goes above 1000) are used in the IN clause of the queries. To avoid such issues BI Server provides an option called Persistent Connection pool.

A persistent connection pool pushes down the processing to temporary tables rather than the BI Server memory. Unfortunately, currently one cannot exercise much control on how and when the temporary tables are created but when used correctly they can increase performance to an extent. This is very similar to Oracle Data Integrator where temporary tables can be leveraged to make joins across databases. For illustration, i shall be using a very simple report wherein we would be enabling the persistent connection pool.

Assume that we are reporting on the SH schema with a very simple repository design. We have a report which references another report in its filter as shown below.

Also for now lets assume that the database does not support IN_SUBQUERIES as shown below.

So, in order to generate the above report BI Server would fire 2 different queries shown below.

select distinct T2976.PROD_NAME as c1
from
     PRODUCTS T2976
where
( T2976.PROD_NAME in
('1.44MB External 3.5 Diskette', '128MB Memory Card',
'17 LCD w/built-in HDTV Tuner', '18 Flat Panel Graphics Monitor',
'256MB Memory Card', '3 1/2 Bulk diskettes, Box of 100',
'3 1/2 Bulk diskettes, Box of 50', '5MP Telephoto Digital Camera',
'64MB Memory Card', '8.3 Minitower Speaker') )

select T2976.PROD_NAME as c1,
     sum(T3013.AMOUNT_SOLD) as c2
from
     PRODUCTS T2976,
     SALES T3013
where  ( T2976.PROD_ID = T3013.PROD_ID and T2976.PROD_NAME in
('1.44MB External 3.5 Diskette', '128MB Memory Card',
'17 LCD w/built-in HDTV Tuner', '18 Flat Panel Graphics Monitor',
'256MB Memory Card', '3 1/2 Bulk diskettes, Box of 100',
'3 1/2 Bulk diskettes, Box of 50', '5MP Telephoto Digital Camera',
'64MB Memory Card', '8.3 Minitower Speaker') )
group by T2976.PROD_NAME
order by c1

So, in effect BI Server converts the sub-query into individual literals and then pushes it down to the where clause of the main query. Now, lets go to the database properties and enable persistent connection pooling.

When a persistent connection pool is enabled, BI Server assumes that there is no need for doing in-memory processing wherever possible. It will try to push down all the processing into the physical layer using temporary tables. The temporary tables get created and get populated based on the transaction boundary defined in the connection pool

As you see, one can specify the database, table-space related properties of the temporary table. Now lets go back and run the same report above. Now you would be noticing 3 different queries. The first query would be the sub-select query fired separately. The 2 query would be a CREATE TABLE query. This is where it gets interesting as this opens up other possibilities that we can put to good use. I would cover them later.

I would have liked a Global Temporary Table instead of a normal table for Oracle. But again the idea of doing this looks promising. Once the table is created, BI Server populates the table in-memory. And then it uses this temporary table in the sub-query of the 3rd query.

Unfortunately BI Server does not do a full clean up of the tables using Purge. DROP commands are not logged and if you are on 10g, you can still see the tables in the Recycle Bin after the command execution.

This is an interesting feature used extensively by the Marketing Segmentation Engine. Unfortunately there is not much control that we can exercise using this. DBA’s would not be happy at-least in the current context of table execution. Also, the isolation levels would have to be set correctly to avoid deadlock kind of scenarios. Having said that this can be put into good use in some other reporting use cases. I shall be covering them later.

in

Persist Connection Pool

Setting Up the Persist Connection Pool Property

---

A persist connection pool is a database property that is used for specific types of queries (typically used to support Marketing queries). 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 could 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 and supports unlimited IN list values and might result in improved performance.

NOTE:  In these situations, the user issuing the logical SQL needs to 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.

Example of Using 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 will be 20 KB. If there are 120 rows, there will be 6 batches with each batch size limited to 20 rows.

If you set the Transaction boundary field to 3, the server will commit 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 will only rollback 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. To make sure that the array-based insert runs successfully, it is recommended that you not set the transaction boundary greater than 10 and you set the buffer size to approximately 32 KB.

To assign a persist connection pool

1. In the Physical layer, double-click the database icon.
2. In the Database dialog box, click the General tab.
3. In the Persist Connection Pool area, click Set.
   If there is only one connection pool, it appears in the Persist Connection Pool field.
4. If there are multiple connection pools, in the Browse dialog box, select the appropriate connection pool, and then click OK.
   The selected connection pool name appears in the Persist connection pool field.
5. (Optional) To set write-back properties, click the Connection Pools tab.
6. In the connection pool list, double-click the connection pool.
7. In the Connection Pool dialog box, click the Write Back tab.
8. Complete the fields using Table 11 as a guide.
9. Click OK twice to save the persist connection pool.

To remove a persist connection pool

1. In the Physical layer, double-click the database icon.
2. In the Database dialog box, 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.

A persist connection pool is a database property that is used for specific types of queries (typically used to support Marketing queries).

In some queries, all of the logical query cannot be sent to the transactional database because that database might not support all of the functionsin the query. This issue might be solved by:

1. temporarily constructing a physical table in the database
2. 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.

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 theconnection pool must have the privileges to create DDL (Data Definition Language) and DML (Data Manipulation Language) in the database.