topic Re: Memory allocation for Oracle database in Operating System - HP-UX

Memory allocation for Oracle database

Augusto César — Mon, 02 May 2005 10:15:30 GMT

Hi all,

I have a server running a HPUX 11.11 with 2 GB memory. This is a database server (Oracle 9i). How I know that all memory is available? How I allocate the 2GB memory for this server?

There is a SAM report:

Memory:
Real active: 1322504.3 KB
Virtual active: 1839271.7 KB
Free Memory pages: 2706 at 4 KB/page
Swap Space:
Used 2146 MB
Free 2974 MB

# dmesg | grep memory

Memory Information:
physical page size = 4096 bytes, logical page size = 4096 bytes
Physical: 2097152 Kbytes, lockable: 1546392 Kbytes, available: 1782672 Kbyte

# cat /stand/system

STRMSGSZ 65535
dbc_max_pct 25
dnlc_hash_locks 512
max_thread_proc 256
maxdsiz 1073741824
maxdsiz_64bit 2147483648
maxssiz 134217728
maxssiz_64bit 1073741824
maxswapchunks 16384
maxtsiz 0X4000000
maxtsiz_64bit 0X40000000
maxuprc ((NPROC*9)/10)
msgmni (NPROC)
msgseg 32767
msgtql (NPROC)
ncsize ((8*NPROC+2048)+VX_NCSIZE)
nfile (15*NPROC+2048)
nflocks 4096
ninode (8*NPROC+2048)
nproc 4096
nstrpty 60
semmni 4096
semmns (SEMMNI*2)
semmnu (NPROC-4)
semvmx 32768
shmmax (----space----)2147483648 <--- There is a space in this parameter.
shmmni 512
shmseg 32

This is SGA size:

SQL> show sga

Total System Global Area 1261400032 bytes
Fixed Size 737248 bytes
Variable Size 838860800 bytes
Database Buffers 419430400 bytes
Redo Buffers 2371584 bytes

Regards,

Augusto Cesar
(from Brazil)

Re: Memory allocation for Oracle database

Steven E. Protter — Mon, 02 May 2005 10:23:57 GMT

a few more tools would be useful.

glance/gpm and its memory report would help.

swapinfo -tam

would help.

the tools I'm attaching will let you measure use over time.

Now that being said, I've run oracle on 2 GB 11.11 machines and resource use can get a little tight.

SEP

Re: Memory allocation for Oracle database

Augusto César — Mon, 02 May 2005 11:40:05 GMT

Hi Steven,

Thanks for your answer and for the script, but I know little HPUX. I just know how I configure the HPUX for it use all physical memory (2GB).

Regards

Augusto

Re: Memory allocation for Oracle database

Tony Scully_2 — Mon, 02 May 2005 12:11:51 GMT

Hi Augusto,

Sorry, but the question is a little unclear.

HP-UX will allocate all the physical memory and its allocated swap as virtual memory.

Kernel parameters (such as dbc_max_pct) can influence how this is allocated, as can application settings (like SGA size).

Do you have a problem running the database? If you do, then post the symptoms. The commands that Steven suggested will provide output that will help give a picture of the system state as it is now.

Hope this helps,
Tony.

Re: Memory allocation for Oracle database

Augusto César — Mon, 02 May 2005 13:47:20 GMT

Hi Tony,

Sorry if my question is not clear. The problem is: the server have 2GB physical memory and the database has approximately 1,2-1,3 GB. I use the QuestÂ´s Spotlight for database management and its alerts that the HPUX only has 10-25 MB free. Its possible? Therefore my question is how I configure the HPUX for it uses efficiently the memory.

Regards,

Augusto

PS: Sorry my Englis

Re: Memory allocation for Oracle database

Julio Yamawaki — Mon, 02 May 2005 15:34:34 GMT

Hi,

