Work with Connection Pools

This topic provides information about how to create and modify a database's connection pools.

Topics:

• What Are Connection Pools?

• About Connection Pools for Initialization Blocks

• Set a Connection Pool's General Properties

• Set a Connection Pool's Connection Property

• Add Connection Scripts to a Connection Pool

• About Setting the Bulk Insert Buffer Size and Transaction Boundary Settings

• Set up Write Back in a Connection Pool

• Set a Connection Pool's Permissions

What Are Connection Pools?

The semantic model's physical layer contains at least one connection pool for each database. These connection pools are configured to enhance the execution of commands between the Oracle Analytics query engine and the semantic model's database data source.

A connection pool is automatically created when you import tables into a database object in the physical layer. You can add and configure multiple connection pools for each database. Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database. Oracle recommends that you create a dedicated connection pool for initialization blocks. See About Connection Pools for Initialization Blocks.

For each connection pool, you must specify the maximum number of concurrent connections allowed. After this limit is reached, the connection request waits until a connection becomes available.

Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. Test and consult with the database administrator to make sure the data source can handle the number of connections specified in the connection pool. Also, if the data sources have a charge back system based on the number of connections, you might want to limit the number of concurrent connections to keep the charge-back costs down.

In addition to the potential load and costs associated with the database resources, the Oracle Analytics query engine allocates shared memory for each connection upon server startup. This raises the number of connections and increases the Oracle Analytics query engine memory usage.

About Connection Pools for Initialization Blocks

You should create a dedicated connection pool for initialization blocks. Don't use the connection pools that you create for initialization blocks for data queries.

You should isolate the connections pools for different types of initialization blocks. By isolating the connection pools, you can ensure that authentication and login-specific initialization blocks don't slow down the login process. The following types of initialization blocks should have separate connection pools:

• All initialization blocks that set session variables.

• All initialization blocks that set semantic model variables. Run initialization blocks that set variables using credentials with administrator privileges.

  Be aware of the number of these initialization blocks, their scheduled refresh rate, and when they're scheduled to run. It would take an extreme case for this scenario to affect resources. For example, refresh rates set in minutes, greater than 15 initialization blocks that refresh concurrently, and a situation in which either of these scenarios could occur during prime user access time frames.

It's more efficient and less resource intensive to set as many variables as possible in an initialization block. For example, suppose you have one initialization block that contains five variables. In this case, the initialization string makes one call to the back-end tables. Creating five initialization blocks that contain one variable each results in five calls to the back-end tables.

If an initialization block fails for a particular connection pool, no more initialization blocks using that connection pool are processed. Instead, the connection pool is denied and subsequent initialization blocks for that connection pool are skipped. This behavior ensures that Oracle Analytics continues to work, even when a connection pool has a large number of associated initialization blocks or variables.

If this issue occurs, a message similar to the following is displayed in the server log:

[OracleBIServerComponent] [ERROR:1] [43143] Blacklisted connection pool name_of_connection_pool

If you see this error, check the initialization blocks for the given connection pool to ensure they're correct.

Connection Pool General Properties

The topic describes the connection pool properties in the Connection Pool's tab General pane. These properties are common among most connection types.

Use the information in this topic to help you create or modify a connection pool. See Set a Connection Pool's General Properties.

