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.