Configuring Database Connection Pooling for Siebel Application Object Managers

This topic describes database connection configuration options for Siebel Application Object Managers, particularly database connection pooling. It contains the following information:

• "About Database Connections for Siebel Application Object Managers"

• "Database Connection Pooling Usage Guidelines"

• "Configuring Pooling for Default Database Connections"

• "Configuring Pooling for Specialized Database Connections"

  
  

  Note: Each customer must determine whether their RDBMS has a sufficient total number of database connections for their needs. The total number of available connections is subject to limitations deriving from RDBMS and operating system platforms and other factors. Before you configure connection pooling, verify how many database connections are available for use by the Siebel Application Object Manager. RDBMS performance and usage of database connections by non-Siebel components are outside the scope of this guide. In general, if the available memory resources for database connections for Siebel Business Applications are sufficient without your using database connection pooling, then it is recommended not to use this feature.

  
  

About Database Connections for Siebel Application Object Managers

This topic is part of "Configuring Database Connection Pooling for Siebel Application Object Managers". It provides an overview of database connections for Siebel Application Object Manager components, including nonpooled connections and pooled connections. Subsequent topics provide guidelines and instructions for configuring different types of database connection pooling.

About Nonpooled Database Connections

If you do not pool database connections, then the number of database connections corresponds to the number of Siebel Application Object Manager sessions; that is, database connections are not pooled. No special Siebel Application Object Manager configuration is required for using nonpooled database connections. When no pooling is configured, database connections are closed when the user session terminates.

• Nonpooled default database connections. With nonpooled database connections, during session login, a database connection is established, using the user's database credentials. (When an external authentication system is used, such as LDAP, the user's database credentials might not be the same as the user's Siebel credentials.)

  This database connection becomes bound to the session, and is the default database connection used for read, write, update, and delete operations.

  In this book, such connections are called default database connections. These connections can alternatively be pooled, as described later in this topic.

• Nonpooled specialized database connections. If, during a session, specialized functionality is invoked that uses the external transaction management capabilities of the Siebel Application Object Manager, then a second database connection is opened for this specialized use.

  This database connection is also bound to the session, and is used for all externally controlled transactions performed by the session. Siebel EAI components are an example of specialized code that does external transaction management.

  In this book, such connections are called specialized database connections. These connections can alternatively be pooled, as described later in this topic.

About Pooled Database Connections

Optionally, you can configure your Siebel Application Object Manager components to support pooling for the same two types of database connections described previously for nonpooled database connections:

• Pooled default database connections. These database connections can be pooled to support sharing (multiplexing), persistence, or both features.

  • Shared connections support multiple user sessions at the same time, by multiplexing (sharing) database operations for multiple SQL statements over the same database connection. Using shared connections can support more users with a given number of connections.

  • Persistent connections are pooled, but are not necessarily shared. Using persistent connections can enhance performance by avoiding the cost of creating database connections. All shared connections are also persistent connections.

  For more information, see "Database Connection Pooling Usage Guidelines" and "Configuring Pooling for Default Database Connections".

• Pooled specialized database connections. These database connections are dedicated to a single session at a time, and serve a specialized purpose. Pooling such connections provides persistence, but such connections are never shared. By persistently pooling these connections, you enhance performance by avoiding the cost of creating connections.

  
  

  Note: If you configure pooling for default database connections, but not for specialized database connections, then each specialized database connection is closed when the transaction that required it completes.

  
  

  For more information, see "Database Connection Pooling Usage Guidelines" and "Configuring Pooling for Specialized Database Connections".

Database Connection Pooling Usage Guidelines

This topic is part of "Configuring Database Connection Pooling for Siebel Application Object Managers".

Observe the following guidelines to help you determine if you must use database connection pooling, or to guide your deployment of connection pooling.

For more information about configuring the Siebel Application Object Manager parameters for database connection pooling mentioned in this topic, see "Configuring Pooling for Default Database Connections" and "Configuring Pooling for Specialized Database Connections".

When to Consider Using Database Connection Pooling

Consider implementing database connection pooling if, and only if, one or more of the following is true for your deployment:

