Re: Cursor and Memory problems

You can get all the old postings at www.dejanews.com.

On Wed, 19 Sep 2001 14:12:58 -0700, "Venki Ramachandran" <venkir_at_wacsinc.com> wrote:

>Hi All,
>
>My application has to load 5 million records into a temp table everyday.
>After it loads it has to process all the records, multiple records for
>the key field (for e.g. SSN #) and squish them into one record per key
>and insert into another table. I know this is an ideal scenario for the
>latest 9i ETL (Extraction, Transformation and Loading) but we are using
>8.1.7. My question is that I have a cursor that process all the 5
>million records and inside the FOR Loop of the cursor all the logic to
>compress multiple rows for the same key into one key takes place. It
>currently takes 5 hours to process all the records on my dev machine. I
>have tuned every other SQL but the one that opens the cursor, Explain
>plan says that it is a FULL TABLE SCAN, as expected. How does cursors
>work while fetching a large number of rows.
>
>My understanding was that it would do a full table scan once, retrieve
>the number of records that can fit into memory, establish a watermark at
>that level, process the records and then retrieve the next set of rows
>from the disk into memory. Am I correct in that assumption? If So, how
>can I speed it up. I need to reduce the number of hours to 3 hours. I
>increased the db_block_buffers parameter to 3 times its original size
>with no impact on performance. Any help will be appreciated?
>
>On a separate note, Does anyone know how I can get all the postings in
>this newsgroup in a CD. I know most of the questions I have, have been
>asked by someone else already. The old postings are either gone or my
>machine becomes too slow when I download all the headers.
>
>Thanks a lot, Venki
>
>
>
Received on Thu Sep 20 2001 - 17:43:06 CDT