Recently as in several days ago and the delete finished in about 5 minutes.
I'm not sure how that would affect DBD::Oracle and not my 2 other tools though. Let me double check with my DBA about that table. John Scoles <sco...@pythian.com> 12/09/2010 11:13 AM To <ericbamba...@discover.com> cc <dbi-users@perl.org>, <martin.ev...@easysoft.com> Subject Re: DBD::Oracle dbd_st_execute slow speed On 09/12/2010 11:59 AM, ericbamba...@discover.com wrote: > List, > > I'm not sure if this is relevant but I recently loaded 2 million > rows into a parent table NDE_DATA_HIST then issued a DELETE FROM to delete > all of the rows. I dont have access to TRUNCATE(). The table now has only > about 200 rows. However, the 2 other tools are still fast, its just DBI > that is slow so I dont think its related. > > > Ha! I very much think so. ;) Most likely the index of the 2000000+ is still being updated Rebuild the indexes and then get back to me cheers John > "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 Please consider the environment before printing this email.