Property	Description
Connection	Displays the connections available to semantic models. A connection's System Connection property must be selected for the connection to display in this list. See Manage Connections to Data Sources. Oracle Analytics doesn't always automatically assign the connection pool's connection, so sometimes you must manually assign one. You're not able to preview a physical table's data until it's database's connection pool connection is assigned.
Remote Connection	Identifies if the database connection uses remote data connectivity. This field isn't automatically selected if the database uses remote data connectivity. You must set this field manually. If the database uses remote data connectivity and this field isn't selected then you'll receive an error when you run consistency check.
Max Connections	Specifies the maximum number of connections allowed for this connection pool. The default is 10. You can determined the value by the database make and model and the configuration of the hardware for the computer where the database runs, and the number of concurrent users who require access. For deployments with Oracle BI Interactive Dashboards pages, consider estimating this value at 10% to 20% of the number of simultaneous users multiplied by the number of requests on a dashboard. You can adjust the number based on usage. Define the total number of all connections in the semantic model to less than 800. To estimate the maximum connections needed for a connection pool dedicated to an initialization block, you might use the number of users concurrently logged on during initialization block processing.
Timeout	Specifies the amount and increment of time that a connection remains open after a request completes. During this time, new requests use this connection rather than open a new one up to the number specified for the maximum connections. The time is reset after each completed connection request.
Isolation Level	Specifies the value sets the transaction isolation level on each connection to the back-end database. For ODBC gateways only. Controls the default transaction locking behavior for all statements issued by a connection. You can only set one at a time. It remains set for that connection until it's explicitly changed. The options are: Dirty read - Implements dirty read, isolation level 0 locking. This is the least restrictive isolation level. When this option is set, it's possible to read uncommitted or dirty data, change values in the data, and have rows appear or disappear in the data set before the end of the transaction. Dirty data is data to clean before running a query to obtain correct results. For example, duplicate records, records with inconsistent naming conventions, or records with incompatible data types. Committed read - Specifies that shared locks are held while the data is read to avoid dirty reads. You can change the data before the end of the transaction, resulting in non-repeatable reads or phantom data. Repeatable read - Places locks on all data that's used in a query, preventing other users from updating the data. You can insert new phantom rows into the data set by another user and are included in later reads in the current transaction. Serializable - Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only if necessary.
Require fully qualified table names	When selected, specifies that all requests sent from the connection pool use fully qualified names to query the underlying database. Select this option if the database or database configuration requires fully qualified table names. This option isn't available for some connection types. The fully qualified names are based on the physical object names in the semantic model. If you're querying the same tables that the physical layer metadata was imported from, then you can safely select this option. If you've migrated your semantic model from one physical database to another physical database that has different database and schema names, the fully qualified names are invalid in the newly migrated database. In this case, if you don't select this option, the queries succeed against the new database objects. For some connections, fully qualified names are a safer because they guarantee that the queries are directed to the desired tables in the desired database. For example, if the RDBMS supports an original database concept, a query against a table named Customer first looks for that table in the original database, and then looks for it in the specified database. If the table named Customer exists in the original database, that table is queried, not the table named Customer in the specified database. You might need to select this option when you're using an Oracle Database and you're accessing the database with a user that isn't the owner of the schema containing the tables. When the Oracle Database interprets table names in SQL, it assumes that the user that made the query is the owner if the table name isn't fully qualified in the query. This can result in an incorrect qualified name. For example, if the user SAMPLE creates a table called CUSTOMER, the fully qualified table name is SAMPLE.CUSTOMER. When the SAMPLE user references the CUSTOMER table in a query, the Oracle Database assumes the fully qualified table name is SAMPLE.CUSTOMER, and the access is successful. However, if the JANEDOE user references the CUSTOMER table in a query, the Oracle Database assumes the fully qualified table name is JANEDOE.CUSTOMER, and a Table or view not found error can result. To enable access for JANEDOE, you must select Require fully qualified table names in the connection pool so that the Oracle Analytics query engine specifies SAMPLE.CUSTOMER in all queries.
Use multithreaded connections	When selected, specifies that the Oracle Analytics query engine terminates idle physical queries (threads). When not selected, one thread is tied to one database connection, number of threads = maximum connections. Even if threads are idle, they consume memory.
Parameters supported	When selected, indicates that the database features table supports parameters and special code runs that allows the Oracle Analytics query engine to push filters (or calculations) with parameters to the database. The Oracle Analytics query engine does this by simulating parameter support within the gateway/adapter layer by sending extra SQLPrepare calls to the database.
Enable connection pooling	Allows a single database connection to remain open for the specified time for use by future query requests. Connection pooling saves the overhead of opening and closing a new connection for every query. If you don't select this option, each query sent to the database opens a new connection.

Set a Connection Pool's General Properties

General properties include Max Connections, Timeout, Isolation Level, and so on.

For description of the general properties and how to set them, see Connection Pool General Properties.

The properties listed in the General tab vary according to the data source type.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer and locate and double-click a database.
4. In the database's tab, click Connection Pools.
5. In the connection pools list table, click a connection pool to select it and then click Detail view to open the Properties pane.
6. Go to the General section of the Properties pane and modify the connection pool's properties.
7. Click Save.

Set a Connection Pool's Connection Property

