cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Out of Memory error

SOLVED
Go to solution
Angie Meeks_1
Occasional Advisor

Oracle Out of Memory error

I have a few questions about some errors that we are occasionally getting in some oracle processes.

These errors have occurred twice in the last 2 weeks.

yesterday, it affected 3 reports early in the day. I was not notified until hours later and at that time, memory and reports looked fine.

Two weeks ago, it happened for the first time. I wasn't sure what was going on, so i bounced the concurrent managers and it solved the problem.

The error is ORA-04030 out of process memory.
I do have a tar logged with oracle, but i'm not sure if the trace files are generated as a result of the error and not by the process possibly causing the error.

We are running 64 bit oracle database. We have an EMC clarion used for disk space and it has caching on it.

I am confused at which tools are best to monitor memory usage - i am reading conflicting articles about vmstat. We don't have glance although i could get a 60 day trial. Although my understanding was that Glance doesn't keep historical info so i couldn't go back and see what happpened a few hours ago.


My questions are:
-- If this problem happens again - is there any good tool to find out if there is 1 process possibly consuming a lot of memory?

-- Is there a good tool to use to monitor memory proactively so that i can maybe do something before this happens? Or is there a preferred way to monitor memory usage while it is happening?
I am running vmstat and storing off the values so I have some historical numbers. Should I just be looking at pi and po?


I am not sure if i am truly out of memory and need to ask management for more memory or if there is some sort of run away process hogging a lot of memory.
Our SGA is fairly large - total SGA is almost 2GB because our database buffers is very large.
I can decrease it but I'm not sure if that is really addressing the problem. I have been here almost 1 year and we have not made any significant changes to the system and have only added a handful of additional users.
We have a total of 8 GB memory on the server so the 2GB SGA should be ok.

Thanks!!
Angie
8 REPLIES
Steven E. Protter
Exalted Contributor
Solution

Re: Oracle Out of Memory error

My questions are:
-- If this problem happens again - is there any good tool to find out if there is 1 process possibly consuming a lot of memory?

I am attachin some tools. You can also use gpm or glance

-- Is there a good tool to use to monitor memory proactively so that i can maybe do something before this happens? Or is there a preferred way to monitor memory usage while it is happening?
I am running vmstat and storing off the values so I have some historical numbers. Should I just be looking at pi and po?

gpm or glance, which must be licenses will definitely do the job.

I am not sure if i am truly out of memory and need to ask management for more memory or if there is some sort of run away process hogging a lot of memory.
Our SGA is fairly large - total SGA is almost 2GB because our database buffers is very large.

That should be all right since you are runnig 64 bit version. You need to see what overall system memory and shared memory use is.

shmmax
shmseg

dbc_max_pct
dbc_min_pct

kernel parameters

I can decrease it but I'm not sure if that is really addressing the problem. I have been here almost 1 year and we have not made any significant changes to the system and have only added a handful of additional users.
We have a total of 8 GB memory on the server so the 2GB SGA should be ok.

Do your research, use the attached scripts to collect some data and see where it leads.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Angie Meeks_1
Occasional Advisor

Re: Oracle Out of Memory error

Thank you for the information.

Do you know if glance/gpm will keep the history data or is it only a real time look at memory utilization?

Do you know if there is a better document for usage of SAR? I tried the man pages, but am not sure the best way to use it to monitor memory usage?

Angie
renarios
Trusted Contributor

Re: Oracle Out of Memory error

Hi Angie,

Try to set maxdsiz to a higer value (i.e. >=128MB) as per Metalink Note 220688.1.

Cheers,

Renarios
Nothing is more successfull as failure
Bill Hassell
Honored Contributor

Re: Oracle Out of Memory error

> I am not sure if i am truly out of memory and need to ask management for more memory or if there is some sort of run away process hogging a lot of memory.

Because HP-UX is a virtual memory system, you can run your entire system Oracle system in just a few hundred megs of RAM. That's right, the 2Gb SGA and hundreds of megs of processes can run in much less than 1Gb of RAM. Oh, there will be a performance penalty--probably 100 to 500 times slower as the processes and shared memory areas are constantly swapped.

What this means is that the "out of memory" errors don't refer to RAM, they usually mean address limits imposed by ulimit and kernel parameters, specifically ulimit -d and maxdsix and shmmax. The kernel parametsrs are for development enviroments where programmers make mistakes and runaway programs consume massive amountgs of RAM. For off-the-shelf apps like Oracle, make maxdsiz and shmmax larger than will ever be necessary, 3Gb to 4Gb for maxdsiz and shmmax. These values are limits only and do not change programs.

> Our SGA is fairly large - total SGA is almost 2GB because our database buffers is very large.

And that is a good choice. Large SGA's can significantly improve Oracle response times. Unforunately, Oracle is not very clear when it says "out of memory" so it may be local memory or it might be a shared memory request.

> dbc_max_pct

With 8Gb of RAM and a large SGA, make sure that dbc_max_pct is about 8-10%.

> Do you know if glance/gpm will keep the history data or is it only a real time look at memory utilization?

Built into GlancePlus is measureware (mwa) which is collecting data even now. The extract command can create a data file suitable for an Excel spreadsheet so you can graph literally hundreds of metrics. You can also edit the Glance/Measureware config file to group specific processes together for easier tracking. Look at the docs in /opt/perf/paperdocs/mwa/C

> Do you know if there is a better document for usage of SAR? I tried the man pages, but am not sure the best way to use it to monitor memory usage?

sar is incredibly crude compared to Measureware. You'll have to spend hours creating scripts to read the sa1 files and as you've seen, stats for individual programs are not available. Since you're running 2Gb of SGA, your Oracle processes should all be 64bits, so make sure trhat maxdsiz_64 is larger than maxdsiz. I would set it to 4Gb just so you can forget it. None of your programs will ever come close to this limit, but by making the limit high, it removes another reason for "out of memory".

Note also that companion middleware programs may access SGA, they must also be 64bit programs.


Bill Hassell, sysadmin
Angie Meeks_1
Occasional Advisor

Re: Oracle Out of Memory error

Thank you very much for the information.
We do not have glance and my management is not interested in purchasing it at this point. I am quite comfortable writing scripts to pull from flat files - so if there is anyone that can help me with figuring out which sar command, that would be especially helpful.

Also note that we have the following settings:
maxdsiz 1073741824
maxdsiz_64bit 2147483684
shmmax 5033164800


However, please note that it is not the SGA that is having a problem - i believe that it is one particular process hogging all of the memory.

Is there any way to find the amount of memory each process is consuming?
Top gives RES and SIZE - but i'm not sure how to run it for all processes...

Is there any way to identify which processes are consuming large amounts of memory?

Eric Antunes
Honored Contributor

Re: Oracle Out of Memory error

Hi,

Try to reduce your huge SGA(see Metalink Note 100709.1 - Top 8 init.ora Parameters Affecting Performance. There is an equivalant Note for Oracle 9i...) to 1.75Gb to fit into quadrant S2 (see Metalink Note 69119.1 - HP-UX: SGA Sizing issues on HP-UX).

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Oracle Out of Memory error

Hi Angie,

Please post here the following results:

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache;

select name, bytes/1024/1024 "Free Size in MB"
from v$sgastat
where name='free memory';

Regards,

Eric
Each and every day is a good day to learn.
Bill Hassell
Honored Contributor

Re: Oracle Out of Memory error

The simplest method is:

UNIX95= ps -e -o vsz,pid,uid,args | sort -rn | head

That will show you the top memory users. There probably isn't anything you can do about the largest programs unless you have the source code.


Bill Hassell, sysadmin