• The RDBMS cannot support the number of dedicated user connections that you would require if using nonpooled database connections. Pooling default database connections for shared use can reduce the number of connections you require.

• Memory resources are scarce on the Siebel Server computer on which the Siebel Application Object Manager is running. Pooling default database connections for shared use can reduce Siebel Application Object Manager memory requirements per concurrent user.

• Your deployment uses external authentication such as LDAP (that is, authentication other than database authentication), and creating new connections is slow on the database server. Pooling database connections can speed up login or other operations by providing persistent pooling, whether or not connections are also shared.

• You use a Siebel Server component that requires frequent logins for special-purpose processing. Pooling database connections to provide persistent connection pooling (not sharing) can provide a significant benefit for such components.

  • For Siebel Product Configurator, if you are using the component Siebel Product Configuration Object Manager (alias eProdCfgObjMgr_jpn in a Japanese locale), then it is highly recommended to configure persistent connection pooling. For more information about Siebel Product Configurator, see Chapter 8, "Tuning Siebel Product Configurator for Performance."

  
  

  Note: A separate Application Object Manager component is provided for each installed and deployed language in which you can run your Siebel applications. For example, Call Center Object Manager for French is SCCObjMgr_fra.

  
  

  • For some other components, such as EAI Object Manager (when run using intermittent sessions, where SessionType in the SOAP header = None), it might also be helpful to configure persistent connection pooling.

  
  

  Note: If session caching is configured for a component (by setting the parameter ModelCacheMax), then persistent connection pooling might provide little benefit. For example, session caching is typically configured for Workflow Process Manager. For more information about session caching for Siebel Workflow, see "Caching Sessions".

  
  

Guidelines for Using Database Connection Pooling

If you decide to use database connection pooling, then observe the following guidelines:

• Start with a low ratio of MaxTasks divided by MaxSharedDbConns, such as 2:1.

  
  

  Note: If you plan to use a ratio higher than 3:1, then it is recommended that you consult Oracle Advanced Customer Services. Contact your Oracle sales representative to request assistance from Oracle Advanced Customer Services.

  
  

• If you have short (aggressive) average think times in your user scenarios, then use a smaller ratio of MaxTasks divided by MaxSharedDbConns. Longer think times can support larger ratios.

  For a 30-second think time, do not use a ratio higher than 10:1. For a 15-second think time, do not use a ratio higher than 5:1. Other factors discussed in this topic will also determine practical limits. For more information about think time, see "Performance Factors for Siebel Application Object Manager Deployments".

• Minimize long-running queries. Long-running queries can affect overall performance and must be minimized or avoided. If the current connection pool is blocked by a long-running query from one user session, then other user sessions will use a different pool. However, a long-running query can continue to run on the RDBMS even if the user who initiated it has stopped the browser.

  When you are using database connection pooling, it is critical to optimize database access in your environment. If long-running queries occur, then monitor the overall database response time. To achieve a satisfactory response time, use a small ratio of MaxTasks divided by MaxSharedDbConns.

  You can minimize or avoid long-running queries by providing the Cancel Query option for users, by adjusting indexes to include fields that can be sorted or queried by end users, by configuring the application user interface so that nonindexed fields are not exposed, and by training users to avoid operations that would perform long-running queries. For more information about how indexing can affect Siebel application performance, see "Managing Database Indexes in Sorting and Searching". For more information about configuring the Cancel Query option, see Siebel Applications Administration Guide.

• Consider the cost of creating database connections. This cost differs based on your authentication method.

  If your deployment uses database authentication, a database connection is created for each login, for authentication purposes. Afterwards, this connection is released to the shared connection pool, if the total number of connections is less than the maximum. Or, if the pool is full, then the connection is closed (terminated). Therefore, even when the pool is full and connections are available, new connections are still created temporarily for each new session login. These connections must be accounted for in determining the allocation of database connections.

  With external authentication, however, you can use persistent connection pooling to reduce the cost of creating database connections. With persistent connection pooling, database connections, once created, are persistent, though such connections might or might not be shared. For pooled default database connections where connections are persistent but not shared, set MaxSharedDbConns to equal MaxTasks minus 1. For more information about authentication options, see Siebel Security Guide.

