Disappearing Sessions - Part 3

Oracle 10.2.0.3.0
Windows Server 2003
16GB Memory

I have a cursor which returns approximately 35 million rows of data. I then process that data
as follows:

  LOOP     BULK FETCH 100,000 rows

    Loop thru retrieved rows 1st time

        if identified as measure #1 add element to measure #1 nested table
        if identified as measure #2 add element to measure #2 nested table
        if identified as measure #3 add element to measure #3 nested table
        if identified as measure #4 add element to measure #4 nested table
        if identified as measure #5 add element to measure #5 nested table
        if identified as measure #6 add element to measure #6 nested table
        if identified as measure #7 add element to measure #7 nested table

        bulk insert nested tables into their respective database tables

        clear memory used by nested tables

    Loop thru retrieved rows 2nd time
        if identified as measure #8 add element to measure #8 nested table
        if identified as measure #9 add element to measure #9 nested table
        if identified as measure #10 add element to measure #10 nested table
        if identified as measure #11 add element to measure #11 nested table
        if identified as measure #12 add element to measure #12 nested table

        bulk insert nested tables into their respective database tables

        clear memory used by nested tables

    Loop thru retrieved rows 3rd time
        if identified as measure #13 add element to measure #13 nested table
        if identified as measure #14 add element to measure #14 nested table
        if identified as measure #15 add element to measure #15 nested table
        if identified as measure #16 add element to measure #16 nested table
        if identified as measure #17 add element to measure #17 nested table

        bulk insert nested tables into their respective database tables

        clear memory used by nested tables

    Clear memory used by bulk fetch nested table

  END LOOP Sometimes this process runs to completion. Other times it "disappears" after anywhere between
100,000 rows and 34 million rows processed. Sometimes there are other processes running at the same
time this one is while other times there are no other processes running.

I have literally run the process to completion and then run it again and it will just disappear.

I'm forcing a full table scan because if I try the FIRST_ROWS hint it consistently dies after only
100,000 rows and other access plans seem to have the same result. The only access path I've managed
to get to run to completion (so far) has been the full table scan.

Does anybody see anything glaringly "wrong" with the logic above? Not every row retrieved will be
identified by a measure. Some would only be identified once and others may match more than one
measure. This has taken a 17 hour process down to only 2 hours.

We have 16GB of memory. Each of my nested table rows only measures 122 bytes. I have not received
ANY out of memory errors or any other type of process/system errors. The process simply ... stops. There
seems to be no event logged causing this but something has to be causing this.

From what I can tell through EM we have three disk/mount points and all three have triggered warning
alerts for low disk space (one is critical with less than 4% free). We also constantly have excessive
virtual memory paging. Is it possible that this process is paging memory and there simply isn't enough
disk space for the virtual memory and it causes the process to disappear without any trace? Received on Wed Feb 13 2008 - 19:28:38 CST