Re: pl/sql tables memory usage

Richard Kuhler wrote:

> You can see the TOTAL PGA and UGA memory being used by your session by
> looking at v$sesstat ...
>
> select class, name, value
> from v$sesstat, v$statname
> where v$sesstat.statistic# = v$statname.statistic#
> and sid = (
> select sid
> from v$mystat
> where rownum = 1)
> and name like 'session%memory%'
> order by class, name
>
> Index by tables are allocated from PGA so you can check that value to
> monitor allocations.
>
> I don't know of any way to get the remaining memory figure from the
> database (it's an OS level quota you're hitting). I'd probably just
> figure out how much PGA was allocated before the error occurs and then
> back down from that figure.
>
> Richard
>
> Anton Yulo wrote:
> >
> > Hi all,
> >
> > I've tried scouring the groups for more info on this but i couldnt'
> > find anything too useful and decided to post after a while. please
> > feel free to direct me to another thread if you know of one.
> >
> > intro: am a novice user to pl/sql. i believe we're running what
> > appears to be oracle 8.1.7.
> >
> > we're running a large batch process that tries to keep track of
> > information in a pl/sql table. the table's index is unique ID to a
> > user and we use the table to save us the time of having to requery the
> > information each time for that user during the batch process.
> >
> > the problem i'm running into is that somewhere, after a bit of running
> > it, we get the "ORA-04030: out of process memory when trying to
> > allocate..." error. this would happen during an insert statement which
> > kinda makes me think the rollback space is tied to the space used by
> > the pl/sql index-by tables. i have to believe it's the tables because
> > emptying out the table every now and then (eg. everytime it gets to a
> > certain size) seems to alleviate this issue.
> >
> > so i was wondering how to monitor the memory usage of this table. i'm
> > hoping to get two bits of info:
> > - how much memory i have left to use before i'm likely to get the out
> > of memory exception
> > - how much memory it is using
> > using this info i was hoping to monitor the memory while the process
> > was running, and manage the size of the table to avoid the exception.
> >
> > any advice would greatly be appreciated. thanks.

And you have access to unlimited documentation if you can post to c.d.o. usenet groups. Go to http://tahiti.oracle.com.

Daniel Morgan Received on Mon Feb 24 2003 - 14:21:42 CST