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
