Loading ...
Sorry, an error occurred while loading the content.

Re: [xml-dbms] Re: Maximum Cursors Exceeded error in Oracle

Expand Messages
  • Ronald Bourret
    Thanks for this information. As Abdel notes, prepared statements are never closed except during garbage collection, so this is probably the cause of the
    Message 1 of 16 , Nov 20, 2003
    View Source
    • 0 Attachment
      Thanks for this information. As Abdel notes, prepared statements are
      never closed except during garbage collection, so this is probably the
      cause of the problem.

      The question is how to trade off efficiency and database resources. If
      XML-DBMS closes all prepared statements after using them, it will have
      to continually reprepare statements, which is very inefficient. On the
      other hand, if it doesn't close statements, there is the risk of running
      out of database resources. Furthermore, it does not appear that
      DatabaseMetaData can tell me how many open cursors I can have, so I
      don't see a way to close statements when the database runs out of
      resources.

      Ideas?

      -- Ron

      Narayanasamy Kanakasabai wrote:
      >
      > HI ,
      >
      > In JDBC, each prepare statement will consume one cursor, So you need to close the prepared statement level.
    • abdelhachim
      ... resources. If ... have ... On the ... running ... As I clearly explain in my initial post, the problem is that statements are re-prepared (pooling is not
      Message 2 of 16 , Nov 20, 2003
      View Source
      • 0 Attachment
        --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
        wrote:
        > The question is how to trade off efficiency and database
        resources. If
        > XML-DBMS closes all prepared statements after using them, it will
        have
        > to continually re-prepare statements, which is very inefficient.
        On the
        > other hand, if it doesn't close statements, there is the risk of
        running
        > out of database resources. Furthermore, it does not appear that
        > DatabaseMetaData can tell me how many open cursors I can have, so I
        > don't see a way to close statements when the database runs out of
        > resources.
        >
        > Ideas?
        >

        As I clearly explain in my initial post, the problem is that
        statements are re-prepared (pooling is not effective) since they are
        not checked-in explicitly to the pool.

        What we actually need is to close the wrapper(SPPrepared statement)
        witch do not close the PreparedStatement (does not free database
        resources) but only release it back to the pool so that it can be
        reused when a statement is to be executed with the same sql code...

        I implemented the solution I explained in my first post and it works
        very fine :
        ==> The maximum number of Prepared Statement allowed by the Database
        is no longer reached (At each time, there is less active prepared
        statements than before)
        ==> It is more efficient since Statements need not to be re-prepared
        (pooling is active)...

        Abdel
      • Charles Fulnecky
        I have not had a chance to implement yet but Abdel s info looks correct to me. Ronald Bourret wrote:Did this fix your problem? -- Ron
        Message 3 of 16 , Nov 20, 2003
        View Source
        • 0 Attachment
          I have not had a chance to implement yet but Abdel's info looks correct to me.

          Ronald Bourret <rpbourret@...> wrote:Did this fix your problem?

          -- Ron

          Charles Fulnecky wrote:
          >
          > Abdel,
          >
          > Thanks for the reply, your analysis makes sense and correlates with what I am seeing ... will try implementing your suggestions and let you know.
          >
          > Charlie

          Yahoo! Groups Sponsor
          To post a message, send it to: xml-dbms@yahoogroups.com
          To unsubscribe, send a blank message to: xml-dbms-unsubscribe@yahoogroups.com
          Or join the newsgroup at: news://news.barkto.com/homeless.xml.xml-dbms

          Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



          ---------------------------------
          Do you Yahoo!?
          Free Pop-Up Blocker - Get it now

          [Non-text portions of this message have been removed]
        • Ronald Bourret
          Oops. You re right -- I didn t read that closely enough. Your solution points out one of the persistent design problems I have had with version 2.0: 1) People
          Message 4 of 16 , Nov 22, 2003
          View Source
          • 0 Attachment
            Oops. You're right -- I didn't read that closely enough.

            Your solution points out one of the persistent design problems I have
            had with version 2.0:

            1) People need to be able to write their own, database-specific SQL
            statements. The primary reason for this is to support database-generated
            keys, as there is no standard way to retrieve these. However, it
            provides a nice hook for other functionality as well. This is what the
            DataHandler interface is about.

            2) XML-DBMS needs to have control over certain things, such as closing
            result sets and, as this bug shows, closing prepared statements.

            The first point requires objects like prepared statements to be
            encapsulated at a low, user-controlled level (the DataHandler object).
            The second point requires many of the very same objects to be exposed at
            a higher, XML-DBMS-controlled level (DOMToDBMS, DBMSToDOM, etc.) I have
            not yet figured out a way to resolve this problem. Ideas are, of course,
            welcome.

            Note that there is still an outstanding problem with respect to system
            resources. Even if XML-DBMS closes prepared statements and returns them
            to the statement pool, the pool needs to know when to close them or it
            runs the risk of keeping too many prepared statements around. Two
            solutions I can think of are:

            1) Set a timeout and close any statement that has existed for more than
            x milliseconds. Two problems with this are that (a) I haven't a clue
            what a reasonable number of milliseconds are and (b) it uses extra
            system resources running timers on each statement. (The latter probably
            aren't that significant, but are an annoyance nonetheless.)

            2) Let the user specify the maximum number of prepared statements that
            can exist at any one time. If this number is exceeded, the pool starts
            closing unused statements.

            Note that it had been my hope that JDBC1DataSource and JDBC2DataSource
            would simply be fall-backs and that most users would use DataSource
            objects provided with their JDBC drivers, as those would undoubtedly be
            more sophisticated than JDBCxDataSource. I'm not sure if this is
            actually happening, although the lack of support for external
            DataSources in Transfer and the problems pointed out by this bug
            certainly make it seem unlikely.

            -- Ron

            abdelhachim wrote:
            >
            > --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
            > wrote:
            > > The question is how to trade off efficiency and database
            > resources. If
            > > XML-DBMS closes all prepared statements after using them, it will
            > have
            > > to continually re-prepare statements, which is very inefficient.
            > On the
            > > other hand, if it doesn't close statements, there is the risk of
            > running
            > > out of database resources. Furthermore, it does not appear that
            > > DatabaseMetaData can tell me how many open cursors I can have, so I
            > > don't see a way to close statements when the database runs out of
            > > resources.
            > >
            > > Ideas?
            > >
            >
            > As I clearly explain in my initial post, the problem is that
            > statements are re-prepared (pooling is not effective) since they are
            > not checked-in explicitly to the pool.
            >
            > What we actually need is to close the wrapper(SPPrepared statement)
            > witch do not close the PreparedStatement (does not free database
            > resources) but only release it back to the pool so that it can be
            > reused when a statement is to be executed with the same sql code...
            >
            > I implemented the solution I explained in my first post and it works
            > very fine :
            > ==> The maximum number of Prepared Statement allowed by the Database
            > is no longer reached (At each time, there is less active prepared
            > statements than before)
            > ==> It is more efficient since Statements need not to be re-prepared
            > (pooling is active)...
            >
            > Abdel
          • Charles Fulnecky
            Ron, I implemented the changes Abdel recommended and everything compiles since his changes are related to retrieves and my problem was associated with inserts,
            Message 5 of 16 , Dec 4, 2003
            View Source
            • 0 Attachment
              Ron,

              I implemented the changes Abdel recommended and everything compiles
              since his changes are related to retrieves and my problem was associated with inserts, I haven't had a chance to test retrieves fully.

              Following Abdel's example I also made the below changes to org.xmlmiddleware.xmldbms.datahandlers.GenericHandler.insert() which resolved my problem.

              public void insert(Table table, Row row)

              throws SQLException

              {

              checkState();

              PreparedStatement stmt = buildInsert(table, row);

              int numRows = stmt.executeUpdate();

              stmt.close(); // caf added to resolve maxopencursors errors in oracle

              databaseModified();

              .

              .

              .

              PreparedStatement selStmt = getConnection().prepareStatement(sql);

              // Set the parameters

              Parameters.setParameters(selStmt, 0, colVec, row.getColumnValues(colVec));

              // Execute it

              ResultSet rs = selStmt.executeQuery();

              selStmt.close(); // caf added to resolve maxopencursors errors in oracle

              // Make sure at least 1 row.

              if(!rs.next())

              throw new SQLException("[xmldbms] Couldn't retrieve inserted row due to changed values.");

              .

              .

              .

              // If more than one row then error.

              if(rs.next())

              throw new SQLException("[xmldbms] Couldn't retrieve inserted row due to multiple rows with identical values.");

              }

              }

              }
              -- Charlie

              Ronald Bourret <rpbourret@...> wrote:
              Oops. You're right -- I didn't read that closely enough.

              Your solution points out one of the persistent design problems I have
              had with version 2.0:

              1) People need to be able to write their own, database-specific SQL
              statements. The primary reason for this is to support database-generated
              keys, as there is no standard way to retrieve these. However, it
              provides a nice hook for other functionality as well. This is what the
              DataHandler interface is about.

              2) XML-DBMS needs to have control over certain things, such as closing
              result sets and, as this bug shows, closing prepared statements.

              The first point requires objects like prepared statements to be
              encapsulated at a low, user-controlled level (the DataHandler object).
              The second point requires many of the very same objects to be exposed at
              a higher, XML-DBMS-controlled level (DOMToDBMS, DBMSToDOM, etc.) I have
              not yet figured out a way to resolve this problem. Ideas are, of course,
              welcome.

              Note that there is still an outstanding problem with respect to system
              resources. Even if XML-DBMS closes prepared statements and returns them
              to the statement pool, the pool needs to know when to close them or it
              runs the risk of keeping too many prepared statements around. Two
              solutions I can think of are:

              1) Set a timeout and close any statement that has existed for more than
              x milliseconds. Two problems with this are that (a) I haven't a clue
              what a reasonable number of milliseconds are and (b) it uses extra
              system resources running timers on each statement. (The latter probably
              aren't that significant, but are an annoyance nonetheless.)

              2) Let the user specify the maximum number of prepared statements that
              can exist at any one time. If this number is exceeded, the pool starts
              closing unused statements.

              Note that it had been my hope that JDBC1DataSource and JDBC2DataSource
              would simply be fall-backs and that most users would use DataSource
              objects provided with their JDBC drivers, as those would undoubtedly be
              more sophisticated than JDBCxDataSource. I'm not sure if this is
              actually happening, although the lack of support for external
              DataSources in Transfer and the problems pointed out by this bug
              certainly make it seem unlikely.

              -- Ron

              abdelhachim wrote:
              >
              > --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
              > wrote:
              > > The question is how to trade off efficiency and database
              > resources. If
              > > XML-DBMS closes all prepared statements after using them, it will
              > have
              > > to continually re-prepare statements, which is very inefficient.
              > On the
              > > other hand, if it doesn't close statements, there is the risk of
              > running
              > > out of database resources. Furthermore, it does not appear that
              > > DatabaseMetaData can tell me how many open cursors I can have, so I
              > > don't see a way to close statements when the database runs out of
              > > resources.
              > >
              > > Ideas?
              > >
              >
              > As I clearly explain in my initial post, the problem is that
              > statements are re-prepared (pooling is not effective) since they are
              > not checked-in explicitly to the pool.
              >
              > What we actually need is to close the wrapper(SPPrepared statement)
              > witch do not close the PreparedStatement (does not free database
              > resources) but only release it back to the pool so that it can be
              > reused when a statement is to be executed with the same sql code...
              >
              > I implemented the solution I explained in my first post and it works
              > very fine :
              > ==> The maximum number of Prepared Statement allowed by the Database
              > is no longer reached (At each time, there is less active prepared
              > statements than before)
              > ==> It is more efficient since Statements need not to be re-prepared
              > (pooling is active)...
              >
              > Abdel


              ---------------------------------
              Do you Yahoo!?
              Free Pop-Up Blocker - Get it now

              [Non-text portions of this message have been removed]
            Your message has been successfully submitted and would be delivered to recipients shortly.