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

Hi Norman

Although, the versions being talked about in the thread in the below mentioned URL is different, I did notice that the core issue is quite close (ie.. 2GB limit).

Plus, it is on hp-ux 11.11, same as yours.

http://www.orafaq.com/maillist/oracle-l/2003/12/23/1651.htm

-Rajeev

On 12/20/06, Jason Heinrich <jheinrich_at_pcci.edu> wrote:
>
> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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