Ref Cursors

[Jeff Smith]

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

[Sybrand Bakker]

On Fri, 4 Jan 2002 12:03:34 -0500, "Jeff Smith" <jsmi...@ford.com>
wrote:

A ref cursor will *always* return one row at a time. Hence, this
results in one roundtrip, and there is nothing you can do about that.
Remember, if you build pure Oracle applications (ie you don't port
them from sqlserver) you won't use stored procedure.
The only solution is to fetch the data in array's, as there will be
one roundtrip per array.
I would look into using the jdbc drivers available from Oracle.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

[Thomas Kyte]

In article <a14n09$q6...@eccws12.dearborn.ford.com>, "Jeff says...

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 (tk...@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

[Thomas Kyte]

In article <5m8c3u8rdnejki2il...@4ax.com>, Sybrand says...

absolutely FALSE. a ref cursor is just like a cursor. See
http://groups.google.com/groups?q=group:comp.databases.oracle.*+insubject:ref+insubject:cursors+author:tkyte%40us.oracle.com&selm=a15i6h01cmm%40drn.newsguy.com&rnum=2

which demonstrates this.

>Remember, if you build pure Oracle applications (ie you don't port
>them from sqlserver) you won't use stored procedure.

absolutely FALSE. Using stored procedures and never putting
SELECTS/INSERTS/UPDATES/DELETES in any application is a very good practice, one
that I encourage (it makes it so much easier to tune when I can sit at a
terminal, look at a tkprof, find the bad query, read the code out of the
database, fix it in vi, put it back in -- no redeploy this
war/ear/jar/zip/whatever file, bounce the app server, reload the application,
yadda yadda yadda.....).

I gain security enhancements by doing this.
I gain reusability by doing this.
I gain ease of maintainence by doing this.
I gain control by doing this.

>The only solution is to fetch the data in array's, as there will be
>one roundtrip per array.

and ref cursors do that quite easily

>I would look into using the jdbc drivers available from Oracle.
>

that I agree with.

>Hth
>
>Sybrand Bakker, Senior Oracle DBA
>
>To reply remove -verwijderdit from my e-mail address

--

[Daniel A. Morgan]

You wrote the following in the posting above:

"absolutely FALSE. Using stored procedures and never putting SELECTS/INSERTS/UPDATES/DELETES in any application is a very good practice"

Could you please expand on this. If you don't put these into a stored procedure what possible use could a stored procedure have? And if not there ... where?

Thanks,

Dan Morgan

[Thomas Kyte]

In article <3C3787A4...@exesolutions.com>, "Daniel says...

We need a bit more context here to make this make sense for others, the
discussion was:

<quote>

>A ref cursor will *always* return one row at a time. Hence, this
>results in one roundtrip, and there is nothing you can do about that.

absolutely FALSE. a ref cursor is just like a cursor. See
http://groups.google.com/groups?q=group:comp.databases.oracle.*+insubject:ref+insubject:cursors+author:tkyte%40us.oracle.com&selm=a15i6h01cmm%40drn.newsguy.com&rnum=2

which demonstrates this.

>Remember, if you build pure Oracle applications (ie you don't port
>them from sqlserver) you won't use stored procedure.

absolutely FALSE. Using stored procedures and never putting

SELECTS/INSERTS/UPDATES/DELETES in any application is a very good practice, one
that I encourage (it makes it so much easier to tune when I can sit at a
terminal, look at a tkprof, find the bad query, read the code out of the
database, fix it in vi, put it back in -- no redeploy this
war/ear/jar/zip/whatever file, bounce the app server, reload the application,
yadda yadda yadda.....).

I gain security enhancements by doing this.
I gain reusability by doing this.
I gain ease of maintainence by doing this.
I gain control by doing this.

</quote>

I read:

>Remember, if you build pure Oracle applications (ie you don't port
>them from sqlserver) you won't use stored procedure.

to mean that if you are using Oracle, putting SELECTS into stored procedures and
returning result sets from stored procedures is a bad practice, something
"oracle people" don't do and "MS people do do".

My point was -- It is a pretty good practice in my opinion regardless of the
database you are using. I would like to see NO inserts/updates/deletes/selects
in the client application. I personally would love to see all of that logic
centralized in the database. Need a result set -- fine, call a stored procedure
to get it. That is my personal preference. The reasons for that preference --
i listed above.

I was trying to say -- the approach of using stored procedures to return result
sets is one to be encouraged in Oracle. Not discouraged, it is not a "sql
server 'ism" (although in sql server, its sort of a mandatory thing -- if you
don't do it that way, you'll kill yourself on parses since they don't have
shared sql like we do)

[Daniel A. Morgan]

Thanks for the expansion and clarification.

Dan Morgan

[Jeff Smith]

This was exactly the discussion and solution I was looking for.

I am grateful for all responses...

Jeff Smith
"Thomas Kyte" <tk...@us.oracle.com> wrote in message
news:a15i6...@drn.newsguy.com...