John, Thanks. Please let me know if you need anyting else. I've attached two gzipped files - a level 15 trace and my affected perl code module. The trace should show the prepare then a single execute before die() ing. The progam is a daemon and will run forever unless I die(). The sub with the problem in the code sample is store().
Do you need the trace set when we first connect to the DB? There are a lot of other DB operations you might not be interested in and which perform jsut fine. Please pardon the code mess around the affected areas. In this trace I see that the execute took 5 seconds. John Scoles <sco...@pythian.com> 12/09/2010 10:30 AM To <dbi-users@perl.org> cc Subject Re: DBD::Oracle dbd_st_execute slow speed On 09/12/2010 10:37 AM, ericbamba...@discover.com wrote: Sounds odd really We will have to know the version of DBD::Oracle and DBI for starters Send me the output from the dbd_verbose=>15 so I can check a few things cheers John > 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); > > $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. > Please consider the environment before printing this email.
trace.log.gz
Description: Binary data
NodeData.pm.gz
Description: Binary data