OBIEE - Connection Pool

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

This articles regroups all the articles and talk about the connection pool features of the BI Server such as:

10G 11G

To automate connection pool changes for use in a process such as production migration, consider using the Oracle BI Server XML API (11g).

For Report Queries, only the first authorized connection pool in each database is used to perform the queries.

Advertising

3 - Init Block / Session Variables

It is recommended that you create a dedicated connection pool for initialization blocks.

Another thing to check is whether or not the init blocks are using the same connection pool that the queries are using. If so, best practices dictate that init blocks (especially authentication) be given their own connection pool, otherwise you might have a situation where nobody can authenticate because the init blocks are waiting along with all the other queries for a connection pool thread.

When a user logs one these Init Blocks are run against the database to populate the session variables, each will use up a connection. If you are using the same connection pool as the one used for your data queries, this means the limit of Max connections will be more quickly reached, and would result in new users being unable to log on as the Init Block queries are queuing up, and cannot be run when the user logs on.

So we recommend that you create dedicated connection pools for your session variables, and associate these new connection pools to the Init Blocks that populate your session variables. You can initially set the Max connections on these connection pools to 64.

4 - Connection Pool Parameters

4.1 - Maximum connection

The rule of thumb is to use the number of simultaneous users times the number of requests on a dashboard. Also important is to know how much connections can be handled by the database, which is specific per database system (like DB2 has to start agents, Oracle needs to configure #connections in the init.ora

The Maximum Connections setting governs the number of active queries on the db. When the limit is reached, then the system waits for a connection to become available. As a single request can generate multiple db queries, it is possible for a maximum connections of 64 to be reached with less than 64 users. Also, a dashboard can have lots of requests so it is possible for a few users to occasionally hit the limit.

Max Connections Formula for Connection Pools The default value of 10 is usually too low (it will cause queuing) but setting it too high will eat up memory Approx 1024 kb of server memory is consumed per connection and the BI Server process may not even start.

As a “rule of thumb”, calculate it using the following theory:

  • At any one time, 10-20% of your users will be logged in
  • Of those users logged in, only 10-20% will actually be running queries
  • There are a maximum of X reports on any one Dashboard Page

So if you have 1000 users in total and a maximum of 4 reports per dashboard page:

  • Maximum Connections = 1000 x 0.2 x 0.2 x 4 = 160

Or in other word:

Maximum number of connections = 
  20% of concurrent users 
  * number of reports per dashboard.

The sum of all of the 'Maximum connections' in a repository cannot exceed 800.

Advertising

4.2 - Call interface

The call interface represents the driver that you use to connect to the data source. You have three methods:

  • XML. Xml is a build-in drivers to be able to connect/read XML files in a specific format.

OBIEE supports accessing:

  • ODBC 2.0 and 2.1 compliant databases/data sources for 8-bit character sets.
  • ODBC 3.5 compliant databases/data sources for UTF-16 (unicode) character sets.

4.3 - Execute Queries Asynchronously

10G only. “Execute Queries Asynchronously” parameter in the connection pool, this defines a parameter of our communication with the database. If this option is:

  • checked, the query is run asynchronously, and can be cancelled by the Analytics Server while the query is running, and using the same connection.
  • not checked, the query is run synchronously and the Database will not receive the cancel call until the query has completed.

Asynchronous mode is more efficient but it is less 'safe', as it is more complex to handle. It is useful to test your queries with the option unchecked (safer) ,as part of the troubleshooting.

4.4 - User and Password

In place of the id and password, you can use the intern variable :USER and :PASSWORD for pass through.

4.5 - Isolation Level

For ODBC and DB2 gateways only. Isolation Level :

  • Committed Read

Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection.

  • Dirty Read

0 Locking. Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.

  • Repeatable Read

Places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transaction.

  • Serialization

Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.

Advertising

4.6 - Multithreaded connections

When multithreaded connections is:

  • selected, Oracle BI Server terminates idle physical queries (threads).
  • not selected, one thread is tied to one database connection (number of threads = maximum connections).

Even if threads are idle, they consume memory.

The parameter DB_GATEWAY_THREAD_RANGE in the Server section of NQSConfig.ini establishes when the Oracle BI Server terminates idle threads. The lower number in the range is the number of threads that are kept open before the Oracle BI Server takes action. If the number of open threads exceeds the low point in the range, the Oracle BI Server terminates idle threads.

For example, if DB_GATEWAY_THREAD_RANGE is set to 40-200 and 75 threads are open, the Oracle BI Server terminates any idle threads.

4.7 - Shared logon

4.8 - DSN

The data source name is

4.9 - Parameters Supported

If this option is not selected, and the database features table supports "parameters", special code executes that allows the Oracle BI Server to push filters (or calculations) with parameters to the database. The Oracle BI Server does this by simulating parameter support within the gateway/adapter layer by sending extra SQLPrepare calls to the database.

4.10 - Enable Connection Pooling

The database connection remain open for the specified amount time for use by future query requests. Connection pooling saves the overhead of opening and closing a new connection for every query.

5 - Support

5.1 - Security

If you have this error :

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. 
[nQSError: 19007] The user does not have sufficient privilege to access the database ORCL. (HY000)
SQL Issued: SELECT B.DATA saw_0 FROM SH ORDER BY saw_0

It comes from the OBIEE connection pools security parameters from the database. Suppose that we have only one connection pool for a database, the image below show that nobody have been granted to access the connection excepted the Administrator :

6 - Documentation / Reference