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_at_environment-agency.gov.uk>
> 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