Database
connection leaks issues
The dataSource handles the connection pooling.
To prevent memory leaks, all you have to do is when you are finished with a
connection is make sure you close it properly, and set it to null. Closing it
actually releases it back into the connection pool to be used by another
process. If you don't close it, it might not be released back to the pool.
con.close();
con = null;
|
We have a major debate on the best way to return a result
set from PL/SQL to a java serverlet. The best way that I have found,
especally in a dynamic environment, is to return a reference cursor to the
java app. Our DBA's here have a fear of open cursors. Does any one know what
would happen to the sessions in a connection pool if connection to the database
was lost from the webserver? |
If you weren't using connction pooling through your app server, your Java would nees a "finally" clause to close the jdbc connection after all other error handling. If not, you would have a connection "leak" and eventually you would hit your Oracle session limit (I have experienced this).
If your connection pool loses a connection to the DB, then pmon will end the orphaned session and the open cursor(s) will die with it. Ref Cursors do need closing unless they exist at a lower level scope (does not apply in your case as your top level call returns the ref cursor handle to the client).
|
Enable Connection Leak Profiling |
Specifies that JDBC Connection leak profiling information is gathered. A Connection leak occurs when a connection from the pool is
not closed explicitly by calling When connection leak profiling is active, the connection pool will store the stack trace at the time the Connection object is allocated from the connection pool and given to the client. When a connection leak is detected (when the Connection object is garbage collected), this stack trace is reported. This option is required to view leaked connections from the connection pool (right-click the connection pool name and select View Leaked Connections). This feature uses extra resources and will likely slow down connection pool operations, so it is not recommended for production use. MBean: Attribute: |
***************************************************************************************************************
1.
In the left pane, click to the JDBC node to
expand it.
2.
Click the Connection Pools node to expand it
and show the list of connection pools defined in your domain.
3.
Click the connection pool for which you want
to see database connection information. A dialog displays in the right pane
showing tabs with attributes for the connection pool.
4.
Click the Monitoring tab. A table displays
with information about connections in the selected JDBC connection pool for
each server on which the connection pool is deployed.
For
details about the information displayed, see JDBC
Connection Pool --> Monitoring.
By
properly configuring connection pools in your WebLogic Server domain, you can
improve application and system performance.
On
the JDBC—>Connection Pool—>Configuration—>Connections tab, there are
two attributes that you can set to enable connection requests to wait for a
connection from a connection pool: Connection Reserve Timeout and Maximum
Waiting for Connection.
When
an application requests a connection from a connection pool, if all connections
in the connection pool are in use and if the connection pool has expanded to
its maximum capacity, you can configure a Connection Reserve Timeout value (in
seconds) so that connection requests will wait for a connection to become
available. After the Connection Reserve Timeout has expired, if no connection
was has become available, the request will fail.
If
you set Connection Reserve Timeout to -1, a connection request will wait indefinitely.
See Connection
Reserve Timeout for more attribute details.
Note
that connection requests that wait for a connection block a thread. If too many
connection requests concurrently wait for a connection and block threads, your
system performance can degrade. To avoid this, you can set the Maximum Waiting
for Connection attribute, which limits the number connection requests that can
concurrently wait for a connection.
If
you set Maximum Waiting for Connection to 0, the feature is disabled and connection requests
will not be able to wait for a connection.
See Maximum
Waiting for Connection for more attribute details.
1.
In the left pane, click to expand the
Services, JDBC, and Connection Pool nodes to display the list of connection
pools in the current domain.
2.
Click the connection pool that you want to
configure. A dialog displays in the right pane showing the tabs associated with
this instance.
3.
Click the Configuration tab, then click the
Connections tab.
4.
Click Show to show the advanced connection
options.
5.
In Connection Reserve Timeout, enter the
number of seconds that connection requests can wait for a connection.
6.
In Maximum Waiting for a Connection, enter
the maximum number of connection requests that can wait for a connection from
the connection pool while blocking threads.
7.
Click Apply.
A
leaked connection is a connection that was not properly returned to the
connection pool. To automatically recover leaked connections, you can specify a
value for Inactive Connection Timeout on the JDBC—>Connection
Pool—>Configuration—>Connections tab. When you set a value for Inactive
Connection Timeout, WebLogic Server will forcibly return a connection to the
connection pool when there is no activity on a reserved connection for the
number of seconds that you specify. When set to 0 (the default value), this feature is turned off.
See Inactive
Connection Timeout for more attribute details.
Note
that the actual timeout could exceed the configured value for Inactive
Connection Timeout. The internal connection pool maintenance thread runs every
5 seconds. When it reaches the Inactive Connection Timeout (for example 30
seconds), it checks for inactive connections. To avoid timing out a connection
that was reserved just before the current check or just after the previous
check, the server gives an inactive connection a "second chance." On
the next check, if the connection is still inactive, the server times it out
and forcibly returns it to the connection pool. On average, there could be a
lag of 50% more than the configured value.
1.
In the left pane, click to expand the
Services, JDBC, and Connection Pool nodes to display the list of connection
pools in the current domain.
2.
Click the connection pool that you want to
configure. A dialog displays in the right pane showing the tabs associated with
this instance.
3.
Click the Configuration tab, then click the
Connections tab.
4.
Click Show to show the advanced connection
options.
5.
In Inactive Connection Timeout, enter the
number of seconds of inactivity after which a connection will forcibly be
returned to the connection pool.
After
enabling automatic leaked connection recovery, you can view statistics about
connections that leaked from a connection pool:
1.
In the left pane, click to expand the
Services, JDBC, and Connection Pool nodes to display the list of connection
pools in the current domain.
2.
Right-click the connection pool that you
suspect is leaking connections and select View Leaked Connections. If any
connections leaked and were recovered, information about the application that
reserved the connection is displayed in the right pane.
**************************************************************************************************************