Re: DBD::Oracle dbd_st_execute slow speed

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");




"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
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com




Please consider the environment before printing this email.