Re: Session PGA memory max exceeded 2 GB and crashes.
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 21 Dec 2006 08:53:44 -0000
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;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
