if you run that query in sqlplus -- what are the results (how many rows) and show us the entire piece of PRO*C code, include things like "my version", "my platform"

Otherwise this question fits into the "my car won't start -- why?" category ;)

correct -- it will do it "automagically"

this subroutine:


void process()
{
exec sql begin declare section;
int x;
exec sql end declare section;

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
exec sql declare c cursor for select rownum from all_objects where rownum <= 1000;
exec sql open c; for( ;; )
{
exec sql whenever notfound do break; exec sql fetch c into :x;
}
exec sql whenever notfound do continue;
exec sql close c;
}


when executed without setting prefetch:


select rownum from
all_objects where rownum<=1000

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 501 0.23 0.33 8 4560 4 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 503 0.26 0.36 8 4560 4 1000


shows the default is 2 -- with prefetch=100

select rownum from
all_objects where rownum<=1000

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11 0.17 0.17 0 4075 4 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.17 0.17 0 4075 4 1000

showing the reduced fetch calls...

you can array fetch -- so to retrieve 1000 rows you could either

o fetch a row at a time, 1000 fetches OR
o fetch N rows at a time, 1000/N fetches (eg: n = 100, you'd fetch 10 times instead of 1000 times)


you can array insert/update/delete as well.

say you have 1000 rows to insert.
you batch them up into an array of 100 elements at a time.

You'll EXECUTE your insert 10 times, inserting 1000 rows.


so, array processing is a method to "batch things up"

memory, choke points (hey, lets flood the network with 500megabytes right now), latency (why does it take 30 seconds to get that first row, I only care about 10 of them initially), etc.


Yes, you'll find diminishing marginal returns for large array sizes -- I have an analogy in Effective Oracle by Design with regards to a wheelbarrow, a big pile of bricks and two people wanting to pave a driveway (regards of Connor McDonald whom I believe developed the analogy in the first place as I recall...)

bringing over a brick at a time (row by row) is slow. lots of wait times, not processing anything.

bringing over the entire stack of bricks is fraught with danger (tipping over), and because the thing weighs so much, takes a long time. So, for a long time, wheelbarrow person works, paver sits. then paver works and wheelbarrow guy does nothing.

brining over a nice pile to get started and going back and forth and back and forth keeps things flowing nicely. no one gets stressed out, no one gets hurt.

hold_cursor=yes release_cursor=no prefetch=20


with hold cursor no, release cursor yes, prefetch defaulted:


select user_id
from
t where user_id>:b0


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10000 0.19 0.17 0 0 0 0
Execute 10000 0.53 0.35 0 0 0 0
Fetch 110000 2.50 2.41 1 120000 0 190000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 130000 3.22 2.94 1 120000 0 190000



with hold_cursor=yes, release cursor = no, prefect=20

select user_id
from
t where user_id>:b0


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.39 0.39 0 0 0 0
Fetch 20000 1.18 1.00 1 40000 0 190000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30001 1.57 1.40 1 40000 0 190000



Ask the coders to take a peek at the pro*c precompile options and see if any of them might be "interesting"


You have a big honking machine there, you probably try to be very scalable. Parsing is death to scaling.


The more you parse, the less you parse.....

(sounds counterintuitive but it is true -- the more you try to parse, the less you can parse, the lines get longer and longer and longer and longer....)

you explained it very well? what more need be said?

why not erase the code and use dbms_scheduler.

it can run external things
has a history/audit trail built in
is already very multi-threaded

use dbms_job and a java stored procedure to run host commands.

dbms_job can do upto 1,000 things at a time.

You'd don't get the features of the scheduler (better intervals/schedules and audit), but you can more easily do that (build your own audit) than attempt to make a multi-threaded pro*c app.



That would be my first choice in 9i - dbms_job.


I would not go multi-threaded in the C program, if I was to stick with the C code (and I would not), I would have it use AQ (advanced queues) and instead of you sticking just into a do it yourself queue (you'd have to figure out how to handle concurrency on that one - AQ does that for you) have the requestor put it into a real queue.

And then just start N of your C programs, each will attempt to dequeue a message - if you start 5 and there are 10 messages, each will get one of the first five, process it and then get the rest...



That would be 2nd choice.




A cheap way to do it would be to simply grab a row, fork/exec the program to deal with it.