Re: Disappearing Sessions - Part 3

From: sybrandb <sybrandb_at_gmail.com>
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 DBA
Received on Thu Feb 14 2008 - 03:43:24 CST

Original text of this message