• In order to configure connection pooling for specialized database connections, you must also configure pooling for default database connections, as follows:

  • If you do not configure connection pooling for shared database connections (MaxSharedDbConns equals -1 or 0), then each specialized database connection, once created, is dedicated to the user session. The value of MinTrxDbConns is ignored.

  • If you configure connection pooling for shared database connections (MaxSharedDbConns has a value greater than 0, and less than MaxTasks), then specialized database connections are not dedicated to user sessions. Such connections are handled according to the setting of MinTrxDbConns:

    • If MinTrxDbConns equals -1 or 0, then, after the transaction that required it has ended, each specialized database connection is closed (deleted).

    • If MinTrxDbConns has a value greater than 0, then, after the transaction that required it has ended, each specialized database connection can return to the connection pool.

• Siebel Business Applications do not support MTS or Oracle multiplexing features.

Configuring Pooling for Default Database Connections

This topic is part of "Configuring Database Connection Pooling for Siebel Application Object Managers".

Default database connections can be used by most Siebel Application Object Manager operations.

Configuring Parameters for Pooling Default Connections

The following information describes how to enable or disable pooling for default database connections using the parameters MaxSharedDbConns (DB Multiplex - Max Number of Shared DB Connections) and MinSharedDbConns (DB Multiplex - Min Number of Shared DB Connections).

• To enable connection pooling, set MaxSharedDbConns and MinSharedDbConns to positive integer values (at least 1) that are no higher than MaxTasks minus 1. A connection will be shared by more than one user session once the number of sessions within the multithreaded process exceeds the maximum number of shared connections allowed per process.

  • MaxSharedDbConns controls the maximum number of pooled database connections for each multithreaded process.

  • MinSharedDbConns controls the minimum number of pooled database connections that the Siebel Application Object Manager tries to keep available for each multithreaded process.

    The setting of MinSharedDbConns must be equal to or less than the setting of MaxSharedDbConns. Depending on your Siebel Application Object Manager usage patterns, set these to the same value or set MinSharedDbConns to a lower value (if you determine this to be helpful in conserving database connection resources).

• To configure persistent and shared database connection pooling, set MaxSharedDbConns, using the appropriate ratio of MaxTasks divided by MaxSharedDbConns. Depending on the ratio, a greater or lesser degree of sharing will be in effect. Start with a 2:1 (or smaller) ratio for MaxTasks divided by MaxSharedDbConns. With this example ratio, two user tasks will share the same database connection.

• To configure persistent but nonshared database connection pooling, set MaxSharedDbConns = MaxTasks minus 1.

• To disable connection pooling, set MaxSharedDbConns and MinSharedDbConns to -1 (this is the default value).

MaxSharedDbConns and MinSharedDbConns are defined per Siebel Application Object Manager component, on an enterprise basis (these parameters are included in named subsystems of type InfraDatasources). The database connections these parameters control are not shared across multithreaded processes. The actual maximum number of database connections for each multithreaded process is determined by the ratio MaxSharedDbConns divided by MaxMTServers.

Note: MaxSharedDbConns and MinSharedDbConns work differently than MinTrxDbConns, which specifies the number of shared specialized database connections available for each multithreaded process. For details, see "Configuring Pooling for Specialized Database Connections".

Example Configuration for Pooling Default Connections

Assume, for example, the following parameter settings:

MaxTasks = 500
MaxMTServers = 5
MinMTServers = 5
MaxSharedDbConns = 250
MinSharedDbConns = 250

With these settings, the Siebel Application Object Manager component can support a maximum of 500 tasks (threads). Those 500 tasks would be spread over five multithreaded processes, each having 100 tasks. Each multithreaded process would have a maximum of 50 shared database connections, each of which would serve up to two tasks.

How Pooled Default Connections Are Assigned

When a user logs into the Siebel Application Object Manager, a database connection is established to authenticate the user, then discarded (closed) once the database or external authentication system authenticates the user. After successful authentication, the Siebel Application Object Manager's connection manager checks the connection pool for SQL statements. If this connection pool is empty, then the connection manager adds a connection.

