Martin,
Most peices of the information you request are in my original
email and in the trace file in my original e-mail. Perhaps it was too
information dense.
-Oracle database version
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
-which Oracle client libs are you using and what version
What is the best way to determine this?
-DBI version
$DBI::VERSION = "1.615"
-DBD::Oracle version
$DBD::Oracle::VERSION = '1.26';
-Perl version
This is perl 5, version 12, subversion 2 (v5.12.2) built for
ppc-thread-multi
"Martin J. Evans" <[email protected]>
12/09/2010 11:24 AM
To
<[email protected]>
cc
Subject
Re: DBD::Oracle dbd_st_execute slow speed
On 09/12/10 16:52, [email protected] wrote:
> Martin,
>
> do() takes 4 seconds as well. Sorry about the SQL_INTEGER junk.
I
> was testing things. It takes the same amount of time if I leave off the
> types. I remember seeing the trace show it bind as VARCHAR or something
> when I leave off the type and it was still slow.
>
> Here is the code snippet I used with do(). The script printed:
>
> Do TOOK 4 seconds
>
> my $do_q = "INSERT INTO FL_SYS_STAT_HIST
> (ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT)
> VALUES
>
(".$_->get_id().",".$s->{id}.",".$_->get_free_space().",".$_->get_used_space().")";
>
> $start = time();
> $s->{_dbh}->do($do_q) or
> die("Do failed: $DBI::errstr\n");
> $end = time();
> print STDERR "Do TOOK ".($end-$start)." seconds\n";
> die("Dying for do()\n");
>
>
There are still things you've not told us:
Oracle database version
which Oracle client libs are you using and what version
DBI version
DBD::Oracle version
Perl version
However, the only time I've seen something like this it was index related
and yet you maintain sqlplus does not suffer from the same issue. One time
it was missing indexes and the other it was Oracle deciding not to use the
index because of data type mismatches.
Is there perhaps a difference in how you are connecting between
DBD::Oracle and sqlplus? Was sqlplus command and Perl script run from same
machine?
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
> "Martin J. Evans" <[email protected]>
> 12/09/2010 10:33 AM
>
> To
> <[email protected]>
> cc
>
> Subject
> Re: DBD::Oracle dbd_st_execute slow speed
>
>
>
>
>
>
> On 09/12/10 15:37, [email protected] 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.
