Thread Dump analysis | ojdbc14 | Oracle Connection pool

Discussion in 'Java' started by kiranmalla.in@gmail.com, Nov 23, 2005.

  1. Guest

    Hi

    The problem description is a bit large...wanted to be more descriptive.

    System Architecture:
    -------------------
    Apache, Tomcats 5.0.x, mod-jk (for load balancing), Oracle, jsp, java,
    ojdbc14 for connection pooling, jdk.14.1

    Issue:
    ------
    We are running a cluster of 8 tomcats and 4 apache instances load
    balanced by mod-jk. Some Tomcats stop responding after running for a
    period of time and need a restart. We have observed over a period of
    time that a restart of tomcat is required when the thread count is
    around 543 and heap is maxed out at 990M. We are giving application
    intial memory of 1 GB using the Xms and Xmx settings while starting
    tomcats...so memory doesn't seem to be an issue.

    Analysis:
    ---------
    We have started recording the thread dumps. Most of the thread dumps
    show a similar behaviour. Let's say 500 threads "waiting for a monitor
    which is locked by a thread

    The messages in 500 threads is "- waiting to lock <0xbf4e9e60> (a
    oracle.jdbc.pool.OracleConnectionEventListener)"

    This thread has the lock:
    "TP-Processor633" daemon prio=5 tid=0x004921d0 nid=0x5ba waiting for
    monitor entry [872fe000..872ffc28]
    at
    oracle.jdbc.pool.OracleConnectionCacheImpl.closeSingleConnection(OracleConnectionCacheImpl.java:677)
    - waiting to lock <0xbf4e98f8> (a
    oracle.jdbc.pool.OracleConnectionCacheImpl)
    at
    oracle.jdbc.pool.OracleConnectionCacheImpl.reusePooledConnection(OracleConnectionCacheImpl.java:592)
    at
    oracle.jdbc.pool.OracleConnectionEventListener.connectionClosed(OracleConnectionEventListener.java:130)
    - locked <0xbf4e9e60> (a
    oracle.jdbc.pool.OracleConnectionEventListener)
    at
    oracle.jdbc.pool.OraclePooledConnection.callListener(OraclePooledConnection.java:482)
    at
    oracle.jdbc.pool.OraclePooledConnection.logicalClose(OraclePooledConnection.java:445)
    - locked <0xcc9af820> (a oracle.jdbc.pool.OraclePooledConnection)
    at
    oracle.jdbc.driver.OracleConnection.logicalClose(OracleConnection.java:2936)
    - locked <0xcc9af8c8> (a oracle.jdbc.driver.OracleConnection)
    at
    oracle.jdbc.driver.OracleConnection.close(OracleConnection.java:1454)
    - locked <0xcc9af8c8> (a oracle.jdbc.driver.OracleConnection)
    at com.ss.framework.sql.SqlStatement.close(SqlStatement.java:984)
    at
    com.cc.ac.cnams.classmanagement.ClassQry.getCommunityLinksForCourse(ClassQry.java:15796)
    at
    com.cc.ac.cnams.classmanagement.ClassQry.getClassDetailsForViewClassInfo(ClassQry.java:8069)

    Threads waiting for the above lock to be released (around 500 in
    number)
    "TP-Processor632" daemon prio=5 tid=0x00491578 nid=0x5b9 waiting for
    monitor entry [873fd000..873ffc28]
    at
    oracle.jdbc.pool.OracleConnectionEventListener.connectionClosed(OracleConnectionEventListener.java:129)
    - waiting to lock <0xbf4e9e60> (a
    oracle.jdbc.pool.OracleConnectionEventListener)
    at
    oracle.jdbc.pool.OraclePooledConnection.callListener(OraclePooledConnection.java:482)
    at
    oracle.jdbc.pool.OraclePooledConnection.logicalClose(OraclePooledConnection.java:445)
    - locked <0xcd699580> (a oracle.jdbc.pool.OraclePooledConnection)
    at
    oracle.jdbc.driver.OracleConnection.logicalClose(OracleConnection.java:2936)
    - locked <0xcd699640> (a oracle.jdbc.driver.OracleConnection)
    at
    oracle.jdbc.driver.OracleConnection.close(OracleConnection.java:1454)
    - locked <0xcd699640> (a oracle.jdbc.driver.OracleConnection)
    at com.ss.framework.sql.SqlStatement.close(SqlStatement.java:984)
    at
    com.cc.ac.cnams.pa.PersonalAssociationQry.getRole(PersonalAssociationQry.java:1141)
    at
    com.cc.ac.cnams.pa.PersonalAssociation.getRole(PersonalAssociation.java:2353)
    at
    com.cc.ac.cnams.entitlements.Entitlements.isUserOfRole(Entitlements.java:195)
    at
    org.apache.jsp.content.BannerLeftNav_jsp._jspService(BannerLeftNav_jsp.java:201)
    at com.ss.framework.presentation.servlet.Jsp.process(Jsp.java:49)
    at com.ss.framework.presentation.servlet.Jsp.service(Jsp.java:41)
    at
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
    at
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)


    I can provide the complete thread trace if anyone is interested in the
    complete dump.

    In the application, we are using ojdbc14 (oracle's connection pool) for
    handling the connections. I have observed that after doing all the
    operations in a DB object, we close the prepared statement
    (SqlPreparedStatement.close). RESULT SET is not closed explicitly. Can
    this issue be due to the resultset not being closed explicitly.

    Javadoc for jdk 1.4.2 says: "A Statement object is automatically
    closed when it is garbage collected. When a Statement object is closed,
    its current ResultSet object, if one exists, is also closed.

    "http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#close()


    However, some doubts have been raised by people in the links below:
    http://www.theserverside.com/discussions/thread.tss?thread_id=33592
    http://forum.java.sun.com/thread.jspa?threadID=135291&messageID=2615181

    I'm not sure if not closing the 'REsult Set' explicitly can create this
    type of issue as it is supposed to be handled by the java. Not sure if
    ojdbc14 has implemented it as per the spec though.

    Any pointers around the thread dump or the issue of whether ResultSet
    should be closed explicity while using ORACLE Conneciton pool (ojdbc14)
    would be higly appreciated. I can provide more details if required.

    Thanks
    Kiran
     
    , Nov 23, 2005
    #1
    1. Advertising

  2. Bjorn Abelli Guest

    <> wrote...

    > In the application, we are using ojdbc14 (oracle's connection pool) for
    > handling the connections. I have observed that after doing all the
    > operations in a DB object, we close the prepared statement
    > (SqlPreparedStatement.close). RESULT SET is not closed explicitly. Can
    > this issue be due to the resultset not being closed explicitly.


    [snip]

    > Any pointers around the thread dump or the issue of whether ResultSet
    > should be closed explicity while using ORACLE Conneciton pool (ojdbc14)
    > would be higly appreciated. I can provide more details if required.


    Oracle claims that there are problems with some JVMs on the issue of
    finalizers, therefore they urge users to explicitly close ResultSets.

    // Bjorn A
     
    Bjorn Abelli, Nov 23, 2005
    #2
    1. Advertising

  3. Guest

    Thanks Bjorn,

    Some additional info:
    We are using sun'
    "java version "1.4.1_02a"
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1_02a-b01)
    Java HotSpot(TM) Client VM (build 1.4.1_02a-b01, mixed mode)"

    Is the Oracle recommendation documented at some place, so that I can
    refer to that and make a case for closing the resultSets explicitly?

    A strange behaviour in most of the threads (of the thread dump) is that
    they have kept a lock at "
    oracle.jdbc.pool.OracleConnectionCacheImpl.closeSingleConnection(OracleConnectionCacheImpl.java:677)
    - waiting to lock <0xbf4e98f8> (a
    oracle.jdbc.pool.OracleConnectionCacheImpl)"

    I can understand if a thread is locked while getting a connection which
    in turn can lead to other threads wait for the resource. But, I am
    surprised as to why it is creating this issue, while closing the
    connection.

    Polling the group for any other inputs and known issues with the Oracle
    connection pool.

    Thanks
    Kiran
     
    , Nov 23, 2005
    #3
  4. Bjorn Abelli Guest

    <> wrote...

    > Is the Oracle recommendation documented at some place,
    > so that I can refer to that and make a case for closing
    > the resultSets explicitly?


    I would have given you a link to the documentation at Oracle, but it seems
    like I would have to login to their technet-pages to find it, and I don't
    have my password at hand, so I looked up a "mirror" instead...

    http://techdoc.c-bizz.klopotek.de/classes12-O9i/oracle/jdbc/driver/OracleResultSet.html#close()

    or...

    http://tinyurl.com/9asac

    ....look what it says on the close() method...


    // Bjorn A
     
    Bjorn Abelli, Nov 23, 2005
    #4
  5. wrote:
    >
    > The messages in 500 threads is "- waiting to lock <0xbf4e9e60> (a
    > oracle.jdbc.pool.OracleConnectionEventListener)"
    >
    > This thread has the lock:
    > "TP-Processor633" daemon prio=5 tid=0x004921d0 nid=0x5ba waiting for
    > monitor entry [872fe000..872ffc28]
    > at
    > oracle.jdbc.pool.OracleConnectionCacheImpl.closeSingleConnection(OracleConnectionCacheImpl.java:677)
    > - waiting to lock <0xbf4e98f8> (a
    > oracle.jdbc.pool.OracleConnectionCacheImpl)


    Somewhere there may be a thread which holds that particular lock, which
    may have a more interesting stack. You may have a situation where many
    threads are acquiring and releasing the lock.

    > In the application, we are using ojdbc14 (oracle's connection pool) for
    > handling the connections. I have observed that after doing all the
    > operations in a DB object, we close the prepared statement
    > (SqlPreparedStatement.close). RESULT SET is not closed explicitly. Can
    > this issue be due to the resultset not being closed explicitly.


    The ResultSets should be closed at the latest when commit or rollback is
    called (unless you use HOLD_SURSORS_OVER_COMMIT). However, in the past
    some drivers have not been without bugs.

    > Javadoc for jdk 1.4.2 says: "A Statement object is automatically
    > closed when it is garbage collected. When a Statement object is closed,
    > its current ResultSet object, if one exists, is also closed.


    Don't rely on finalisation. Perhaps it will release the resource, but
    you may already have run into trouble before it happens.

    Tom Hawtin
    --
    Unemployed English Java programmer
    http://jroller.com/page/tackline/
     
    Thomas Hawtin, Nov 23, 2005
    #5
  6. steve Guest

    On Wed, 23 Nov 2005 11:32:56 +0800, wrote
    (in article <>):

    > Thanks Bjorn,
    >
    > Some additional info:
    > We are using sun'
    > "java version "1.4.1_02a"
    > Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1_02a-b01)
    > Java HotSpot(TM) Client VM (build 1.4.1_02a-b01, mixed mode)"
    >
    > Is the Oracle recommendation documented at some place, so that I can
    > refer to that and make a case for closing the resultSets explicitly?
    >
    > A strange behaviour in most of the threads (of the thread dump) is that
    > they have kept a lock at "
    >

    oracle.jdbc.pool.OracleConnectionCacheImpl.closeSingleConnection(OracleConnect

    > ionCacheImpl.java:677)
    > - waiting to lock <0xbf4e98f8> (a
    > oracle.jdbc.pool.OracleConnectionCacheImpl)"
    >
    > I can understand if a thread is locked while getting a connection which
    > in turn can lead to other threads wait for the resource. But, I am
    > surprised as to why it is creating this issue, while closing the
    > connection.
    >
    > Polling the group for any other inputs and known issues with the Oracle
    > connection pool.
    >
    > Thanks
    > Kiran
    >


    just a tip.

    Explicitly close EVERYTHING you can.

    Statments & result sets, and anything else you are using, ( apart from the
    connection, if you are re-using it)

    Basically if the oracle or SQL object has a ".close()" then use it
    Explicitly.

    Anything else is the kiss of death, but it looks like you may already know
    that.

    Steve
     
    steve, Nov 25, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Linus Nikander
    Replies:
    5
    Views:
    550
  2. Ulas Ergin

    source code for ojdbc14.jar

    Ulas Ergin, Feb 1, 2005, in forum: Java
    Replies:
    1
    Views:
    6,304
    Antti S. Brax
    Feb 1, 2005
  3. markpittsnh
    Replies:
    0
    Views:
    460
    markpittsnh
    Apr 5, 2005
  4. Wei  Lu
    Replies:
    2
    Views:
    5,923
    Wei Lu
    Feb 16, 2009
  5. Feyruz
    Replies:
    4
    Views:
    2,250
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page