Can anyone help/explain Proc*C PL/SQL table procedure call, slow to start and stop.

I am running a pro*C application against an oracle 8.1.6 database (I have also tested against an oracle 8.1.7 database) Compiled with Pro*C/C++: Release 8.1.6.2.0

Both of these show similar results.

The application does something along the lines of

<<In the c code>>

1. Print current time, "Start"

2. Processes a file, extracting certain records and storing the data into arrays

     each record containts 60 fields, a mixture of int's, doubles and char [...] (1 record is 565 bytes in size)

     The test file I am loading has about 12,000 records of which about 9800 meet the criteria

3. Print current time, "File Processed"

4. Call an oracle procedure stored in a package, passing in the arrays

<<Into the PL/SQL Procedure>>

5. In the procedure (Which takes 60 PL/SQL tables index by binary_integer. The parameters are passed IN only)

6 Log the current time, "Into PL/SQL procedure"

7 Use a forall ... insert to insert the data.

8 Log the current time, "Data Inserted"

<<Back to c code>>

9 Print the current time, "Back from PL/SQL"

The program is slightly different in that I can control after how many rows read from the file, the procedure call is invoked. (So it can process in batches)

If I set the batch size (array element size) to 10,000 elements (so all the elements fit in one batch)

Looking at the times, program execution goes something like:

10:00:00 Start
10:00:02 File Processed

10:00:13 Into PL/SQL procedure
10:00:30 Data Inserted

10:00:40 Back from PL/SQL

For some reason it take 11 seconds to call the Oracle PL/SQL procedure. (11 seconds from my c printf to the start of executing my first PL/SQL line) OK, I know it has to transfer the data to the database but 11 seconds seems a little long.

And the return from my last line of PL/SQL back to my C application takes 10 seconds. (10 seconds from my last PL/SQL line to my next line of c)

• That means it takes 21 seconds to enter and exit the PL/SQL block and 17 to actually do the insert. ***

I thought It might be transfering the arrays back to my C application, (I did set them to IN - but the entry/exit times looked similar) so I changed my procedure to IN OUT, then did a var.DELETE on each parameter but this made no difference.

The file and data base are stored on one machine, with the program executing on another.
The program processes the file in about 2 seconds, so its not the network bandwidth.

I have used various batch sizes, 500, 1000, 2000, 5000 & 10000 and they all yield total execution times about the same.

At the moment the PL/SQL code is quite simple, just an insert. But in the proper application it will be more complex. i.e. I cannot just do a Pro*C array insert.

Any ideas why this takes so long? even better, any ideas how I could fix/improve things?

Cheers, thanks for any help,

     Gary. Received on Tue Apr 16 2002 - 14:58:05 CDT