Re: DBD::Oracle dbd_st_execute slow speed

  On 09/12/2010 11:59 AM, [email protected] wrote:
> List,
>
>          I'm not sure if this is relevant but I recently loaded 2 million
> rows into a parent table NDE_DATA_HIST then issued a DELETE FROM to delete
> all of the rows. I dont have access to TRUNCATE(). The table now has only
> about 200 rows. However, the 2 other tools are still fast, its just DBI
> that is slow so I dont think its related.
>
>
>
Ha! I very much think so. ;)

Most likely the index of the 2000000+ is still being updated

Rebuild the indexes and then get back to me

cheers
John

> "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_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.
>>
> Martin