John,
Its just the execute that takes a long time. The commit is instant
in DBD::Oracle.
Let me check with the DBA about those indexes. It might take a day
or two.
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.
