Re: bulk collect memory leak

"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 --