For the sqlplus its a constant statement. I didn't know you could bind variables in sqlplus but I'll try. I suspect it will still be fast.
INSERT INTO table (column) VALUES (value); For the DBD::Oracle I've done both prepare(INSERT INTO table (column) VALUES (?)) execute($value) and do('INSERT INTO table (column) VALUES (value);') Both the execute against the prepared statment and the do() with the constant SQL statement take about the same time. Martin Hall <martin.h...@oracle.com> 12/13/2010 11:35 AM To <ericbamba...@discover.com> cc John Scoles <sco...@pythian.com>, "Martin J. Evans" <martin.ev...@easysoft.com>, <dbi-users@perl.org> Subject Re: DBD::Oracle dbd_st_execute slow speed 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. > > Please consider the environment before printing this email.