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: Alex Filonov <afilonov_at_yahoo.com>
Date: 24 Feb 2003 11:21:30 -0800
Message-ID: <336da121.0302241121.2bde6bfc@posting.google.com>


anton_yulo_at_genealogy.com (Anton Yulo) wrote in message news:<29b77ac.0302211803.4731ff8f_at_posting.google.com>...
> 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.
>

How big is the table growing? BTW, PL/SQL tables were real memory hogs in 7.3 and 8.0. Situation improved a lot in 8.1. Look up documentation to estimate the amount of memory your PL/SQL table is taking. For 7.3 and 8.0 it was something like 256 bytes for every number variable and 2000 bytes for every varchar2, no matter what their acutal size was. (I might be wrong, look in documentation). Any OS restrictions on the amount of memory per 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:21:30 CST

Original text of this message

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