Mem Util at 100%

[Coolmar]

I have hp-ux 11.11 running on a system with 2GB of memory running Oracle 10g. When I run Glance, I notice the Mem Util at 100% which tells me that things are not tuned correctly. The problem is that I am not sure which params should be tuned. I believe it might be dbc_min_pct and (max) but I don't know what would be ideal for my system. I have cut and pasted the glance screen below:

CPU Util 86% 75% 86%
Disk Util 30% 30% 43%
Mem Util 99% 100% 100%
Swap Util 44% 44% 44%
------------------------------------------------------------------------------------------------------------------------------------
MEMORY REPORT Users= 3
Event Current Cumulative Current Rate Cum Rate High Rate
--------------------------------------------------------------------------------
Page Faults 11124 28280 2366.8 875.5 2366.8
Page In 3131 9130 666.1 282.6 666.1
Page Out 380 1203 80.8 37.2 80.8
KB Paged In 11.3mb 44.2mb 2462.9 1399.7 2462.9
KB Paged Out 3.6mb 14.7mb 778.7 466.2 1317.1
Reactivations 0 0 0.0 0.0 0.0
Deactivations 0 0 0.0 0.0 0.0
KB Deactivated 0kb 0kb 0.0 0.0 0.0
VM Reads 1166 5410 248.0 167.4 276.8
VM Writes 394 1361 248.0 42.1 83.8

Total VM : 6.93gb Sys Mem : 322.1mb User Mem: 1.58gb Phys Mem: 1.98gb
Active VM: 5.76gb Buf Cache: 81.2mb Free Mem: 12.8mb

[Tim Nelson]

What is your output from swapinfo -tam ?

Also, User mem is 1.58 of 1.98GB. All memory is most likely being used by application. Look or ask what Oracle is configured for in it's SGA. Most likely Oracle is over configured for your 2GB system.

Buffer cache is only 81MG so your dbc_max or dbc_min is not the issue.

[Mel Burslan]

kmtune | grep dbc_max_pct

if this is left at the default 50, I would start by pulling it down a bit. It should not be any more than 10% and if you have a large memory model server, like 8G or so, make sure that, value of this parameter as a percentage of your physical memory is not any more then 600 MB as a rule of thumb.

[Coolmar]

swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 3968 0 3968 0% 0 - 1 /dev/vg00/lvol2
dev 4000 1257 2743 31% 0 - 0 /dev/vg01/swap2
reserve - 2683 -2683
memory 1497 185 1312 12%
total 9465 4125 5340 44% - 0 -

and

dbc_max_pct is at 10 and the min is at 4

[Bill Hassell · Accepted solution]

You wrote:

"Mem Util at 100% which tells me that things are not tuned correctly."

That's not really accurate at all. If you have spare RAM, then it is unused. The ideal system will use all your RAM most of the time. The only tuning is to tell the database admins to reduce their SGA area (which will severely impact Oracle performance), or buy more RAM (2 GB is really small for Oracle 10g).

The way you tell whether you need more RAM is to look at page outs, and in your case, you are SEVERELY short of RAM. The system still struggles to keep running processes in memory but it is paging like crazy. This will impose about 100:1 (that's correct) performance penalty because of massive swapping. Remember that when two programs can't fit into RAM, one must be stopped and paged out, and the other paged in so it can run. And during this page swap, NEITHER is running at all.

So paging (swapping) must be avoided before any performance questions are answered. If paging occurs at a few pages every few seconds, it's OK. But dozens ot hundreds per second: big penalty. Solution: double or triple your RAM. Now your DBAs can increase SGA even more for tremendous performance boosts.

[TwoProc]

I agree with Bill H - 2G is wayyy to small for 10g (or even 9i - maybe even 8i) for a server. Go and get 6-8G for a small to med server.

[Cem Tugrul]

i am fully agree with Bill & John...
As an addition;

How Much Memory is Oracle Using?
A common inquiry that I get from HP-UX admins, is how much memory is the database consuming?
There are four pieces in the amount of memory that Oracle uses (called the System Global Area or SGA): fixed size, variable size, database buffers, and redo buffers. You can determine the size of each of these pieces by using svrmgrl, and running the command "SHOW SGA."
The fixed size is the only parameter that cannot be changed using an init.ora parameter. On Oracle 8.0.x systems, the fixed size is approximately 50K, and
around 100K on 8.1.x systems. This section contains general database
information about the database (or instance) and the background processes.
The variable size is based on the sum of the shared_pool_size and the large_pool_size on 8.0.x and the sum of the shared_pool_size, large_pool_size and java_pool_size on 8.1.x. This chunk of memory is used for things like the data dictionary cache, sql area, and sort area.
The database buffers section is based on the db_block_buffers parameter multiplied by the block_size. This portion of memory is used for Oracle's caching of data. As data is retrieved from the database, it is stored in memory in the database buffer cache. You can determine the percentage of reads from memory using the following query :
select round(100*(1-(phy.value / (dbg.value + cng.value))),2) buffer_hit from v$sysstat phy, v$sysstat dbg, v$sysstat cng where phy.name = 'physical reads' and dbg.name = 'db block gets' and cng.name = 'consistent gets' ;
The redo buffer section is driven by the init.ora parameter log_buffer (actually it is log_buffer + one db_block). This portion of memory is used to store redo information (changes to the database), before they are written to the redo logs.

and also please have look at my attachment!!!

Good luck,

[Coolmar]

Thanks for the info Bill. Actually we are not really noticing too much of a performace hit, I just started playing around with Glance and of the very limited information that I could actually find on what the numbers mean and what they should be set at...I figured we had problems. It is only an "admin" server and therefore, mostly a "sandbox" so to speak so not too big a deal. Our prod servers are running 10g with 4 and 8GB of RAM and have Mem Util at 20%. I just was wondering if I could tune the kernel in any way in order to bring that 100% down into the 90's which, from what I have read, is good.

Thanks again,
Sally

[Bill Hassell]

You can't "tune" the kernel to reduce program sizes. If a program asks for 3Gb of RAM, it will get it, assuming the kernel limits and program design will allow it. I have demonstrated many times how a program can ask for (and get) 50Gb of RAM on a system with 2Gb of RAM. That's why HP-UX is called a virtual memory system. In HP-UX, there is no advantage to having memory lses than 100%. Indeed, you are probably running at 120% or even 200% of memory. In Glance (or the swapinfo command) look at the swap line. The UUUUUUUU section means something did not fit into memory so it was paged (swapped) out.

This is a good thing when there are idle programs (shells, mail readers, interactive programs with long user response times). You can run a lot more programs than you have RAM. Where this is not desirable is when the page-out rate goes over 1 or 2 digits for long periods. That means that a lot of processes are competing for memory at the same time, and competition only occurs when the programs are trying to execute.

So forget the percentage of RAM used. Instead, look at the page-out rates in the memory section of Glance (the m command). Ignore page-in as these count program starts as well as page-in, not useful.