Slow performance of getObject on ref cursor
843854Nov 6 2001 — edited Dec 19 2003Hi guys and gals,
I have a performance issue in accessing data from a ref cursor. The getObject statement is unbelievably slow. In one case, it takes as long as 3 seconds to execute a single getObject statement. I have tested this using a simple System.currentTimeMillis() before and after the statement in question.
The basic scenario is as follows :
I utilise a class that calls an Oracle PL/SQL Stored Procedure using a CallableStatement. The calling class executes on a Web Server, the Oracle ( DB Server ) resides on a different machine ( standard 3 tier stuff ). Network traffic is not a problem. A number of the output parameters registered for the CallableStatement are ref cursors. After the CallableStatement is executed, I use a getObject and cast it to a Result Set object as follows :
objResults = (ResultSet) objCallable.getObject(18);
Where the 18th param has been registered like so :
objCallable.registerOutParameter (18, OracleTypes.CURSOR);
The volume of data is certainly not excessive, typically 80 - 100 rows of data that (eventually) find their way into the drop down list on a Web Page.
About 5 other ref cursors are used and their data accessed in like manner, some of these taking between 1 & 1.5 seconds each to execute. So the whole process of retrieving from these ref cursors via getObject can take almost 10 seconds.
Has anyone experienced this kind of performance problem before, and if so, how can this be optimized ?