DBD::Oracle dbd_st_execute slow speed

[email protected]
Newsgroups perl.dbi.users
Message-ID <OF6A5D859E.1A0823FE-ON862577F4.0053177B-862577F4.0055E6F6@discover.com>
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.
trace.log.gz (application/octet-stream, 2 KB) - not displayed
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.