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

Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql tables memory usage

Re: pl/sql tables memory usage

From: Richard Kuhler <noone_at_nowhere.com>
Date: Mon, 24 Feb 2003 19:55:09 GMT
Message-ID: <xMu6a.147034$ce4.39704402@twister.socal.rr.com>


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.
Received on Mon Feb 24 2003 - 13:55:09 CST

Original text of this message

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