wierd problem while retrieving results from ResultSet

[Rahi Sanj]

Hello,
I am using oracle 10g and using Weblogic's 10g jdbc driver.
I am struck with a wierd problem while retrieving results from a ResultSet object returned from a stored proc.
The program gets struck at the point where I do..

while (resultSet.next())
{..

It takes about 600-900 sec. to go to the first record. After that, when it tries to execute again to retrieve next records, its fast and runs normally. It is having an issue in moving the cursor to the first record for some reason. The proc. itself executes faster and gets the results back. But takes a lot of time when trying to get records.
I have other stored procs. too which return refcursor as resultset objects in exactly similar fashion and I am not having any issue with them.
So, I am not sure if the ResultSet.next() is causing the problem or there is an issue with the cursor or data itself. Does any one had this kind of problem or tell me what might be the issue. Any help is very much appreciated. Thank you.

[Prabhu Venkatachalam]

It might be because of Data Size returned by stored proc is huge.

[Rahi Sanj]

No. The results are not many. Not more than 25 records.

[Hareesh Ram Chanchali]

Can you post the code once

[Chris Hendy]

Does the procedure call have the same delay if you call it from SQL*Plus?

If so then it is a problem with the proc and you should look at the SQL (either use SQL_TRACE and TKPROF on the entire procedure call or yank out the individual SQL statements and execute each in SQL*Plus with typical bind values and AUTOTRACE on).

If not then the problem is more likely in the Java tier.

If you have a metalink account with Oracle then search their bug database in case it's a jdbc driver bug.

Also, if possible post your own code as Hareesh suggested.

[ November 19, 2006: Message edited by: Chris Hendy ]
[ November 19, 2006: Message edited by: Chris Hendy ]

[Chris Hendy]

To expand a little on why it may be a SQL problem:

The number of rows returned is not as important as the amount of work done by the database to retrieve the rows.

Oracle10g PL/SQL has a built in optimisation so it will do an array fetch of 100 rows unless the developer explicitly codes their own using SELECT ... BULK COLLECT.

So given you received 25 rows in your result set, Oracle internally retrieved all of them (i.e. it havd done all the work) when you fetched the first in Java.

If one of the tables you are getting data from is large (say 1,000,000 rows) and you don't use an index to find the 25 rows of interest, then the database will have to scan the entire 1,000,000 row table to satisfy your query. Even with big iron this will take some time. I'm not even going to get into the possible inefficiencies when joining two or more tables together to get your results.

SQL_TRACE and AUTOTRACE are tools that will tell you the amount of work the database has done to retrieve your data and the access path used.

SQL Tuning is a reasonably sized study in its own right. If you are also responsible for maintaining the database code then check out:

Oracle SQL High-Performance Tuning (2nd Edition)
Guy Harrison
http://www.amazon.com/Oracle-SQL-High-Performance-Tuning-2nd/dp/0130123811/sr=8-1/qid=1163943635/ref=sr_1_1/102-5265735-4254527?ie=UTF8&s=books

Or the Oracle 10g doco

Performance Tuning Guide Ch 20
on-line
http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14211%2Ftoc.htm&remark=portal+%28Getting+Started%29
PDF
http://www.oracle.com/pls/db102/to_pdf?pathname=server.102%2Fb14211.pdf&remark=portal+%28Getting+Started%29

If you have a separation of resposibilties between Java Tier and Database Tier, then knowledge of SQL_TRACE/AUTOTRACE is still useful to identify where the problem lies - then if it is SQL you can let the DB Bods sort it out for you.