Each time a user initializes an SQL statement, the connection manager checks the connection pool for available connections. The connection manager reserves a connection for the SQL statement in one of the following ways:

• If a connection is available to handle the SQL statement, the connection manager assigns this connection to execute the SQL statement.

  The connection manager reserves this connection until execution of the SQL statement terminates. On termination of the SQL statement, the connection manager releases the connection. At the end of a user session (due to a user logging out or a session timeout), the connection manager checks the connection used by the user session. If this connection is not referenced by other user sessions and the number of connections available in the pool of database connections exceeds the value specified by MinSharedDbConns, the connection manager closes this connection and releases it from the pool of database connections.

• If no connection is available in the connection pool, then the connection manager creates a new connection and assigns it to execute the SQL statement.

  The connection manager continues to add connections to the connection pool until the number of connections in the connection pool equals MaxSharedDBConns.

Configuring Pooling for Specialized Database Connections

This topic is part of "Configuring Database Connection Pooling for Siebel Application Object Managers".

Specialized database connections, which are not shared, are used primarily by specialized Siebel components such as Siebel EAI that need transactions to span multiple Siebel Application Object Manager operations. These connections are used for operations that use BEGIN TRANSACTION and END TRANSACTION.

Configuring Parameters for Pooling Specialized Connections

The following information describes how to enable or disable specialized connection pooling using the parameter MinTrxDbConns (DB Multiplex - Min Number of Dedicated DB Connections).

• MinTrxDbConns controls the minimum number of specialized database connections for each multithreaded process. The connections are not created until they are needed. The minimum value is thus the minimum size of the pool of specialized connections once all of the connections in the pool have been created.

  • To enable specialized connection pooling, set MinTrxDbConns to a positive integer value (at least 1). You must also configure pooling for default database connections.

  • To disable specialized connection pooling, set MinTrxDbConns to minus 1 (this is the default value).

• There is no explicit limit to the maximum number of specialized connections. However, effectively, there cannot be more specialized connections than sessions. On average, there will be many fewer connections than sessions.

MinTrxDbConns is defined per Siebel Application Object Manager component, on an enterprise basis (this parameter is included in named subsystems of type InfraDatasources). The database connections that this parameter controls are not shared across multithreaded processes. The actual minimum number of specialized database connections for each multithreaded process is what you specify as the value for MinTrxDbConns.

Note: MinTrxDbConns works differently than MaxSharedDbConns and MinSharedDbConns. MaxSharedDbConns and MinSharedDbConns specify the number of shared database connections available for all Siebel Application Object Manager processes, while MinTrxDbConns specifies the number of specialized database connections per Siebel Application Object Manager process. For more information, see "Configuring Pooling for Default Database Connections".

Example Configuration for Pooling Specialized Connections

Assume, for example, the following parameter setting, in addition to those described in "Example Configuration for Pooling Default Connections":

MinTrxDbConns = 5

With this setting, each multithreaded process would have a minimum of five specialized database connections. If all five multithreaded processes are running on this Siebel Application Object Manager, then there would be a minimum of 25 specialized connections for this Siebel Application Object Manager.

How Pooled Specialized Connections Are Assigned

When the Siebel Application Object Manager starts up, the specialized connection pool is empty. When a request is made to start a transaction, the Siebel Application Object Manager requests a database connection from the specialized connection pool. If one is available, then it is removed from the pool and given to the session for that session's exclusive use.

When the transaction completes (such as by being committed or canceled), the session returns the specialized connection to the pool. If the pool already contains more than the number of connections specified by MinTrxDbConns, then the specialized connection is closed; otherwise, it is retained in the pool.

Scenario for Assigning Pooled Specialized Connections

Assume, for example, that MinTrxDbConns is set to 2. Specialized connections will be handled as follows:

• User 1 starts Transaction 1. The specialized connection pool is empty, so a new connection is created. Once Transaction 1 completes, this connection is returned to the pool.

• User 2 starts Transaction 2. If Transaction 1 is still running, then a new specialized connection is created. If Transaction 1 completed, then Transaction 2 uses the first database connection.

• When two specialized connections have been created, they will remain in the pool until the Siebel Application Object Manager terminates.