Depends what is in your procedure. If it just opens the ref cursor, then the work (and hence the
60 seconds) will come from the fetching. Oracle can be quite smart by doing almost no work until
the first fetch call is issued (after all, if you never do the first fetch, there is no point in
Oracle doing the work).
hth
Connor
- Stephen.Lee_at_DTAG.Com wrote: > I got one here. I'll start without all the details to see if
the answer is
> something simple that can be answered without the ugly details, trace files,
> etc.
>
> The app calls a procedure in a package which does a select and hands back
> the results to a refcursor which gets used by a VB app (don't look at me, I
> didn't do it). It takes about 60 - 70 seconds for the VB app to get its
> data back.
>
> If I make the procedure a stand-alone procedure that just opens a plain old
> cursor and loops through it with some dbms_output.put_line calls, the thing
> prints out the same info, but returns in less than a second. If I do the
> following:
>
> ----------
> SQL> var A refcursor
> SQL> exec THE_PROCEDURE(parm1, parm2, .... , :A)
>
> PL/SQL procedure successfully completed.
>
> SQL> print A
> ----------
>
> The "successfully completed" returns almost immediately. But the "print A"
> takes about a minute to start printing. Is there some essential element of
> using refcursors that has been missed here?
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
BT Yahoo! Broadband - Free modem offer, sign up online today and save £80
http://btyahoo.yahoo.co.uk
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Jan 29 2004 - 17:20:38 CST
