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
[EMAIL PROTECTED]


Reply via email to