OCI prefetching REF CURSORS

#1

Hi All,

My application connects to an Oracle Server 9i via OCI.
I have all my select statements on stored procedures, for this I use
REF CURSOR to return the result sets.
It is working correct but in some cases, because one trip to the
server is needed for each row returned by the stored procedure, is
very slow.

Is there any way to enable prefetching of rows when using stored
procedures and REF CURSORs ?
If not, is there any other possiblity to improve perfomance when using
ref cursor ?

Thanks in advance...

#2

On Tue, 22 May 2007 05:01:50 -0700, remius wrote:

Quote:

Hi All,

My application connects to an Oracle Server 9i via OCI. I have all my
select statements on stored procedures, for this I use REF CURSOR to
return the result sets. It is working correct but in some cases, because
one trip to the server is needed for each row returned by the stored
procedure, is very slow.

Is there any way to enable prefetching of rows when using stored
procedures and REF CURSORs ?
If not, is there any other possiblity to improve perfomance when using
ref cursor ?

Thanks in advance...

Sounds like the array interface. You should bind (define?) the parameters
to the array variables and fetch into the arrays. I remember somebody
nagging and requesting the very same thing for Perl DBI.

--
http://www.mladen-gogala.com

#3

On May 22, 8:01 am, rem... (AT) lycos (DOT) es wrote:

Quote:

Hi All,

My application connects to an Oracle Server 9i via OCI.
I have all my select statements on stored procedures, for this I use
REF CURSOR to return the result sets.
It is working correct but in some cases, because one trip to the
server is needed for each row returned by the stored procedure, is
very slow.

Is there any way to enable prefetching of rows when using stored
procedures and REF CURSORs ?
If not, is there any other possiblity to improve perfomance when using
ref cursor ?

Thanks in advance...

You may also want to take a look at OCI_ATTR_PREFETCH_ROWS and/or
OCI_ATTR_PREFETCH_MEMORY.

#4

On May 22, 2:41 pm, Mladen Gogala <mgogala.SPAM_ME.... (AT) verizon (DOT) net>
wrote:

Quote:

On Tue, 22 May 2007 05:01:50 -0700, remius wrote:
Hi All,

My application connects to an Oracle Server 9i via OCI. I have all my
select statements on stored procedures, for this I use REF CURSOR to
return the result sets. It is working correct but in some cases, because
one trip to the server is needed for each row returned by the stored
procedure, is very slow.

Is there any way to enable prefetching of rows when using stored
procedures and REF CURSORs ?
If not, is there any other possiblity to improve perfomance when using
ref cursor ?

Thanks in advance...

Sounds like the array interface. You should bind (define?) the parameters
to the array variables and fetch into the arrays. I remember somebody
nagging and requesting the very same thing for Perl DBI.

--http://www.mladen-gogala.com

Hi, thanks for your answer...

do you know where I can find any example about array binding ?

Thanks.

#5

On Tue, 22 May 2007 09:07:01 -0700, remius wrote:

Quote:

do you know where I can find any example about array binding ?

Thanks.
http://tinyurl.com/2lm5c4

http://tinyurl.com/3d5y39

--
http://www.mladen-gogala.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

OCI prefetching REF CURSORS

comp.databases.oracle.server comp.databases.oracle.server

OCI prefetching REF CURSORS - 05-22-2007 , 07:01 AM

Re: OCI prefetching REF CURSORS - 05-22-2007 , 07:41 AM

Re: OCI prefetching REF CURSORS - 05-22-2007 , 09:40 AM

Re: OCI prefetching REF CURSORS - 05-22-2007 , 11:07 AM

Re: OCI prefetching REF CURSORS - 05-22-2007 , 10:33 PM