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.