A connection pool's connection must be correctly assigned before you can preview a physical table's data.

When you import a .rpd or .zip file or load the deployed semantic model to create a semantic model, Oracle Analytics doesn't always automatically assign the connection pool's connection. In such cases you must manually assign one.

The Connections field displays the data source connections that you can use with semantic models. You must create or have been given access to the needed semantic model's data source connection. See Manage Connections to Data Sources.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer and locate and double-click a database.
4. In the database, click Connection Pools and click Detail view.
5. Go to the connections pool list and click a connection pool to display its details.
6. In the connection pool's details, scroll to the Connection field, click it, and select a connection.
7. Click Save.

Add Connection Scripts to a Connection Pool

You can add one or more connection scripts and set them to run before the connection is established, before a query is run, after a query is run, or after the connection is disconnected.

For example, you can create a connection script that on connect inserts the name of the user and the connection time into a table.

Connection scripts can contain any commands accepted by the database, such as a command to turn on quoted identifiers. This enables mainframe environments to maintain security in one central location.

Because the connection script is sent directly to the data source, you must write the script in native SQL. Don't write the script in Oracle Analytics Logical SQL because the data source won't understand it.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer and locate and double-click a database.
4. In the database's tab, click Connection Pools.
5. In the connection pools list table, click a connection pool to select it and then click Detail view to open the properties pane.
6. Scroll to Connection Scripts, click Add Script, and select when to run the script.
   A subsection is added for the selected connection script type.
7. Write the script using native SQL or a language that the data source understands.
8. Optional: Click Enable so that the script runs before the connection is established.
9. Click Save.

About Setting the Bulk Insert Buffer Size and Transaction Boundary Settings

For write back, 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.

Set up Write Back in a Connection Pool

A connection pool's write back requirements include a temporary table and bulk insert properties.

See About Setting the Bulk Insert Buffer Size and Transaction Boundary Settings.

The table describes the properties in the Write Back tab of the Connection Pool dialog.

Property	Description
Database supports unicode	Select when the columns are of an explicit Unicode data type, such as NCHAR, in a Unicode database. This makes sure that the binding is correct and that data is inserted correctly. Different database vendors provide different character data types and different levels of Unicode support. Use these guidelines to determine when to set this option: On a database where CHAR data type supports Unicode and there isn't a separate NCHAR data type, don't select this option. On a database where NCHAR data type is available, it's recommended to select this option. On a database where CHAR and NCHAR data type are configured to support Unicode, it's option to select this option. Unicode and non-Unicode data types can't coexist in a single non-Unicode database. For example, mixing the CHAR and NCHAR data types in a single non-Unicode database environment isn't supported.
Temporary Table - Prefix	Enter the first two characters in the temporary table name that the Oracle Analytics query engine creates.
Temporary Table - Owner	Enter the table owner name used to qualify a temporary table name in a SQL statement. For example, owner.tablename. If left blank, the user name specified in the writeable connection pool is used to qualify the table name.
Bulk Insert - Buffer size (KB)	Enter the maximum number of bytes inserted into a database table. For optimum performance, set this parameter to 10240.
Bulk Insert - Transaction boundary	Enter the batch size for an insert in a database table. For optimum performance, set this parameter to 1000.

Use these steps to specify your database's write back properties.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer and locate and double-click a database.
4. In the database's tab, click Connection Pools.
5. In the connection pools list table, click a connection pool to select it and then click Detail view to open the properties pane.
6. Scroll to Write Back and specify the write back properties.
7. Click Save.

Set a Connection Pool's Permissions

A connection pool's permissions specify which application roles have read-write, read-only, and no access permissions to use the connection pool. For example, you can set up the users in the DV Content Author application role to have their own connection pool.

By default, all roles have read-only access to the connection pool. Add applications roles and assign permissions to limit who can use the connection pool.

Don't use connection pool permissions to determine data access security. For example, connection pool permissions don't protect cache entries.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer and locate and double-click a database.
4. In the database's tab, click Connection Pools.
5. In the connection pools list table, click a connection pool to select it and then click Detail view to open the properties pane.
6. Scroll to Permissions.
7. Click in the search field and type the name of a role, or enter * (an asterisk) to see the full list of roles. From the results list, click the role that you want to add to the permissions table.
8. Specify the role's permission.
9. Click Save.