12 Replies Latest reply on Dec 11, 2009 12:10 PM by 739067

    Memory problem with oracle jdbc driver 10.2.0.4 p-6396242

    739067
      Hi
      We run a Webapplication on Tomcat 5.5.27 (jdk 1.5.0_16)
      and Oracle 10.2.0.4 p-6396242 (implicit and explicit statement caching disabled)

      We use connectionPooling apache commons-dbcp-1.2.2 with commons-pool-1.5.4.

      In a generic module of the application, a lot of dynamic sql statements get created and executed.
      Most of the statements are completely unique and not reusable (no bind variables used).
      Of course, it's not a very usual way to make all statements without using binding. It's an older framework of the customer, which cannot get replaced at the moment.

      The problem is as followed:

      When running a mass data mutation, the jvm's memory-usage goes up in a more or less straight line, about 250meg per minute, which results in an OutOfMemoryException.
      I checked every connection, statement and resultSet to get closed properly.... everything's fine.
      In jProfiler i see, where the memory is used, but it makes no real sense to me.

      In every T4CConnection the 'statements' property contains a linkedList of T4CPreparedStatement instances and the list only gets bigger and bigger, and of course, the gc does not collect anything, since they're all referenced through the linkedList.

      Has anyone an idea, what the problem could be in this case?
      What exactly is T4CConnection.statements, I mean what is it for? And why are the statements hold within T4CConnection.statements?

      Thanks a lot
      Best Regards, Andreas
        • 1. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
          Joe Weinstein-Oracle
          If you are absolutely sure that all statements are being closed ASAP,
          then it sounds like you're describing a driver bug, and the first step
          I'd recommend is that you download and apply the latest appropriate
          version of the Oracle driver, and see if the problem goes away.
          Joe
          • 2. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
            518325
            I can't immediately offer any specific suggestions since you said you've disabled the statement caching, but here's a link to an "Oracle JDBC Memory Management" white paper that may shed some light and is useful regardless.

            [http://www.oracle.com/technology/tech/java/sqlj_jdbc/pdf/memory%20management%20aug%202009.pdf]

            - Rick
            • 3. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
              739067
              Thanks for your answers.
              I use the latest appropriate Oracle driver, same behaviour.
              Andreas
              • 4. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                Timur Akhmadeev
                Hi,

                most likely you have enabled [statements pooling|http://db.apache.org/ojb/docu/guides/repository.html#dbcp.poolPreparedStatements] with default setting maxOpenPreparedStatements=0 which means "no limit". Thus, all statements which are closed in the code, are not really closed via Oracle JDBC driver methods; they go to the DBCP pool. From Oracle JDBC driver point of view they are not closed, hence, there are numerous open cursors both in JVM memory and on the Oracle side (which is a sign for you).
                • 5. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                  518325
                  Good point. Here's a link to the DBCP configuration page.

                  [http://commons.apache.org/dbcp/configuration.html]

                  poolPreparedStatements is false by default but if enabled maxOpenPreparedStatements will be unlimited.

                  - Rick
                  • 6. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                    739067
                    Hi
                    Thanks for your answer, Timur. Good point, yes.. but unfortunately it seems not to be the DBCP statement pooling, the settings of the dataSource(subClass of BasicDataSource) are poolPreparedStatements==false and maxOpenPreparedStatements==-1, which are the defaults (and means 'disabled'). I stepped through the creation of the BasicDataSource to be absolutely sure, that no statementPool was created. I also checked the heap dump.. no statementPool (DBCP) and no statementCache (Oracle).
                    Any suggestions?

                    Thanks, Andreas
                    • 7. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                      Timur Akhmadeev
                      OK. It may not be an issue with DBCP, but I think it is related to the opened cursors. I would run following:
                      select sid, count(*) from v$open_cursor group by sid order by count(*) desc;
                      
                      select sql_id, count(*) from v$open_cursor group by sql_id order by count(*) desc;
                      (probably, filtering SID for only needed sessions based on V$SESSION - column MACHINE or something like this)
                      1 person found this helpful
                      • 8. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                        739067
                        I ran a few tests and monitored the open cursors while the system was working.
                        There are in fact a lot of opened cursors, ... it's always the same sql_text, which I don't really understand: 'SELECT NULL AS table_cat, c.owner AS table_schem, '.
                        The strange thing is...
                        I started in debug mode and stopped on every sql statement, which was executed. Sometimes it creates 1 such cursor, sometimes 3, sometimes more at once.
                        It seems not to be always the same statement, maybe it's a timing problem or something on the database itself.?.

                        After all.... thats the cursor, which fills the memory:
                        'SELECT NULL AS table_cat, c.owner AS table_schem, '
                        It matches with the prepared statements, which are hold within the T4CConnection.

                        You've any idea?

                        Thanks a lot
                        Andreas
                        • 9. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                          Timur Akhmadeev
                          user8680745 wrote:
                          I ran a few tests and monitored the open cursors while the system was working.
                          There are in fact a lot of opened cursors, ... it's always the same sql_text, which I don't really understand: 'SELECT NULL AS table_cat, c.owner AS table_schem, '.
                          Can you post the complete text of this SQL (look at V$SQLAREA.SQL_FULLTEXT by SQL_ID)? I have a feeling it's one of the metadata queries executed by JDBC driver (getDatabaseMetaData(), getColumns(), etc).
                          1 person found this helpful
                          • 10. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                            739067
                            Timur Akhmadeev wrote:
                            Can you post the complete text of this SQL (look at V$SQLAREA.SQL_FULLTEXT by SQL_ID)?
                            Here it is:

                            <pre class="jive-pre">
                            SELECT NULL AS table_cat,
                            c.owner AS table_schem,
                            c.table_name,
                            c.column_name,
                            c.position AS key_seq,
                            c.constraint_name AS pk_name
                            FROM all_cons_columns c, all_constraints k
                            WHERE k.constraint_type = 'P'
                            AND k.table_name = :1
                            AND k.owner like :2 escape '/'
                            AND k.constraint_name = c.constraint_name
                            AND k.table_name = c.table_name
                            AND k.owner = c.owner
                            ORDER BY column_name
                            </pre>

                            Obviously it's the statement to find out the key columns for primaryKeys. But..why it stays open?
                            Maybe we come a bit closer now. PrimaryKeys exist in any variations in the system. There are technical keys as single column (Integer or Number), but also logical keys, single or multi column (Varchar2, Integer, Number, etc.), ... and rarely, there are some tables without primary key.
                            I don't think, that that tables without keys are used within the job, but definitely used are tables with multi column keys (Varchar2, Varchar2).

                            Andreas

                            Edited by: user8680745 on 10.12.2009 01:43
                            • 11. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                              Timur Akhmadeev
                              You have calls to DatabaseMetaData#getPrimaryKeys() in your code. Check for it's ResultSet closing correctness.
                              • 12. Re: Memory problem with oracle jdbc driver 10.2.0.4 p-6396242
                                739067
                                Timur
                                Thank you very much! That's it.
                                There was a getPrimaryKeys() call without closing the resultSet afterwards.

                                Best Regards
                                Andreas