Front page | perl.dbi.users |
Postings from January 2007
RE: :Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?
Thread Previous
|
Thread Next
From:
Reidy, Ron
Date:
January 19, 2007 11:31
Subject:
RE: :Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?
Message ID:
7209E76DACFED9469D4F5169F9880C7A675397@mail01bldr.arraybp.com
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.
Thread Previous
|
Thread Next