Re: Ref Cursors

In article <a14n09$q674_at_eccws12.dearborn.ford.com>, "Jeff says...
>
>I have a question regarding what is the most efficient way to deal with
>procedures returning ref cursors.
>
>We are developing a very large web application using J2EE and Oracle 8i (see
>below for versions).
>Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
>PL/SQL Release 8.1.7.2.0 - Production
>CORE 8.1.7.0.0 Production
>TNS for SEQUENT DYNIX/ptx: Version 8.1.7.2.0 - Production
>NLSRTL Version 3.4.1.0.0 - Production
>
>All database interaction is done via Oracle procedures, functions and
>packages. This includes generating/obtaining all results sets.
>
>There seems to be some issues. To return more than one row from a stored
>procedure, we use Ref Cursors. But the communication that occurs back and
>forth between the application server (Websphere) appears to be excessive.
>There is a complete round trip occurring between the app server and the
>database for each row.
>
>My question, is there any way to speed up the results that come from a ref
>cursor used as an out parameter in a stored procedure? If we were to execute
>the same sql statement from a JSP or elsewhere on the app server, would our
>peformance be better?
>
>We would prefer to keep all database activity contained within the database.
>Is there a more efficient way to get "sets" of data out of procedures?
>
>Thanks
>Jeff Smith
>
>
>

well, basically a ref cursor won't be any different then a cursor cursor -- meaning if you put the sql in the app -- it would be the same.

Oracle has for a very long time support the concept of an ARRAY fetch -- one fetch fetches many rows (reducing the round trips).

This is a question for your JDBC vendor -- do they support this? If I were using the Oracle thin/thick jdbc drivers, I would get a default "prefetch" (number of rows to ask the database for at a time) of 10. I can set this progamatically using:

    ((OracleStatement)stmt).setRowPrefetch (200);

where statement was a jdbc Statement. That would cause us to return (upto) 200 rows when you asked asked for one.

For example, I took this simple program:

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class RefCursor
{

public static void main (String args []) throws SQLException, ClassNotFoundException {

   String query = "begin getRefCur(:1); end;";

   DriverManager.registerDriver

        (new oracle.jdbc.driver.OracleDriver());

   Connection conn=

       DriverManager.getConnection
       ("jdbc:oracle:thin:@aria-dev:1521:ora817dev",
         "tkyte", "tkyte");

   Statement trace = conn.createStatement();    trace.execute( "alter session set sql_trace=true" );

   CallableStatement cstmt = conn.prepareCall(query);

   cstmt.registerOutParameter(1,OracleTypes.CURSOR);    cstmt.execute();

   ResultSet rset= (ResultSet)cstmt.getObject(1);

   for(int i = 0; rset.next(); i++ )
    System.out.println( rset.getString(1) );

   rset.close();
   cstmt.close();
}
}
where the plsql behind it is:

create or replace package types
as

    type rc is ref cursor;
end;
/

create or replace procedure getRefCur( p_cursor in out types.rc ) as
begin

    open p_cursor for select * from all_users; end;
/

and after running tkprof on the trace file i find:

SELECT *
FROM
 ALL_USERS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        3      0.00       0.01          0        127          4          24
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.03          0        127          4          24

It did THREE fetches to get 24 rows (10 rows, 10 rows, 4 rows). there were 3 round trips for this.

If you find this is different -- consult your jdbc documentation from your vendor for how to fix this.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp