On 09/12/10 16:52, ericbamba...@discover.com 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" <martin.ev...@easysoft.com>
> 12/09/2010 10:33 AM
>
> To
> <dbi-users@perl.org>
> cc
>
> Subject
> Re: DBD::Oracle dbd_st_execute slow speed
>
>
>
>
>
>
> On 09/12/10 15:37, ericbamba...@discover.com 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
