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.