Greetings,

I am developing an application using C++, and using OCI to access Oracle
database.

One process read data from a table, let' call it TA, and handle each record
read from TA. The code is like following:

select fields from TA;
set prefetch rows to 200.
loop
fetch a record
handle the record
sleep a while
end loop

For some reason, I have to slow down the speed of fetch, so I have a "sleep"
in the loop. So that, each second, only about 20 records are fetched.

But sometime, TA initially contains a huge number of records (more than 400k
records), so the fetch will take a very long time (several hours).

MY QUESTION IS: Will that cause server resource (memory, CPU) are seized for
the selection??? Since server need to keep the selection result.

If this is not a good way, do you have an alternation?

Thanks in advance!
Evan

Use the array interface and fetch them 200 at a time with no sleep between.
I've never had to do that. The other day I did a similar thing but with an
insert. I used the array interface and inserted almost 500 rows at a shot
very quickly. (under .05 of a sec) The rows were very narrow.(1 sequential
number and a text string of 15 bytes)
Jim

--
Replace part of the email address: kennedy-down_with_spammers (AT) attbi (DOT) com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"music4" <music4 (AT) 163 (DOT) net> wrote

"music4" <music4 (AT) 163 (DOT) net> wrote

I don't get it. You slow down the loop with a sleep for some "unknown
reason" and then you complain about the speed of the loop?

The fetch depends on the SQL you use. Are you fetching all the records
sequentially from the table? Why all the rows? Is there a where
clause? If not, why not? Did you explain plan and tkprof it?

Each Oracle session consumes resources on the server. Be that in
dedicated or MTS mode.

However, resource usage equates to two basic things:
- Oracle configuration (i.e. the init.ora file)
- What the session is doing (i.e. the SQL/SQLs it uses)

BEFORE mucking about with the first, make sure that the latter does it
correctly.

Not sure how to put this politely Evan, but do you know what the heck
you're doing?

Oracle is not something like a normal data file or even ISAM file..
you can not treat it like one and expect good performance.

Data *MUST* be processed *INSIDE* Oracle and not outside. I.e. it is
stupid fetching a million rows into an application and processing the
data there, and then throw the results back at Oracle.

Process the data as data sets in Oracle. Oracle is... it feels like
I'm repeating this too often,.. Oracle is a DATA PROCESSING PLATFORM.
It is _not_ a file system where you need to fetch a bunch of rows for
processing in a C++ application.


--
Billy