Performance problem - using Ref Cursor in stored procedure call from Hibernate

[hildich kilal]

I am using many stored procedures in my application. And We are calling them using normal callable statements.
Issue is the stored procedure takes very little time to execute.. hardly couple of seconds.
But it just hangs as soon as we do
"while(rs.next)".

I got to know ref cursor only start executing when first time "rs.next " is called. But the select query in stored procedure it self is not very heavy and returns from 10 to 1000 rows only.

My ref cursor definition is type RefCursor is ref cursor;
Stored procedure is simple
getMydata( marketData_cur_out OUT RefCursor , marketDataCount_out OUT NUMBER, p_idArray_in IN GLOBAL_NUMBER_VArray, p_searchDateYYYYMMDD_in in varchar2 );

The select is simple.
Is there any trick or issue with using ref cursor to return data in JDBC? Or is it possible that some DB settings is problematic
Any pointers in this regard will be highly appreciated / helpful.

Thanks

[Jan Cumps]

'Hanging' is most of the times not a performance issue. And from what I read from the post, the queries are small, and the return set of records is also normal.

Have you checked for locks? How does the stored procedure behave when you test it without JDBC?