DBD::Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?

Hello, all!

I'm having slow performance reading from a cursor that is returned from a stored procedure. Network tracing and strace confirms that a round-trip is being done to the Oracle DB to fetch each row, with no pre-caching. Using DBI-1.52, DBD-Oracle-1.18 on Linux with Perl 5.8.5 and the ora10202_32 client.

If I'm reading the DBD::Oracle docs correctly, a cursor returned from a PL/SQL stored proc will be considered a "nested cursor" (even though there are no cursors nested inside the rows returned). So, I've set the ora_max_nested_cursors and the RowCacheSize database handle attributes right after connecting. (I've also tried them in the connect.) This does not seem to change the behavior.

Here's some sample code I've culled out of my program. Any ideas welcome!

[...]

my $dbh = DBI->connect( "dbi:Oracle:$config{'OracleDB'}",
$config{'OracleUser'},
$config{'OraclePassword'},
{ RaiseError=> 1,
PrintError => 0,
AutoCommit => 1 } ) ;

$dbh->{RowCacheSize} = 10;
$dbh->{ora_max_nested_cursors} = 16;

##
## Pull cursor, dump to file
##

my $cursor;
my @errors;
open (DUMPFILE, '>', "/tmp/dumpfile.txt");

eval {
my $sth = $dbh->prepare( q{
BEGIN
SOME_PKG.GET_CURSOR(:someid,:cursor );
END;
} );
$sth->bind_param(":someid", 42);
$sth->bind_param_inout(":cursor", \$cursor, 0, {ora_type => ORA_RSET});
$sth->execute;
};

if ($@) {
print STDERR "$DBI::err -- $DBI::errstr\n";
} else {
while (my @row = $cursor->fetchrow_array) {
print DUMPFILE join ("\t",@row) . "\n";
}
}

[...]



Thank you!

Joel Noble
jnoble@frii.net

RE: :Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?

Joel,

To really know understand the problem, you should gather an extended SQL
trace (event 10046) at the DB level at level 8 or higher -
http://orafaq.com/faqdbain.htm#EVENTS

After the program has completed, you will need to get the trace file
from the server's udump directory and format it with tkprof for easy
reading.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc

-----Original Message-----
From: Joel Noble [mailto:jnoble@frii.net]=20
Sent: Friday, January 19, 2007 11:07 AM
To: dbi-users@perl.org
Subject: DBD::Oracle: Row cache fetch ahead on cursor returned from
PL/SQL stored proc?


Hello, all!

I'm having slow performance reading from a cursor that is returned from
a stored procedure. Network tracing and strace confirms that a
round-trip is being done to the Oracle DB to fetch each row, with no
pre-caching. Using DBI-1.52, DBD-Oracle-1.18 on Linux with Perl 5.8.5
and the ora10202_32 client.

If I'm reading the DBD::Oracle docs correctly, a cursor returned from a
PL/SQL stored proc will be considered a "nested cursor" (even though
there are no cursors nested inside the rows returned). So, I've set the
ora_max_nested_cursors and the RowCacheSize database handle attributes
right after connecting. (I've also tried them in the connect.) This
does not seem to change the behavior.

Here's some sample code I've culled out of my program. Any ideas
welcome!

[...]

my $dbh =3D DBI->connect( "dbi:Oracle:$config{'OracleDB'}",=20
$config{'OracleUser'},=20
$config{'OraclePassword'},
{ RaiseError=3D> 1,
PrintError =3D> 0,
AutoCommit =3D> 1 } ) ;

$dbh->{RowCacheSize} =3D 10;
$dbh->{ora_max_nested_cursors} =3D 16;

##
## Pull cursor, dump to file
##

my $cursor;
my @errors;
open (DUMPFILE, '>', "/tmp/dumpfile.txt");

eval {
my $sth =3D $dbh->prepare( q{
BEGIN
SOME_PKG.GET_CURSOR(:someid,:cursor );
END;
} );
$sth->bind_param(":someid", 42);
$sth->bind_param_inout(":cursor", \$cursor, 0, {ora_type =3D>
ORA_RSET});
$sth->execute;
};

if ($@) {
print STDERR "$DBI::err -- $DBI::errstr\n";
} else {
while (my @row =3D $cursor->fetchrow_array) {
print DUMPFILE join ("\t",@row) . "\n";
}
}

[...]



Thank you!

Joel Noble
jnoble@frii.net



This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: :Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?

Thanks -- will give that a try.

Can I assume this means you believe that DBD::Oracle/DBI should
indeed be pre-caching rows in the reading from the cursor that was
received from the procedure? That is, it is reasonable to think that
it should be doing pre-caching and that it's a bug (configuration
problem, etc.) that it's not working? This is my assumption, but I'm
not sure -- perhaps I'm expecting something that doesn't really work that way.

Thanks!

Joel Noble
jnoble@frii.net

At 12:30 PM 1/19/2007, Reidy, Ron wrote:
>Joel,
>
>To really know understand the problem, you should gather an extended SQL
>trace (event 10046) at the DB level at level 8 or higher -
>http://orafaq.com/faqdbain.htm#EVENTS
>
>After the program has completed, you will need to get the trace file
>from the server's udump directory and format it with tkprof for easy
>reading.
>
>--
>Ron Reidy
>Lead DBA
>Array BioPharma, Inc
>
>-----Original Message-----
>From: Joel Noble [mailto:jnoble@frii.net]
>Sent: Friday, January 19, 2007 11:07 AM
>To: dbi-users@perl.org
>Subject: DBD::Oracle: Row cache fetch ahead on cursor returned from
>PL/SQL stored proc?
>
>
>Hello, all!
>
>I'm having slow performance reading from a cursor that is returned from
>a stored procedure. Network tracing and strace confirms that a
>round-trip is being done to the Oracle DB to fetch each row, with no
>pre-caching. Using DBI-1.52, DBD-Oracle-1.18 on Linux with Perl 5.8.5
>and the ora10202_32 client.
>
>If I'm reading the DBD::Oracle docs correctly, a cursor returned from a
>PL/SQL stored proc will be considered a "nested cursor" (even though
>there are no cursors nested inside the rows returned). So, I've set the
>ora_max_nested_cursors and the RowCacheSize database handle attributes
>right after connecting. (I've also tried them in the connect.) This
>does not seem to change the behavior.
>
>Here's some sample code I've culled out of my program. Any ideas
>welcome!
>
>[...]
>
>my $dbh = DBI->connect( "dbi:Oracle:$config{'OracleDB'}",
> $config{'OracleUser'},
> $config{'OraclePassword'},
> { RaiseError=> 1,
> PrintError => 0,
> AutoCommit => 1 } ) ;
>
>$dbh->{RowCacheSize} = 10;
>$dbh->{ora_max_nested_cursors} = 16;
>
>##
>## Pull cursor, dump to file
>##
>
>my $cursor;
>my @errors;
>open (DUMPFILE, '>', "/tmp/dumpfile.txt");
>
>eval {
> my $sth = $dbh->prepare( q{
> BEGIN
> SOME_PKG.GET_CURSOR(:someid,:cursor );
> END;
> } );
> $sth->bind_param(":someid", 42);
> $sth->bind_param_inout(":cursor", \$cursor, 0, {ora_type =>
>ORA_RSET});
> $sth->execute;
>};
>
>if ($@) {
> print STDERR "$DBI::err -- $DBI::errstr\n";
>} else {
> while (my @row = $cursor->fetchrow_array) {
> print DUMPFILE join ("\t",@row) . "\n";
> }
>}
>
>[...]
>
>
>
>Thank you!
>
>Joel Noble
>jnoble@frii.net
>
>
>
>This electronic message transmission is a PRIVATE communication which contains
>information which may be confidential or privileged. The information
>is intended
>to be for the use of the individual or entity named above. If you are not the
>intended recipient, please be aware that any disclosure, copying,
>distribution
>or use of the contents of this information is prohibited. Please notify the
>sender of the delivery error by replying to this message, or notify us by
>telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: :Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?

On Fri, Jan 19, 2007 at 05:24:53PM -0700, Joel Noble wrote:
> Thanks -- will give that a try.
>
> Can I assume this means you believe that DBD::Oracle/DBI should
> indeed be pre-caching rows in the reading from the cursor that was
> received from the procedure? That is, it is reasonable to think that
> it should be doing pre-caching and that it's a bug (configuration
> problem, etc.) that it's not working? This is my assumption, but I'm
> not sure -- perhaps I'm expecting something that doesn't really work that
> way.

This topic has cropped up from time to time. I believe the code does
the right thing and that the issue _may_ be related to specific Oracle
versions. But then again I don't recall anyone specifically saying it
worked for them. Check the archives for more detail.

Tim.

Re: DBD::Oracle: Row cache fetch ahead on cursor returned from PL/SQLstored proc?

On 19/01/07 18:07, Joel Noble wrote:
> Hello, all!
>
> I'm having slow performance reading from a cursor that is returned
> from a stored procedure. Network tracing and strace confirms that
> a round-trip is being done to the Oracle DB to fetch each row, with no pre-caching. Using DBI-1.52, DBD-Oracle-1.18 on Linux with Perl 5.8.5 and the ora10202_32 client.
>
> If I'm reading the DBD::Oracle docs correctly, a cursor returned
> from a PL/SQL stored proc will be considered a "nested cursor"
> (even though there are no cursors nested inside the rows returned).
> So, I've set the ora_max_nested_cursors and the RowCacheSize
> database handle attributes right after connecting. (I've also
> tried them in the connect.) This does not seem to change the behavior.
>
> Here's some sample code I've culled out of my program. Any ideas welcome!

Two points.

1. ora_max_nested_cursors is not relevant. This value is used
only in the case where a result set has columns of type REF CURSOR,
resulting in nested fetches. If you don't have a 'CURSOR( ... )'
expression in the select list of a 'SELECT' statement, you don't
have nested cursors.

2. Does you result set have LONG or LONG RAW columns? Caching is
disabled if you have these.

--
Charles Jardine - Computing Service, University of Cambridge
cj10@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679

Re: DBD::Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?

Charles Jardine wrote:

> 1. ora_max_nested_cursors is not relevant. This value is used
> only in the case where a result set has columns of type REF CURSOR,
> resulting in nested fetches. If you don't have a 'CURSOR( ... )'
> expression in the select list of a 'SELECT' statement, you don't
> have nested cursors.

OK, that makes sense to me -- they didn't seem intuitivel "nested".
As you can tell, I was just trying anything that seemed close.

Unfortunately, removing the setting of ora_max_nested_cursors
doesn't make it go faster either... :)

> 2. Does you result set have LONG or LONG RAW columns? Caching is
> disabled if you have these.

I checked, and they're all VARCHARs and one NUMBER type. Interesting
observation though -- I'd never heard that before.

Thank you for your reply. Anything else you can think of for me to
check?

If you do a similar thing in your environment, do you get
the same behavior, or does DBI cache ahead when reading from a
cursor returned from a stored proc?

Just in case, I replaced the stored proc call with the SQL from
within the stored proc, and sure enough the sample DBI/DBD::Oracle
code caches ahead very nicely. So this problem seems limited to
pulling results from a cursor.


Thanks again!

Joel Noble
jnoble@frii.net