Re: Question about fetch huge number of records!

"music4" <music4_at_163.net> wrote

> 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.

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?

> 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).

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?

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

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.

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

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