Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Closing Cursors
Hi.
Is it possible for you to completly avoid alloc/dealloc of PL/SQL tables ? ( Define them at package level and using counter to keep the actual number of rows in table for each iteration of main cursor. You can try NOCOPY parameter as well. ) In my experience the process of alloc/dealloc is quite CPU expensive. If as Jonathan suggested you may have a memory leak then avoiding it may improve things.
HTH. Michael
In article <38b18852.132395_at_news.freeserve.net>,
news_at_abc.co.uk (Peter L) wrote:
> On Mon, 14 Feb 2000 19:42:18 -0000, "Jonathan Lewis"
> <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> >
> >You may have a memory allocation/release
> >problem with your PL/SQL tables.
> >
> >I always (when I remember) declare two of
> >every type of table:
> >
> >declare
> > ......
> > table_to_work_with my_table_type;
> > empty_table my_table_type;
> > .....
> >begin
> > .....
> >
> > -- do the work with the real table
> > -- make sure the memory is freed
> >
> > table_to_work_with := empty_table;
> >
> >
> >end;
> >
> >This shouldn't be your problem, but there
> >are more bugs in heaven and earth, Horatio ...
> >
> Tried this method, with the empty table making sure the memory is
> released, over the weekend. The good news is the memory usage seems to
> be OK. The bad news is the code now processes records at roughly half
> the speed it did before. It shouldn't run into the problem with
> performance dying after a few hours but I was wondering if there is
> any way to keep the program working at full speed. Is there a large
> overhead in the above method or am I missing something.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Feb 22 2000 - 15:04:24 CST