Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ref cursor slow compared to sql query

Re: ref cursor slow compared to sql query

From: Allan Streib <streib_at_cs.indiana.edu>
Date: 22 Jun 2004 13:31:15 -0700
Message-ID: <e334e4a7.0406221231.1da87dad@posting.google.com>


Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1087912992.521963_at_yasure>...

> The difference seems excessive. But how do you know the extra time is
> the REF CURSOR And not all of the ASP page and web server baggage?

I see the difference in sql-plus also. e.g.

variable c refcursor

begin
  my_proc(args, :c);
end;
/

print c
[7 - 10 second delay, then 32 rows].

> Can you post the code?

It's rather involved; I'm working to reproduce this with a simpler example. If I run the same select that the procedure is opening for the ref cursor, it returns the results much faster, in subsecond time.  Is there any reason that a static query would run orders of magnitude faster than the same query in a stored proc, with parameters for some of the "WHERE" conditions?

Can I view the query plan for a stored proc? I'd like to compare it to the one for my static query.

Allan Received on Tue Jun 22 2004 - 15:31:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US