Re: BULK Processing Process "Expires" without completing

Dereck L. Dietz wrote:
>
> Oracle 10.2.0.3.0
> Windows Server 2003
> Enterprise Edition
>
> I have a process where I have a cursor which returns approximately 36
> million rows which I have to check against certain criteria to load into 25
> possible tables (so far).
>
> I'm doing bulk reads (limit of 50000 and 25000 have both been used) and
> FORALL inserts. I've experienced this problem with both limits - a limit of
> 25,000 seems to take a bit longer to complete (when it does) than 50,000 but
> they both have had problems with the process disappearing.
>
> After the bulk inserts I have 25 lines similary to: nested_table_name.DELETE
> to free up the memory before the it loops through and retrieves the next
> 25,000 or 50,000 rows.
>
> It seems that whenever my process gets to where it has processed about 30
> million rows it just stops processing.
>
> I'm going to have more tables to load - I don't want to do what the current
> process does (which takes over a day to run) and read the source table over
> and over again for each table to load when I can read once write many times
> much quicker.
>
> Is it possible that somehow I'm running out of memory and not completely
> releasing the memory with the "nested_table_name.DELETE"s I'm doing?
>
> I know this may be construed as possible double posting since this ties in
> with my other post about processes expiring but I'm wondering if there is
> anything I can do in my package to reduce memory usage.
>
> I should also point out that up to now nobody has tried to write packages
> against this database and I'm not completely sure the database is configured
> for using packages. There are other people who are writing PL/SQL code
> instead of just SQL scripts who are experiencing the same disappearing
> processes as I am.
>
> Any help would be appreciated.

Try looking at v$sesstat from another session to see how much PGA you are soaking up.

There used to be an old adage about assigning an empty plsql table was better at releasing memory then xxx.delete, but I've never really tested that hypothesis.

Can you not achieve the same with just plain old SQL, or maybe a pipeline function

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------