I think you want to use more memory, right?
In your case, lower dbc_max_pct to 10 (normally, itÂ´s enough, as oracle doesnÂ´t use hp-uxÂ´s buffercache) and rise db_cache_size and shared_pool_size, but, pay attention to some other issues: if you use dedicated server, each connection that you have in the database will use aproximately 80 MB (processes oracleSID), so you have to calculate the space needed for this processes befores rising the values above.

Rega

Re: Memory allocation for Oracle database

Bill Hassell — Mon, 02 May 2005 20:46:28 GMT

The problem is not with HP-UX. All applications will get whatever memory the programs request. Most applications (other than HP's Glance program) give very poor information about free memory. HP-UX is a virtual memory system so you can run programs that need 10Gb of RAM in your 2Gb system. Whatever can't fit into RAM will be moved to the swap area. This create a very large delay for all your programs, but that's what you should expect.

In your case, you have SGA set to 1.26Gb out of your RAM (more than 50%). SAM shows that 50% of your swap space is already used so you have exceeded your memory by 2Gb already. So your report from the measurement tool is very misleading...you are using 200% or your RAM. If you run more processes, you'll just get more pageouts (swapping).

So your RAM size is way too small for this configuration of Oracle. The minimum should be 4Gb, but 6Gb is strongly recommended. Some of the Oracle SGA can't fit into RAM all the time so many of the benefits of a large SGA are being removed by swapping.

Re: Memory allocation for Oracle database

Augusto César — Tue, 03 May 2005 06:38:41 GMT

Hi Julio,

I will decrease the number of dedicated server, maybe solve a little.

Thanks.

Hi Bill,

Lately this message shows to me:

"ORA-27102: out of memory
HP-UX Error: 12: Not enough space
Additional information: 103
Additional information: 33554432
Additional information: 14833656 "

This problem, according the Metalink, is "not enough space usually means there is not enough real memory or swap space. In this case, it seems hardware has some problem freeing the memory". Solution: "Apparently reboot the box fix the problem". Therefore my initial question about the memory configuration.
A little question: Whats means the "avaliable" in this command?

# dmesg | grep memory

Memory Information:
physical page size = 4096 bytes, logical page size = 4096 bytes
Physical: 2097152 Kbytes, lockable: 1546392 Kbytes, available: 1782672 Kbyte

Thanks,

Augusto.

Re: Memory allocation for Oracle database

Bill Hassell — Tue, 03 May 2005 07:10:40 GMT

The "HP-UX error 12" is called the errno value, the Unix standard for reporting error conditions.

from /usr/include/sys/errno.h:
define ENOMEM 12 /* Not enough core */

from man 2 errno:
[ENOMEM] Not enough space.

During a system call such as exec(), brk(), fork(), or sbrk(), a program asks for more space than the system is able to supply. This may not be a temporary condition; the maximum space size is a system parameter. The error can also occur if there is not enough swap space during a fork().

Now the hardware has nothing to do with memory managemnent and as I mentioned, you have an unlimited amount of RAM as long as you have lots of swap space. The first step is to temporarily add more swap space using swapon. Because you are using so much swap space already, even a relatively small increase in memory usage may run out of swap space. You are already at 200% RAM usage (2Gb of swap already in use).

To see how badly the system is thrashing due to small RAM, use the vmstat command and look at the po (page out) column. 0-9 = OK, 10-99 = low memory, 100+ means severe memory shortage. There are only three fixes for excessive swapping (which means very slow performance):

1. Reduce the size of the Oracle SGA to perhaps 200-400 megs and accept the slow response time especially for row insertions and temp file sorts,

2. Reduce the number of users on the system by more than 1/2.

3. Add 4Gb of additional RAM to eliminate swapping and provide room for growth.

Re: Memory allocation for Oracle database

Hoang Chi Cong_1 — Tue, 03 May 2005 23:11:01 GMT

How size of your Database? I think you should reduce the SGA size...
As you said, you have 2GB of RAM but in your Oracle's SGA, it nearly 1.1 GB.....
Need to discuss with DBA administrator.

Regard,
HoangChiCong