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"); "Martin J. Evans" <martin.ev...@easysoft.com> 12/09/2010 10:33 AM To <dbi-users@perl.org> cc Subject Re: DBD::Oracle dbd_st_execute slow speed On 09/12/10 15:37, ericbamba...@discover.com wrote: > DBI Users, > > I'm having trouble with DBD::Oracle and very simple insert > statements into tables with less than 200 records only 2 FKs and no > indexes taking over 4 seconds. Inserts to other tables seem unaffected. I > can run the exact same statement from SQLPlus or SQL Developer with no > speed issues. It is lightning quick unless I use my perl code. The exact > same issue presents itself whether I use $dbh->do() or a traditional > prepare/execute. > > Does anyone know why only DBD::Oracle would have trouble? It looks > like it hangs in the driver on OCIStmtExecute_log_stat but I cant be 100%. > Something wacky is happening in the interaction between the driver and > server. > > The issue is in $sth->execute(). My timing from just that part > shows about 4-5 seconds consistently. It happens each and every query. > > Almost my exact issue seems to have been covered before on a slightly > different DB version but no answer was posted: > > http://www.nntp.perl.org/group/perl.dbi.users/2006/11/msg30473.html > > Please help me troubleshoot this issue and let me know if I can > provide any more information to the group. > > Here is the perl code I'm using. > > my $fs_store_q = "INSERT INTO FL_SYS_STAT_HIST > (ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT) > VALUES ( ?,?,?,?)"; > my $fs_sth; > $s->{_dbh}->{ora_verbose} = 6; > $s->{_dbh}->trace(6); > unless($fs_sth = $s->{_dbh}->prepare($fs_store_q,)){ > carp("Can't prepare stmt: $DBI::errstr\n"); > return undef; > }; > foreach(@{$s->{workspaces}}){ > $fs_sth->bind_param(1,$_->get_id(),SQL_INTEGER); > $fs_sth->bind_param(2,$s->{id},SQL_INTEGER); > $fs_sth->bind_param(3,$_->get_free_space(),SQL_INTEGER); > $fs_sth->bind_param(4,$_->get_used_space(),SQL_INTEGER); Not that I believe this is the source of your problem but I don't think DBD::Oracle knows what an SQL_INTEGER is: dbd_bind_ph(): bind :p1 <== '6' (type 4 ((UNKNOWN SQL TYPECODE 4))) and might be better written as ora_type => SQLT_INT or leave the type off the bind. DBD::Oracle by defaults binds strings to parameters and I have seen Oracle get upset when it receives strings for another type where it basically ignores the index. When you are using sqlplus or sql developer or some_other_tool are they binding the parameter as DBD::Oracle does or are they just passing in the entire SQL e.g., there is a world of difference between: insert into mytable values(1,2,3) and prepare insert into mytable values(?,?,?) bind params 1-3 as strings execute How long does it take if it is straight forward do method call with just SQL and no parameters? > $start = time(); > unless($fs_sth->execute()){ > carp("Can't execute stmt: $DBI::errstr\n"); > return undef; > }; > $end = time(); > $s->{_dbh}->{ora_verbose} = 0; > print STDERR "STORE TOOK ".($end-$start)." seconds\n"; > } > > $ sqlplus mjmc_u...@db30 > > SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 9 10:20:13 2010 > > Copyright (c) 1982, 2009, Oracle. All rights reserved. > > Enter password: > > Connected to: > Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit > Production > With the Partitioning, OLAP, Data Mining and Real Application Testing > options > > SQL> select * from v$version where banner like 'Oracle%'; > > BANNER > -------------------------------------------------------------------------------- > Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit > Production > > The table and insert statement are dead simple. Here they are. Sorry about > the lengthy DDL its autogenerated. > > INSERT INTO FL_SYS_STAT_HIST > (ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT) VALUES > (1,28990,0,0); > > CREATE TABLE MJMC.FL_SYS_STAT_HIST > ( > ABI_FMT_ID NUMBER(5) NOT NULL, > DATA_COLL_ID NUMBER(10) NOT NULL, > WRK_SPCE_FREE_KB_CNT NUMBER(15) NULL, > WRK_SPCE_USE_KB_CNT NUMBER(15) NULL > ) > TABLESPACE MJMC_D_01 > LOGGING > STORAGE(BUFFER_POOL DEFAULT) > NOPARALLEL > NOCACHE > > ALTER TABLE MJMC.FL_SYS_STAT_HIST > ADD CONSTRAINT FL_SYS_STAT_HIST_PK > PRIMARY KEY (ABI_FMT_ID,DATA_COLL_ID) > USING INDEX TABLESPACE MJMC_X_01 > STORAGE(BUFFER_POOL DEFAULT) > ENABLE > VALIDATE > > CREATE INDEX MJMC.FL_SYS_STAT_HIST_FK1_X > ON MJMC.FL_SYS_STAT_HIST(ABI_FMT_ID) > TABLESPACE MJMC_X_01 > STORAGE(BUFFER_POOL DEFAULT) > NOPARALLEL > NOCOMPRESS > / > CREATE INDEX MJMC.FL_SYS_STAT_HIST_FK2_X > ON MJMC.FL_SYS_STAT_HIST(DATA_COLL_ID) > TABLESPACE MJMC_X_01 > STORAGE(BUFFER_POOL DEFAULT) > NOPARALLEL > NOCOMPRESS > > ALTER TABLE MJMC.FL_SYS_STAT_HIST > ADD CONSTRAINT FL_SYS_STAT_HIST_FK1 > FOREIGN KEY (ABI_FMT_ID) > REFERENCES MJMC.ABI_FL_SYS_FMT (ABI_FMT_ID) > ENABLE > / > ALTER TABLE MJMC.FL_SYS_STAT_HIST > ADD CONSTRAINT FL_SYS_STAT_HIST_FK2 > FOREIGN KEY (DATA_COLL_ID) > REFERENCES MJMC.NDE_DATA_HIST (DATA_COLL_ID) > ENABLE > > > The attached trace should show 2 inserts and then I called die() otherwise > the program is designed to run forever in a loop. > > > Please consider the environment before printing this email. > Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com Please consider the environment before printing this email.