2 Replies Latest reply: Oct 1, 2009 9:37 PM by 800387 RSS

    Resultset data processing : Strange behaviour : default fetchsize returned

    807580
      I have observed a strange behaviour from Resultset object. My application fetches 400 records from a table and processes these records every 10secs. By default the resultset has a fetchsize of 10 from the database cursor.

      As I understand if the query returns 400records, the resultset will fetch 40times, in multiple of 10 to get all these 400 records from database cursor.
      Query : SELECT * FROM ( SELECT * FROM TestTable WHERE STATE_ACTION = 0 ORDER BY ROP_TIME DESC ) WHERE ROWNUM <= 250

      Observation :
      Under a normal operation, the resultset fetches all the 400 records on query execution from database cursor, but under unknow conditions the same resultset object fetches only 10 records from the database cursor and exits.

      Please refer page 297 in document below for the result fetch size details.
      JDBC developer guide for oracle 10g : http://download.oracle.com/docs/cd/B19306_01/java.102/b14355.pdf

      This condition self-heals itself in few hours or restarting the database or restarting the server. The root cause of this behavior is unknown.
      Can anyone explain why and how this behaviour can be observed ?

      Any help on this topic will be highly appreciated as I am observing this condition on a critical production environment and it heals itself. Please advice.

      Edited by: Vijay_Tripathi on 2/10/2009 00:26

      Edited by: Vijay_Tripathi on 2/10/2009 00:27