On Thu, 14 Mar 2002 18:03:40 -0600
Chris Starling <[EMAIL PROTECTED]> wrote:
> First question: Is there a DBD::Oracle specific mailing list I should
> be posting this question to?
>
You can see diferences when you are inserting/deleting/updating records, but the
performance of select statments in Perl is almost as good as a c program fetching in
arrays.
Set the $dbh->{RowCacheSize} to a significative value (not 1 !!!), try with 1000 or
5000 to start.
Have you set the array option in sqlplus?
I just tried 2 scripts, one sqlplus and one perl. Both fetch all the rows from a
500000 rows table and write it to a file:
I run the scripts on the same dbms box (linux 2.4.16, oracle 9.0.1
Perl (include connect and disconnect time) (DBI=1.21 DBD::Oracle=1.12)
RowCacheSize = 0 time 167s
RowCacheSize = 100 time 23s
RowCacheSize = 500 time 19s
RowCacheSize = 5000 time 19s
sqlplus (just the query time)
set array off time 47s
set array 1000 time 29s
set array 5000 time 29s
Best regards,
Marcelo.
> Now onto my real question:
>
>
> Is there some way to tweak DBD::Oracle or DBI to make it run faster?
>
>
> I'm exporting a large amount of data from Oracle 8.1.7 with a Perl
> script using DBD::Oracle, and I'm getting pretty lousy performance.
> I've eliminated most variables by running my perl script and then
> running a SQL*Plus script using the same query. I've done this on
> three different platforms now and the SQL*Plus script is MANY, MANY
> times faster. The Perl runs on the order of 200 seconds and the
> SQL*Plus is usually between 25 and 40 seconds. The query itself runs
> in less than 10 seconds.
>
> Platforms tested:
>
> Linux 2.4.18 (Redhat 7.1 I think)
> perl 5.6.1
> DBI 1.14
> DBD::Oracle 1.06
>
> CygWin on Win2K
> perl 5.6.1
> DBI 1.20
> DBD::Oracle 1.12
>
> Solaris 7
> Unsure of perl & DBI versions (not my box)
>
>
> Below is the Perl that I'm testing. I've removed the SQL and column
> names to protect the innocent:
>
>
> #!/usr/bin/perl
> use strict;
> use DBI;
>
> my $dbinstance = "dbinstance";
> my $dbuser = "dbuser";
> my $dbpasswd = "dbpasswd";
> my $sql = "query that returns many rows";
>
> my $dbh = DBI->connect("dbi:Oracle:$dbinstance", $dbuser, $dbpasswd);
> my $sth = $dbh->prepare($sql);
> $sth->execute;
> my ($col1, $col2, $col3, $col4);
> $sth->bind_columns( \($col1, $col2, $col3, $col4) );
> while ($sth->fetch) {}
>
>
>
> Not much to it, really.
>
> Note that I'm not doing anything with each row I fetch. The SQL*Plus
> script is "SPOOL"-ing to a file, so even with the disk overhead it's
> still faster. (alas!)
>
> I've been running both Perl & SQL*Plus on both a machine with a local
> oracle instance and a machine where the oracle is across the network on
> another machine. It seemed to make little impact and the SQL*Plus was
> still significantly faster regardless of the situation.
>
> So unless there are any other variables or factors that I'm missing
> here, I'm concluding that the difference in speed has to do with the
> internal workings of DBI and DBD::Oracle. So the question is:
>
>
> Is there some way to tweak DBD::Oracle or DBI to make it run faster?
>
>
> Thanks!
> -Chris Starling
>
