I take it you are comparing like for like with the Plus or Developer
script? By that I mean, using bind variables as well. Or, have you
tried this piece of Perl with some hardcoded values? I just wonder if
it's the binding that's taking the time.
Cheers
(another) Martin
On 13/12/2010 17:04, ericbamba...@discover.com wrote:
Martin, John, list
My DBA ran an analyze table, looked at the indexes and server
load, and confirmed its not an 'Index Organized' table.
Yes, its just the execute() that takes 4 seconds not the commit
which makes the problem even more bizzarre. I would expect the opposite.
The slqplus script and the perl program were run from the same
server so that eliminates that. The SQL Developer statement was run from
my local machine.
I've been able to reproduce the behavior on 2 different servers.
On both servers sqlplus is fast while DBD::Oracle is slow. Both connect to
the same oracle database and same schema and tables.
Any ideas list? I like the direction the threading idea was going
but unless someone can re-explain it to me I think the fact that only the
execute() is slow and not the commi() it doesn't make sense. sqlplus's
execute would be on my thread and should take just as long as DBD::Oracle.
Thanks for your help but the DBA and I are fresh out of ideas and
performance is still terrible :(
Server 1:
AIX 6.1
Perl (v5.12.2)
$DBI::VERSION = "1.615"
$DBD::Oracle::VERSION = '1.26'
Server 2:
AIX 5.3
Perl v5.8.8
$DBI::VERSION = "1.51";
$DBD::Oracle::VERSION = '1.17';
John Scoles<sco...@pythian.com>
12/09/2010 11:52 AM
To
<dbi-users@perl.org>
cc
Subject
Re: DBD::Oracle dbd_st_execute slow speed
On 09/12/2010 12:24 PM, Martin J. Evans wrote:
On 09/12/10 16:52, ericbamba...@discover.com wrote:
Martin,
do() takes 4 seconds as well. Sorry about the SQL_INTEGER
junk. I
was testing things. It takes the same amount of time if I leave off the
types. I remember seeing the trace show it bind as VARCHAR or something
when I leave off the type and it was still slow.
Here is the code snippet I used with do(). The script printed:
Do TOOK 4 seconds
my $do_q = "INSERT INTO FL_SYS_STAT_HIST
(ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT)
VALUES
(".$_->get_id().",".$s->{id}.",".$_->get_free_space().",".$_->get_used_space().")";
$start = time();
$s->{_dbh}->do($do_q) or
die("Do failed: $DBI::errstr\n");
$end = time();
print STDERR "Do TOOK ".($end-$start)." seconds\n";
die("Dying for do()\n");
There are still things you've not told us:
Oracle database version
which Oracle client libs are you using and what version
DBI version
DBD::Oracle version
Perl version
However, the only time I've seen something like this it was index
related and yet you maintain sqlplus does not suffer from the same issue.
One time it was missing indexes and the other it was Oracle deciding not
to use the index because of data type mismatches.
Is there perhaps a difference in how you are connecting between
DBD::Oracle and sqlplus? Was sqlplus command and Perl script run from same
machine?
One other thing to remember is that sqlplus is threaded for each command
while DBD::Oracle is not.
So in sqlplus the commit may be on a different thread so it be quick to
return even if the commit hits a 2meg index regenerate.
Do you know if the execute or the commit is taking a long time in
DBD::Oracle??
One other thing to think of check with your DBA that your table is not
an 'Index Organized Table' that will really bugger you up if you have a
big index
and few rows
Martin
Please consider the environment before printing this email.
