onnection pooling in OBIEE
hello world,
connection pooling, the term in its simplest sense for any novice developer suggests "pool of connections", which is true. The "pool of connections" doesn't mean there exists multiple connections to the database. It should be comprehended as concurrent connections within A single database connection. This connection pool object is governed by a set of options which controls its 'demeanor'.
Following are the options on general tab:
e.g. as recommended by oracle multiple connection pools exist for repository variable initialization blocks, session variable initialization blocks (mostly used to authenticate users..) etc. Delete unused connections, not deleting them increases server memory. Excerpt from OBIEE help: "In addition to the potential load and costs associated with the database resources, the Oracle BI Server allocates shared memory for each connection upon server start up. This raises the number of connections and increases Oracle BI Server memory usage."
as a test, create some n (=50) connection pools, and check your task manager for nqsserver.exe on start up.
2. Call Interface: specify the interface through which you want the application to communicate with underlying database.
To communicate with oracle databases select either odbc or native call interfaces (oci 8i/9i, oci 10g/11g). caveat 1, when a odbc is selected "Data source name:" is the name of the System DSN in odbc data source administrator. caveat 2, when a oci call interface is selected "Data source name:" is the TNS Name in the tnsnames.ora file.
3. Maximum Connections: this parameter specifies maximum concurrent connections - a database can handle. how many maximum concurrent connections are possible? this is an abstruse question. This is a multi-part question. As a developer one should analyze the average number of users, reports fired from a dashboard page including any complex prompts, # dashboard pages... From the database perspective system dba determines how many concurrent connections can be possible. Again finding answer to this question depends upon what types (simple to complex) queries are being fired, what tables are accessed, indexes being accessed etc...
overall, Maximum Connections parameter must be defined based on BI developer and system dba's input. No astronomical number must be used which might have adverse effects.
4. Required fully qualified table names: When checked the SQL issued to the back end data bases contains "shema.tablename" format. hence changing the schema, table, or column names in the physical layers is not advisable. If renamed, here is a sample error
with sql server as back end data base:
5. shared logon: check shared logon to authenticate users based on the user name and password provided. If the shared logon is omitted application uses user name and password specified in the systems dsn.
6. Enable Connection Pooling: the parameter specifies the maximum wait time before a request reuses the database connection. If all the Maximum connections specified are being used, the new request either waits for a existing request to complete or looks for another connection pool (if specified).
7. Use multithreaded connections: a good excerpt from the help - "When the check box is select ed, Oracle BI Server 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.
8. Execute queries asynchronously: check if database supports asynchronous queries. Asynchronous queries helps boost the response time of an application.
9. Parameters Supported: check if the sql issued to the database employs any parameters. by default it's checked.
10. Isolation levels: In my perspective the Isolation levels are useful when there exists concurrent reads and writes - which implies a write back should exist.
connection pooling, the term in its simplest sense for any novice developer suggests "pool of connections", which is true. The "pool of connections" doesn't mean there exists multiple connections to the database. It should be comprehended as concurrent connections within A single database connection. This connection pool object is governed by a set of options which controls its 'demeanor'.
Following are the options on general tab:
- Connection Name and Permissions...
- Call Interface
- Maximum Connections.
- Required fully qualified table names
- Shared Logon
- Enable Connection pooling.
- Use multithreaded connections.
- Execute queries asynchronously.
- Parameters supported.
- Isolation Level.
e.g. as recommended by oracle multiple connection pools exist for repository variable initialization blocks, session variable initialization blocks (mostly used to authenticate users..) etc. Delete unused connections, not deleting them increases server memory. Excerpt from OBIEE help: "In addition to the potential load and costs associated with the database resources, the Oracle BI Server allocates shared memory for each connection upon server start up. This raises the number of connections and increases Oracle BI Server memory usage."
as a test, create some n (=50) connection pools, and check your task manager for nqsserver.exe on start up.
2. Call Interface: specify the interface through which you want the application to communicate with underlying database.
To communicate with oracle databases select either odbc or native call interfaces (oci 8i/9i, oci 10g/11g). caveat 1, when a odbc is selected "Data source name:" is the name of the System DSN in odbc data source administrator. caveat 2, when a oci call interface is selected "Data source name:" is the TNS Name in the tnsnames.ora file.
3. Maximum Connections: this parameter specifies maximum concurrent connections - a database can handle. how many maximum concurrent connections are possible? this is an abstruse question. This is a multi-part question. As a developer one should analyze the average number of users, reports fired from a dashboard page including any complex prompts, # dashboard pages... From the database perspective system dba determines how many concurrent connections can be possible. Again finding answer to this question depends upon what types (simple to complex) queries are being fired, what tables are accessed, indexes being accessed etc...
overall, Maximum Connections parameter must be defined based on BI developer and system dba's input. No astronomical number must be used which might have adverse effects.
4. Required fully qualified table names: When checked the SQL issued to the back end data bases contains "shema.tablename" format. hence changing the schema, table, or column names in the physical layers is not advisable. If renamed, here is a sample error
with sql server as back end data base:
5. shared logon: check shared logon to authenticate users based on the user name and password provided. If the shared logon is omitted application uses user name and password specified in the systems dsn.
6. Enable Connection Pooling: the parameter specifies the maximum wait time before a request reuses the database connection. If all the Maximum connections specified are being used, the new request either waits for a existing request to complete or looks for another connection pool (if specified).
7. Use multithreaded connections: a good excerpt from the help - "When the check box is select ed, Oracle BI Server 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.
The parameter DB_GATEWAY_THREAD_RANGE in the Server section of NQSConfig.ini establishes when Oracle BI Server terminates idle threads. The lower number in the range is the number of threads that are kept open before Oracle BI Server takes action. If the number of open threads exceeds the low point in the range, Oracle BI Server terminates idle threads. For example, if DB_GATEWAY_THREAD_RANGE is set to 40-200 and 75 threads are open, Oracle BI Server terminates any idle threads."
8. Execute queries asynchronously: check if database supports asynchronous queries. Asynchronous queries helps boost the response time of an application.
9. Parameters Supported: check if the sql issued to the database employs any parameters. by default it's checked.
10. Isolation levels: In my perspective the Isolation levels are useful when there exists concurrent reads and writes - which implies a write back should exist.
