Re: DBD::Oracle dbd_st_execute slow speed

For the sqlplus its a constant statement. I didn't know you could bind 
variables in sqlplus but I'll try. I suspect it will still be fast.

INSERT INTO table (column) VALUES (value);

For the DBD::Oracle I've done both

prepare(INSERT INTO table (column) VALUES (?))
execute($value)

and

do('INSERT INTO table (column) VALUES (value);')

Both the execute against the prepared statment and the do() with the 
constant SQL statement take about the same time. 




Martin Hall <[email protected]> 
12/13/2010 11:35 AM

To
<[email protected]>
cc
John Scoles <[email protected]>, "Martin J. Evans" 
<[email protected]>, <[email protected]>
Subject
Re: DBD::Oracle dbd_st_execute slow speed






  I take it you are comparing like for like with the Plus or Developer 
script?  By that I mean, using bind variables as well.  Or, have you 
tried this piece of Perl with some hardcoded values?  I just wonder if 
it's the binding that's taking the time.

Cheers

(another) Martin

On 13/12/2010 17:04, [email protected] wrote:
> Martin, John, list
>
>          My DBA ran an analyze table, looked at the indexes and server
> load, and confirmed its not an 'Index Organized' table.
>
>          Yes, its just the execute() that takes 4 seconds not the commit
> which makes the problem even more bizzarre. I would expect the opposite.
>
>          The slqplus script and the perl program were run from the same
> server so that eliminates that. The SQL Developer statement was run from
> my local machine.
>
>          I've been able to reproduce the behavior on 2 different 
servers.
> On both servers sqlplus is fast while DBD::Oracle is slow. Both connect 
to
> the same oracle database and same schema and tables.
>
>          Any ideas list? I like the direction the threading idea was 
going
> but  unless someone can re-explain it to me I think the fact that only 
the
> execute() is slow and not the commi() it doesn't make sense. sqlplus's
> execute would be on my thread and should take just as long as 
DBD::Oracle.
>
>          Thanks for your help but the DBA and I are fresh out of ideas 
and
> performance is still terrible :(
>
>          Server 1:
>                  AIX 6.1
>                  Perl (v5.12.2)
>                  $DBI::VERSION = "1.615"
>                  $DBD::Oracle::VERSION = '1.26'
>          Server 2:
>                  AIX 5.3
>                  Perl v5.8.8
>                  $DBI::VERSION = "1.51";
>                  $DBD::Oracle::VERSION = '1.17';
>
>
>
>
> John Scoles<[email protected]>
> 12/09/2010 11:52 AM
>
> To
> <[email protected]>
> cc
>
> Subject
> Re: DBD::Oracle dbd_st_execute slow speed
>
>
>
>
>
>
>    On 09/12/2010 12:24 PM, Martin J. Evans wrote:
>> 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?
>
> One other thing to remember is that sqlplus is threaded for each command
> while DBD::Oracle is not.
>
> So in sqlplus the commit may be on a different thread so it be quick to
> return even if the commit hits a 2meg index regenerate.
>
> Do you know if the execute or the commit is taking a long time in
> DBD::Oracle??
>
> One other thing to think of check with your DBA that your table is not
> an 'Index Organized Table' that will really bugger you up if you have a
> big index
> and few rows
>
>
>> Martin
>
>
>
>
> Please consider the environment before printing this email.
>
>





Please consider the environment before printing this email.