| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Ref Cursors
This was exactly the discussion and solution I was looking for.
I am grateful for all responses...
Jeff Smith
"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
news:a15i6h01cmm_at_drn.newsguy.com...
> 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
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 |
 |