Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: bulk collect memory leak

Re: bulk collect memory leak

From: Aleksey Kochetov <kochetov_at_optonline.net>
Date: Fri, 25 May 2001 17:05:31 GMT
Message-ID: <vhwP6.60278$MR1.9461816@news02.optonline.net>

It works a bit better. Now it allocates ~32M (it's not good), but the second call does not increase allocated memory size (that's good news). It seems Dbms_Session.Free_Unused_User_Memory does not work (i tried it before your suggestion).

DECLARE
  TYPE strings IS TABLE OF VARCHAR2(32000)     INDEX BY BINARY_INTEGER;
  lv_dummy strings;
BEGIN
  FOR i IN 1..1000 LOOP
    SELECT dummy BULK COLLECT INTO lv_dummy       FROM dual WHERE dummy IS NULL;
    lv_dummy.DELETE;
    Dbms_Session.Free_Unused_User_Memory;   END LOOP;
  lv_dummy.DELETE;
  Dbms_Session.Free_Unused_User_Memory;
END;
/

Thank you.

"Mark D Powell" <mark.powell_at_eds.com> wrote in message news:178d2795.0105250459.762ad81b_at_posting.google.com... > "Aleksey Kochetov" <kochetov_at_optonline.net> wrote in message  news:<ABkP6.58958$MR1.8774184_at_news02.optonline.net>...
> > I am a contracter and an official way is not short for me. "BULK
 COLLECT" is
> > a great feature for performance improvement and i actually need it.
> > About error messages:
> > HP: ORA-04030: out of process memory when trying to allocate 25520 bytes
> > (callheap,bind var buf)
> > Linux (in alert_SID.log): ORA-00600: internal error code, arguments:
> > [plio.c: non-reus], [1], [], [], [],..... and server crashed :)
> >
> > "Spencer" <spencerp_at_swbell.net> wrote in message
> > news:71jP6.139$ll2.6594_at_nnrp1.sbc.net...
> > > have you requested assistance from oracle support with this
> > > particular issue ?
> > >
> > > the error message you got was "out of memory error" ?
> > > with no oracle error message id ?
> > > any entry in the alert log ?
> > > any trace files in bdump or udump directories ?
> > >
> > > one possible workaround i would suggest would be to avoid
> > > using the BULK COLLECT syntax.
> > >
> > > "Aleksey Kochetov" <kochetov_at_optonline.net> wrote in message
> > > news:92hP6.56673$MR1.8328698_at_news02.optonline.net...
> > > > Hi.
> > > > I use bulk collect in a stored procedure. I got out of memory error.
 I
> > > > reproduced it for 8.1.6.2 (HP & Linux) and 8.1.7 (HP). Somebody
 knows?
 Does
> > > > Oracle have a fix?
> > > >
> > > > CREATE OR REPLACE PROCEDURE stress_test IS
> > > > TYPE strings IS TABLE OF VARCHAR2(1000)
> > > > INDEX BY BINARY_INTEGER;
> > > > lv_dummy strings;
> > > > BEGIN
> > > > FOR i IN 1..10 LOOP
> > > > SELECT dummy BULK COLLECT INTO lv_dummy
> > > > FROM dual WHERE dummy IS NULL;
> > > > lv_dummy.DELETE;
> > > > END LOOP;
> > > > lv_dummy.DELETE;
> > > > END stress_test;
> > > > /
> > > >
> > > > BEGIN
> > > > FOR i IN 1..10000 LOOP
> > > > stress_test;
> > > > END LOOP;
> > > > END;
> > > > /
> > > >
> > > > SELECT * FROM v$sesstat t1, v$statname t2
> > > > WHERE name IN ('session uga memory','session pga memory')
> > > > AND t1.statistic# = t2.statistic#
> > > > AND sid = 14;
> > > >
> > > >
> > > >
> > > >
> > > >
> > >

> I am not sure this is a bug, but rather it may be a problem with the
> application design.  The procedure allocates a pl/sql table every time
> it is called.  It then loads 1 row into it and then proceeds to delete
> the one row, but not the plsql table.  The plsql table still exists
> when you exit the routine.  I believe that plsql tables exist for the
> life of the session but because it is not being passed there is no way
> to access it.  And a new one is being created on procedure entry every
> time.  Try adding a call to
> dbms_session.free_unused_user_memory to the stress procedure and see
> if the problem goes away.
>
> I would think for the example to be a more realistic use that the
> array would need to be created in one procedure and passed as a
> parameter to the procedures that are going to use it.
>
> -- Mark D Powell --
Received on Fri May 25 2001 - 12:05:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US