Poor stored procedure performance using varchar2 parameters

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) Received on Wed Oct 03 2001 - 21:41:26 CEST