参考文档：AOL/J JDBC Connection Pool White Paper (文档 ID 278868.1)
The JDBC Pool in e-Business Suite is implemented through the core java technology framework for the entire suite (named AOL/J). AOL/J uses the “dbc” file in order to obtain the parameters needed to create a database connection. AOL/J technology uses JDBC, mostly and more intensively, （AOL/J 使用dbc文件作为建立数据库连接的参数）in web applications. When starting iAS HTTPD it launches the ApacheJserv engine（R12应用是mod-OC4J）. During the startup of ApacheJerv is when the pool is built, based on the parameters specified by the DBC file. The DBC file is located under $FND_TOP/secure and is generated during installation time. Here’s an example of a DBC file:
#DB Settings
#Wed Oct 29 13:08:09 EST 2003
APPS_JDBC_DRIVER_TYPE=THIN
APPL_SERVER_ID=C07944762D3D6A6EE0340003BA0DE5CC42929651847093158337209480361998
TWO_TASK=PROD
GUEST_USER_PWD=GUEST/ORACLE
DB_HOST=gjimenez-sun.us.oracle.com
DB_NAME=PROD
FNDNAM=APPS
GWYUID=APPLSYSPUB/PUB
DB_PORT=14001
This particular example does not include any sizing parameter for the JDBC connection pool and, when initialized, it will start with the default values for the following parameters:
FND_JDBC_MAX_CONNECTIONS: 2147483647 (Defined as Integer.MAX, in java.lang.Integer)
FND_JDBC_BUFFER_MIN: 5
FND_JDBC_BUFFER_MAX: 50%
The default parameters basically means that:
· A maximum of 2147483647 connections will be opened.
· When started, the JDBC Connection pool will open 5 connections as a minimum.
· In order to start reusing connections, when the percentage (or number) declared in parameter FND_JDBC_BUFFER_MAX of FND_JDBC_MAX_CONNECTIONS is reached.
When using the default settings in a heavily used system, it is possible to encounter situations where the size of the pool grows, leaving the database running out of processes or out of connections. If this condition surfaces, the best way to deal with this situation is to reconfigure the AOL/J database connection pool parameters. Please, before doing this, check with Oracle Support Services if effectivelly changing these parameters will fix your situation.
NOTE |
We are changing the default values for the parameter FND_JDBC_MAX_CONNECTIONS in Bug#3186367. |
If multiple JVMs are implemented (ie: Jserv（R12应该mod-OC4J） load balancing -multiple JVMs in a single box-, HTTP Load balancing -Multiple Web Servers connecting to the same instance- or a combination of both), it is important to note that each JVM will create its own JDBC Pool. Under these circumstances, it’s very difficult to troubleshoot what the problem is, since some users might be getting errors when trying to obtain connections from the pool, while when trying to use the diagnostics tools provided to troubleshoot the problem, a different JVMs could be accessed, obtaining the wrong results for the tests.
he connection pool parameters are summarized in the following table, and discussed in detail below.
| Category | Parameter | Meaning | Allowed Values | Default value |
|---|---|---|---|---|
| Pool Size | FND_MAX_JDBC_CONNECTIONS | The maximum pool size. This is the sum of the number of available connections and the number of locked connections. | positive integers less than Integer.MAX | Integer.MAX |
| FND_JDBC_BUFFER_MIN | Minimum buffer size to maintain. (SessionManager.java 115.116) |
positive integers less than Integer.MAX | 5 | |
| FND_JDBC_BUFFER_MAX | Maximum buffer size to maintain. (SessionManager.java 115.116) |
positive integers less than Integer.MAX. A percent sign can be appended to the integer to indicate that the buffer maximum should be calculated dynamically as a percent of total pool size. | 50% | |
| Thread | FND_JDBC_BUFFER_DECAY_INTERVAL | How often the maintenance thread should check buffer size (in seconds). (SessionManager.java 115.116) |
positive integers less than Integer.MAX | 60 seconds |
| FND_JDBC_BUFFER_DECAY_SIZE | The maximum number of available objects that should be removed during any one thread cycle. (SessionManager.java 115.116) |
positive integers less than Integer.MAX | 1 | |
| Selection | FND_JDBC_MAX_WAIT_TIME | The maximum wait time. This is the maximum amount of time that a client should use trying to get a connection. | Not user configurable. | 10 seconds |
| FND_JDBC_SELECTION_POLICY | The selection policy used to select connections from the available list. | Not user configurable. | Pool.COST (cost-based) |
|
| Safety Check | FND_JDBC_USABLE_CHECK | Indicates whether the a simple pl/sql query should be performed to check whether the connection is usable before giving a connection to the client. (DBConnObj.java 115.12) |
true or false | true |
| FND_JDBC_CONTEXT_CHECK | Indicates whether the AOL security context and NLS state should be obtained from the database server session instead of the java client when the connection is returned to the pool. (DBConnObj.java 115.12) |
true or false | true | |
| FND_JDBC_PLSQL_RESET | Indicates whether the PL/SQL state should be freed before the pool gives the connection to the client. (DBConnObj.java 115.12) |
true or false | false |
FND_MAX_JDBC_CONNECTIONS
The maximum pool size is the maximum allowed sum of the number of available connections and the number of locked connections. If the pool reaches the maximum size and all connections are locked, new clients will not be able to borrow a connection until one of the current clients has returned one. The default setting for this parameter is essentially unlimited (about 2 billion).
FND_JDBC_BUFFER_MIN
The buffer minimum is the minimum number of connections that the pool should try to maintain in the available list. When the buffer size falls below the buffer minimum, the pool maintenance thread will be notified to create new connections. When notified, the thread will immediately attempt to create the number of connections to fill the difference. New connections will not be created if the pool is already at its maximum size. When creating new connections the thread uses the attributes of the most recent client request that resulted in a new connection being created.
Setting this parameter to "0" will disable maintenance of the buffer minimum. However, the buffer maximum will still be maintained.
Setting this parameter to a number greater than the maximum pool size (FND_MAX_JDBC_CONNECTIONS) will disable all buffer maintenance.
See Buffer Maintenance.
FND_JDBC_BUFFER_MAX
The buffer maximum is the maximum number of connections that the pool should try to maintain in the available list. During heavy usage, the buffer may exceed this maximum. However, during periods of low usage, the maintenance thread will decrease the buffer size until the buffer maximum is reached.
If the value of this parameter is an integer, (for example "20") the buffer maximum is static. If the value is a percent (for example, "20%"), the buffer maximum is not constant but instead is calculated dynamically as a percent of total pool size. The buffer minimum is also taken into account when determining a dynamic buffer maximum. The exact expression used is:
- maximum(t) = buffer minimum + ( (FND_JDBC_BUFFER_MAX/100) * size(t) )
where maximum(t) and size(t) are the buffer maximum and pool size at some time t.
The thread is configured to periodically check the buffer size. If the buffer size is greater than the maximum, the thread will remove either the number of available connections specified by FND_JDBC_BUFFER_DECAY_SIZE or the number of connections in excess of the buffer minimum, whichever is smaller. When connections are removed from the available list, the least recently used ones are removed first.
Setting this parameter to100%, or to a number equal to FND_MAXIMUM_JDBC_CONNECTIONS, or to a number less than or equal to FND_JDBC_BUFFER_MIN will effectively prevent the maintenance thread from ever removing any connections.
See Buffer Maintenance.
FND_JDBC_BUFFER_DECAY_INTERVAL
The buffer decay interval specifies how often the connection pool maintenance thread should check the buffer size. The thread will check the buffer size at most once every FND_JDBC_BUFFER_DECAY_INTERVAL seconds. The actual time between consecutive thread cycles will vary somewhat depending on the JVM load.
This parameter, along with FND_JDBC_BUFFER_DECAY_SIZE, allows the buffer decay rate to be tuned. For example, if the buffer decay size is 2 and the buffer decay interval is one minute, the buffer decay rate will never exceed two connections per minute. When connections are removed, the least recently used ones are removed first.
See Buffer Maintenance.
FND_JDBC_BUFFER_DECAY_SIZE
The buffer decay size specifies the maximum number of connections that should be removed during any single thread cycle during which the number of available connections is greater than the buffer size. This parameter, along with FND_JDBC_BUFFER_DECAY_INTERVAL, allows the buffer decay rate to be tuned.
See Buffer Maintenance.
FND_JDBC_MAX_WAIT_TIME
The maximum wait time specifies how much time a client should spend trying to get a connection. The borrow algorithm, used to borrow an object from the pool, contains check points at which the elapsed time is compared to the maximum wait time. If it exceeds the maximum wait time, then a null object will be returned to the client. The pre-configured value for the maximum wait time is 10 seconds.
FND_JDBC_SELECTION_POLICY
The selection policy determines how a connection is selected from the list of available connections for a particular client. The connection pool is pre-configured to use a cost-based selection algorithm, which selects the connection that will require the smallest amount of initialization to match the client's context.
FND_JDBC_USABLE_CHECK
The FND_JDBC_USABLE_CHECK parameter governs whether a pl/sql query is performed before giving a connection to a client. The pool checks whether a connection is usable before handing it to a client. This always involves checking that the connection is not null and is not closed. If FND_JDBC_USABLE_CHECK is set to true, then it also verifies that the connection can be used to perform a simple PL/SQL query. (This parameter may have to be set to "true" in order to clean up connections to a database that has been restarted.)
The table below summaries the affect of FND_JDBC_USABLE_CHECK and the other safety check parameters on borrowing a connection from the pool.
FND_JDBC_CONTEXT_CHECK
The FND_JDBC_CONTEXT_CHECK parameter governs whether the AOL security context and NLS state is obtained from the database when the connection is returned to the pool. If FND_JDBC_CONTEXT_CHECK is "true", when the connection is returned to the pool, the AOL security context and NLS state will be obtained from the database. (This is implemented in the DBConnObj.isReusable() method). This check must be done when the connection is returned (rather than when it is borrowed) so that the selection matching algorithm has access to the actual session context of the connections in the available list.
The table below summaries the affect of FND_JDBC_CONTEXT_CHECK and the other safety check parameters on borrowing a connection from the pool.
FND_JDBC_PLSQL_RESET
The PL/SQL reset flag, set using the variable FND_JDBC_PLSQL_RESET, governs whether the PL/SQL state associated with a connection should be freed before the pool hands the connection to the client. By default this flag is false. If the flag is set by true, by including the line "FND_JDBC_PLSQL_RESET=true" in the .dbc file, each connection to the database will have its PL/SQL state cleared before the pool returns the connection to the client.
This is how it works. After the pool selects a connection from the available list for a client, it initializes the connection. One of the things initialization does is to set a flag that is later used by SessionManager to determine if the apps initialization routine needs to be performed for the connection. When FND_JDBC_PLSQL_RESET has been set to "true", this flag will always be set to true. After the pool initializes the connection, it also checks whether the connection is usable. In this case, this check will include a call to DBMS_SESSION.RESET_PACKAGE, which frees the PL/SQL state. The table below summaries the affect of FND_JDBC_PLSQL_RESET and the other safety check parameters on borrowing a connection from the pool.
The FND_JDBC_PLSQL_RESET parameter has been added to only to address the case where production PL/SQL global bugs are known to exist. The performance of the pool is reduced by setting this flag to true.
- Pool Safety Checks
- The following table shows the affect on default connection pool operations of setting each "safety check" parameter to true. If the check causes extra database round trips, it is noted as "+n DBRT", where "n" is the number of additional round trips.
