pl/sql tables memory usage
Anton Yulo
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.
DA Morgan
It seems highly unlikely that this table is the issue if you are using it
as you seem to be. Can you provide the structure of the table and the
number of rows? If so ... why not a simple byte calculation. But, as I
indicated, I highly doubt this is the issue. Though you might want to look
at your initSID.ora parameters.
To find your Oracle version ...
SELECT version
FROM v_$instance;
Daniel Morgan
Anton Yulo
>
> It seems highly unlikely that this table is the issue if you are using it
> as you seem to be. Can you provide the structure of the table and the
> number of rows? If so ... why not a simple byte calculation. But, as I
> indicated, I highly doubt this is the issue. Though you might want to look
> at your initSID.ora parameters.
>
> To find your Oracle version ...
>
> SELECT version
> FROM v_$instance;
>
> Daniel Morgan
oops. thanks. my ver is 8.1.7.3.0.
the table is defined:
TYPE custdate_rec IS RECORD(
custdate DATE,
custdatesource NUMBER);
TYPE date_buffer_table IS TABLE OF custdate_rec INDEX BY
BINARY_INTEGER;
DateBuf date_buffer_table;
the number of rows i reached before hitting the exception was 180,206.
do you know what the max sizes of variables with type DATE and NUMBER
are? I don't have any Oracle Reference material on me so am unsure.
is there a way the process that is running to programatically
determine how much memory is still available to use?
Alex Filonov
> 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?
Richard Kuhler
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
DA Morgan
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
Pablo Sanchez
@twister.socal.rr.com:
> 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.
If it's an O/S limit you're hitting, it's more than likely a per user
limit. Depending on your shell you can try the shell built-in 'limit'
to tweak your shell's limits against the O/S limits.
You'll always be bounded by the O/S so if you hit that limit and you
haven't hit the O/S max, you'll need to reconfigure your kernel.
Here's an example in bash:
pablo@bird-1003-[pablo]: limit
cputime unlimited
filesize unlimited
datasize unlimited
stacksize 8192 kbytes
coredumpsize unlimited
memoryuse unlimited
descriptors 1024
memorylocked unlimited
maxproc 1527
openfiles 1024
pablo@bird-1004-[pablo]: limit stacksize unlimited
pablo@bird-1005-[pablo]: limit
cputime unlimited
filesize unlimited
datasize unlimited
stacksize unlimited <------------------
coredumpsize unlimited
memoryuse unlimited
descriptors 1024
memorylocked unlimited
maxproc 1527
openfiles 1024
--
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com
Pablo Sanchez
216.166.71.233:
> Here's an example in bash:
Correction: ^bash^tcsh (or csh)