Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, a Business Intelligence and Data Warehouse Enthusiast passionate about blogging articles in the BI, Data warehousing, space. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects.

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 73 other followers

  • Blog Hits Since Nov 2010

    • 364,321 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for the ‘Connection Pools’ Category

Physical Layer – Connection Pool

Posted by Santosh Kumar Gidadmani on February 15, 2011

The connection pool contains information about the connection between Oracle BI Server and a data source. For each data source, there is at least one corresponding connection pool.

Components of Connection Pool
1. Connection Pool name: Name of the connection pool.
2. Call Interface: The call interface represents the driver which you use to connect to the data source. There are three kinds ODBC, OCI, XML.
3. Maximum Connections: Mention the maximum connections based on the concurrent users in the organization. Once this limit is reached, the Oracle BI Server routes all other connection requests to another connection pool if available or, if no other connection pools exist, 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. Ensure the right connections are provided.
4. Require fully qualified table names: When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database.
5. Data source name (DSN): Name of the DSN
6. Share Logon: If this option is checked, then all connections to the database that uses the connection pool will use the user name and password specified in the connection pool. If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the Siebel user profile.
7. Enable Connection Pooling: It allows multiple concurrent query requests to share a single database connection. This reduces the overhead of connecting to a database because it does not open and close a new connection for every query. If you do not select this option, each query sent to the database opens a new connection.
8. Use multithreaded connections: When the check box is selected, Oracle BI Server terminates idle physical queries (threads). When not selected, one thread is tied to one database connection. Even if threads are idle, they consume memory.
9. Execute queries synchronously: This provides the way to communicate with Oracle Database, if this option is checked, it is run asynchronously. If this option is not checked, the query is run synchronously. It is always uncheck by default.
10. Parameters supported: If this option is checked, that means all the DB parameters mentioned in the DB features are supported by server. By default, this option will be checked.
11. Isolation level: This option controls the transaction locking behavior for all statements issued by a connection. There are four kinds
1. 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.
2. Dirty Read: Locking. Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.
3. 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.
4. 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.

– Santosh

Posted in Connection Pools, OBIEE 10g, OBIEE 10g RPD, Physical Layer Objects | Leave a Comment »

%d bloggers like this: