Discussions

Performance and scalability: PreparedStatement with Oracle 8i and WAS 4

Bookmark and Share Get thread feed

  1. PreparedStatement with Oracle 8i and WAS 4 (3 messages)

    Hi everyone,
    We try to use preparedStatements in our application which is running under Websphere 4 with Oracle JDBC driver.
    We set the WAS preparedStatement cache properly and we have implemented a initialization phase that precompile preparedStatements.
    My questions are:
    - When a preparedStatement is created, executed, cached by WAS, then resultset and preparedstatement closed, is the associated cursor closed too ? The fact is that we encountered the "open cursor exceeded" Oracle error.
    - Secondly, is the preparedStatement precompiled during creation (connection.prepareStatement()) or execution (pstmt.execute()) ?
    - Finally, how to estimate the memory that will allocated for caching the preparedStatement in WAS ? Same question on Oracle side...

    Thanx in advance for your help.
    Regards,
    Jacques.
    Reply to this

    Threaded Messages (3)

  2. PreparedStatement with Oracle 8i and WAS 4[ Go to top ]

    When a preparedStatement is created, executed, cached by WAS, then resultset and preparedstatement closed, is the associated cursor closed too ? The fact is that we encountered the "open cursor exceeded" Oracle error.-

    When you get an 'open cursor exceeded' error, the solution is:

    . if you are using WAS 4.0.5, you need to migrate to WAS 4.0.7 in order to avoid an error that appears related to prepared statements and cursors.

    . if you have a different version of WAS, and you still get this error, then you probably are not closing properly a resultset, and statement/preparedStatement or a connection. Take a look at the code involved in the resource closing when appears another kind of error (use always 'finally').
    Secondly, is the preparedStatement precompiled during creation (connection.prepareStatement()) or execution (pstmt.execute()) ?

    No idea.
    Finally, how to estimate the memory that will allocated for caching the preparedStatement in WAS ?

    We have a different strategy. First of all, we allocate a very small cache for prepared statements for every data source. Then, we do a load testing, and see what happens in the metric 'prepared statement cache discards'. If we have a lot of discards, we increase the cache a 50%, and he repeat the process until we get very little discards.


    Jose Ramon Huerga
    http://www.terra.es/personal/jrhuerga
    Reply to this Reply to original

  3. PreparedStatement with Oracle 8i and WAS 4[ Go to top ]

    Thank you for your reply.
    As much as I know, we are using WAS 4.0.5, Oracle 8.1.7.4.0, JDK 1.3.1.
    Regarding the opened cursor error, I found on another forum this:
    "In my experience, using Oracle 8.1.7.4 database with the Oracle (thin) JDBC driver on SUN [sparc] Solaris 7 and J2SE version 1.3.1, if you close the "PreparedStatement" before (or without) closing the "ResultSet", the (Oracle) cursors remain open!"
    So, based on that, we checked all our Resultset, statement, preparedstatement and finally clause. Hope this will be enough.

    The reason why I asked the second question is that we planed to write an initialization phase that creates all necessary preparedStatements associated to the maximum of connections. This way, all will be prepared and cached.
    I was wondering if we just have to do connection.prepareStatement() to prepare the statement and cache it or if we have to execute it also. Nevermind, we'll try both.

    Finally, we planed 1 week of load testing starting with a maximum preparedStatement cache the we'll play with it...

    Thanx again.
    Jacques.
    Reply to this Reply to original

  4. PreparedStatement with Oracle 8i and WAS 4[ Go to top ]

    As much as I know, we are using WAS 4.0.5, Oracle 8.1.7.4.0, JDK 1.3.1.

    Be careful. We had a lot of errors related to cursors that remain opened, and they just disappeared when we moved to WAS 4.0.7.

    we planed to write an initialization phase that creates all necessary preparedStatements associated to the maximum of connections. This way, all will be prepared and cached.

    You don't need to write any initialization phase: you only have to start using the prepared statements, and the first time they are used, WAS will compile them and they will be saved in the cache.


    Hope this helps,

    Jose Ramon Huerga
    http://www.terra.es/personal/jrhuerga
    Reply to this Reply to original