Re: Session PGA memory max exceeded 2 GB and crashes.


Norman,

In general you can expect the function to be called
twice if you use it to identify an indexed access path
(as in your later experiment, perhaps).
   id > function(const)
However, for a tablescan - which this seems to be - the function is called once per row. (Until 10g where 
deterministic functions nearly work).

I would guess that you have a memory leak in the pl/sql.

Change the code to:
WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) > (select Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT') from dual) 

and I think scalar subquery caching will probably kick in -
so you should only call the function once, and bypass any
leakage problem.  You'll also save a lot of CPU too, probably.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



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

Date: Wed, 20 Dec 2006 13:57:04 +0000
From: "Norman Dunbar" <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>
Subject: Session PGA memory max exceeded 2 GB and crashes.


Afternoon (UK time) all,

Oracle 8174. (I know, I know !)
HPUX 11.11.


BEGIN
    Pk_Trace.ENTER ( 'Delete HAZ_RGBA_REG_BATCH_AUD records' );
DELETE HAZ_RGBA_REG_BATCH_AUD
WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) > Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT'); 
COMMIT;
Pk_Trace.LEAVE ;
EXCEPTION
    WHEN OTHERS THEN
       Pk_Trace.REPORT_ERROR;
       RAISE;
END;




--
http://www.freelists.org/webpage/oracle-l

Other related posts: