Re: DBD::Oracle dbd_st_execute slow speed

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 <[email protected]> 
12/09/2010 10:30 AM

To
<[email protected]>
cc

Subject
Re: DBD::Oracle dbd_st_execute slow speed






  On 09/12/2010 10:37 AM, [email protected] 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_USER@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.