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.
trace.log.gz
Description: Binary data
