Re: Disappearing Sessions - Part 3
Date: Thu, 14 Feb 2008 01:43:24 -0800 (PST)
Message-ID: <dfbea751-20f3-4936-856c-243c680a9973@q65g2000hsd.googlegroups.com>
On Feb 14, 2:28 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> 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?
Dump this crap and move your logic to SQL.
You should not do in PL/SQL what you can do in SQL. You can do this in
SQL.
A cursor returning 35 million rows... Utterly ridiculous.
You are working with a *relational database*, and you are treating it
like a flat file system.
Go figure.
-- Sybrand Bakker Senior Oracle DBAReceived on Thu Feb 14 2008 - 03:43:24 CST