Norman, Are you able to modify this code? Using MONTHS_BETWEEN in your WHERE clause is forcing the full table scan, which, I suspect, is causing a large sort in-memory. Placing an index on LAST_USED_DATTIM and rewriting your WHERE clause thusly: WHERE LAST_USED_DATTIM < ADD_MONTHS( SYSDATE, Pk_Utils.fnc_get_sys_par('HOUSE_AUDIT') * (-1) ) would avoid both the scan and the sort. I don't know if this will solve all of your PGA usage, but it should help. On 12/20/06 7:57 AM, Norman Dunbar wrote: > The DELETE does a full table scan of a table with 1,527,000 rows of > average length 102 bytes. The table has 22,008 blocks (8K block size) > and 6 free blocks and was analysed earlier this week - even with my > limited arithmetical abilities, that's a grand total of approx 150MB of > data (as per rows * length) or approx 175 MB if you take blocks * block > size. However, that will be part of the buffer cache and noty in the > PGA. > > Obviously, there will be a cursor opened in the PGA but I cannot see > why it would need over 2 GB. The delete uses no UNDO at all - there are > no rows selected for deletion. > > 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; > > In the WHERE clause above, is the PK_UTILS package being called for > each row or just once. 10046 trace didn't give any useful help. (Nor did > single stepping the code in TOAD - it executed as one statement, the > DELETE.) > > The PK_UTILS code makes two calls to PK_TRACE internally and this > simply writes a record to a logfile using UTL_FILE. > > This is about step 7 of 10 in the houskeeping code and everything > before it uses MONTHS_BETWEEN etc as above - different tables are > involved along with a different parameter from the fnc_get_sys_par call. > That's all. So far, nothing is actually being deleted as we have not hit > anything older than the retention period in any of the preceeding or > following DELETEs. --------------- Jason Heinrich Oracle Database Administrator Pensacola Christian College -- //www.freelists.org/webpage/oracle-l