Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Poor stored procedure performance using varchar2 parameters

Re: Poor stored procedure performance using varchar2 parameters

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 03 Oct 2001 23:36:50 +0100
Message-ID: <3BBB9302.15E3@yahoo.com>


Bozo wrote:
>
> Does anyone have an explanation for this:
>
> Our application generates A LOT of data and can continuously call a stored
> procedure to insert data into our Oracle 8.1.5 database (Win2000) as fast as
> the database can handle it. It can make around 500 stored procedure calls
> per second when using SQL Server 2000, but we only get around 100 calls per
> second using Oracle, even if we turn off commits.
>
> Our code to execute the stored procedure uses OCI and essentially makes 3
> calls:
>
> oparse() to parse a PL/SQL block that looks like:
> BEGIN ProcName(1,'ABC',:ret1,:ret2); END;
> oexec() to execute the parsed statement
> ocom() to commit (we've disabled this and it doesn't help performance)
>
> After some research, we determined the real bottleneck is not the oparse()
> or oexec() call, but whether the stored procedure uses varchar2 parameters.
> We changed our stored procedure to do practically nothing and it looks like:
>
> CREATE PROCEDURE ProcName
> (param1 number,
> param2 varchar2,
> param3 OUT number,
> param4 OUT number
> )
> IS
> begin
> update table set keycolumn=param1, textcolumn='FIXEDTEXT'
> where keycolumn=param1;
>
> param3 := 1;
> param4 := 0;
> end;
>
> With this change (and removing commit calls to ocom()), we still don't get
> more than 150-200 S/P calls per second.
>
> HOWEVER.... if we remove the 'param2 varchar' declaration (or change it to a
> 'param2 number'), then performance increases up to around 750-900 S/P calls
> per second!! We're not even USING the varchar2 parameter, yet the
> declaration for it seems to drastically impact performance.
>
> We're not using packages or user-defined datatypes, just tables and stored
> procedures, Oracle 8.1.5 for NT, and OCI. We haven't tried bulk loads using
> arrays, but that's next.
>
> Any explanation for this, or recommendations to increase performance? I
> can't believe that SQL Server can outperform Oracle by a 3:1 margin!
>
> Thanks,
> Doug Piercy (songbird_at_pacifier.com)

It's not in the PL/SQL code - I tried two procs equivalent to yours (one with numeric, one with varchar2) and got about 2500 per sec from each (the update was on an empty table)...

Maybe throw a trace on and see if the relationship between OCI and PL/SQL throws out any strange SQL's that are slowing things down

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
Received on Wed Oct 03 2001 - 17:36:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US