topic Re: Oracle and memory in Operating System - HP-UX

Oracle and memory

Stafford Hyacinth — Wed, 02 Nov 2005 06:42:18 GMT

I have an rp8400 w/40GB or ram but the DBA is only using 1/4 of the memory.

Q. Does not the philosophy the more memory for oracle the happier it will be still apply?

I am being told by the dba that the more memory for oracle = the more memory oracle has to manage so that does not mean better performance. Does everyone agree w/this analysis?

Re: Oracle and memory

RAC_1 — Wed, 02 Nov 2005 06:47:37 GMT

There are limits as to what is good setting. But in most cases the more memory for oracle the better it performs.

What are you settings for oracle SGA, shared memory-shmmax??

Also other kernel tunables??

Re: Oracle and memory

Steven E. Protter — Wed, 02 Nov 2005 06:51:56 GMT

A. Yes

Are you running 32 bit or 64 bit Oracle.

In general, without any known limits, Oracle will perform better with more memory.

If its a small database and performance is maxing out you gain nothing by adding memory.

However the last paragraph has never happened to anyone I know.

Assuming there is no plans long term to increase your machines workload, increasing certain parts of the Oracle SGA might provide some performance impact.

To analyze os performance:

http://www.hpux.ws/system.perf.sh

HP-UX only.

SEP

Re: Oracle and memory

Eric Antunes — Wed, 02 Nov 2005 06:52:12 GMT

Hi Stafford,

I fully agree with your DBA.

See Metalink Note 1012046.1 to calculate the shared_pool_size requirements

Best Regards,

Eric Antunes

Re: Oracle and memory

Jean-Luc Oudart — Wed, 02 Nov 2005 07:24:30 GMT

Hi

from Oracle9i you have advisory utilities and you should use them to tune your memory usage on the server.

see attachment

Regards
Jean-Luc

Re: Oracle and memory

Eric Antunes — Wed, 02 Nov 2005 07:56:18 GMT

Hi again,

See Metalink Note 62143.1 (Understanding and Tuning the Shared Pool) witch has the reference to Note 1012046.1...

Eric

Re: Oracle and memory

Patti Johnson — Wed, 02 Nov 2005 09:16:16 GMT

Depending on the size of the database and the number of users being supported 10G may be enough memory for Oracle. If there are no performance issues and the user community is happy, then their is no reason to increase Oracle's memory usage.
There can be performance problems if the dba just throws memory at a problem. For example making the shared_pool very large instead of pinning packages and using bind variables will not help performance. Lots of memory for the buffer cache is no subsitute for a well tuned application that accesses few block to complete a query.

In general Oracle does like a lot of memory, but if the database is using 10G then it may be performing well.

As a DBA I do like it when the Unix admin "wants" the database to have lots of memory :)

Patti

Re: Oracle and memory

Stafford Hyacinth — Wed, 02 Nov 2005 10:44:47 GMT

Thanks for the information it has been most helpful. The database is ~ 900GB on Oracle 9i.

Re: Oracle and memory

Stafford Hyacinth — Wed, 02 Nov 2005 10:46:31 GMT

Oops 64-bit

Re: Oracle and memory

TwoProc — Wed, 02 Nov 2005 10:54:07 GMT

10g is pretty good size, unless you're still seeing very high I/O. In that case, EVEN if you've got a 97% hit ratio, increasing the size of db_block_buffers *could* greatly increase throughput. However, it may not. Increasing the size of the SGA is not NEARLY as effective as tuning the code that is doing lots of reads to and from disk. But, if that is already done to a reasonable level, then increasing the buffer cache could deliver great benefits, but then again maybe not. You'd have to get some benchmarks, make the change and see.

What people don't generally grasp is that when your buffer cache hit ratio is 97% - the remaining three percent represents just about 70% - 90% of your disk i/o (except redo logs and archive logs). So, theoretically a 1% increase in the hit ratio to 98% from 97% which looks small, represents as much as 1/3 reduction of total i/o (except for redo and arch logs)!
From my experience this is largely true and accurate.

The problem is, that the increase in SGA that got you from say 96% to 97% may cost you as little as 1G more or so, while the jump from 97% to 98% may cost you 6G more, and the jump to 99% may cost you another 15Ggig!

In other words, it follows the law of diminishing returns. Now, at that point of getting another 1% reduction in I/O you've now got an offset in performance from the brand-new CPU load that you've got from now maintaining, let's say 6 G more of 8k blocks - that's housekeeping and maintenance for 786,432 more blocks! Well, if you weren't CPU bound(per process and in total) before - then you may be much better off now. Glance, or even better - perfview will tell you this now, along with your DBA's help by using statspack. The next question is, does the next increment of 1% or so, which would require even more blocks to achieve get you going forward?

Someone mentioned the shared pool. This is where the executing code lives. It is vitally important that the cache hit ratio for this area remains high too. Make sure that is checked. Keep in mind that a 1% increase in this area is MUCH easier in terms of commmitted memory to achieve than db_buffer_cache. So, check this, and resolve it first.

Overall, I agree with the others who say more memory is better. In general it is, but it would be best to know what you're doing and why. Then, measure the system to make that the intended effects are positive, because you could be trading in your i/o problems for cpu ones.

Re: Oracle and memory

Jean-Luc Oudart — Wed, 02 Nov 2005 11:03:50 GMT

Hi again,

also before you make any change you should take a few statspack reports (ask the DBA) along with OS monitoring.
Then apply the changes (usually one at a time), monitor again (OS + Oracle) and compare with the baseline.

The baseline is important for this exercise as well as when users will(!) complain about performance. At least you will have something to go back too. Most of time it's either the increase in load (average number of users /transactions) and/or new transactions.
Your perfstats reports will help you (and DBA).

Regards
Jean-Luc

Re: Oracle and memory

Ted Buis — Thu, 03 Nov 2005 18:33:20 GMT

You haven't really told us much about your environment. Do you have OnLineJFS? Do you have GlancePlus? What is your buffer cache kernel parameters. Is your swapchunks/maxswapchunks value high enough to address all your virtual memory.

Your buffer cache is likely wasting memory if you still have the default for the maximum dynamic buffer cache of 50%. Also, you can avoid double buffering from Oracle and the HP-UX OS if you select the proper mount options if you have OnLineJFS, which would waste memory and create extra memory to memory transfers.

GlancePlus can show you your peak and average memory usage, so it will allow you to see how much unused memory you really have.

Since RAM access is 1000 times faster than disk access, I still think that more RAM is a great asset, but just adding it doesn't optimize performance. Optimization requires looking at the system to see what is the precise bottleneck for your particular case. You might want to get a copy of the book "HP-UX 11i Tuning and Performance" by Robert F. Sauers to better understand the tools available for examining system performance.

Re: Oracle and memory

Stafford Hyacinth — Fri, 04 Nov 2005 11:51:38 GMT

All,

I have onlineJFS & GlancePlus.

The kernel params are as follows:
bufpages: 0
dbc_max_pct: 7
dbc_min_pct: 5
maxfiles: 4000
maxfiles_lim: 1024
nfile: 123000
nflocks: 2000
ninone: 34816
maxswapchunks: 8192
swapmem_on: 1
swchunks: 2048
vps_ceiling: 64
maxdsiz: 2415919104
maxdsiz_64bit: 2415919104
maxssiz: 401604608
maxsxiz_64bit: 1073741824
maxtsiz: 1073741824
maxtsiz_64bit: 1073741824
maxuprc: 3000
max_thread_proc: 4096
ntkthread: 7184
nproc: 4096
msgmap: 4098
msgmni: 4096
msgseg: 32767
msgtql: 4096
semmap: 4098
semmni: 4096
semmns: 16384
semmnu: 4092
semvmx: 32767
shmmax: 12884901888
shmmni: 1024
shmseg: 1024

Re: Oracle and memory

Ted Buis — Fri, 04 Nov 2005 18:18:50 GMT

If the swap page is 4K (I think, maybe someone can help here, since I don't have time to look it up right now), then you only have 32GB covered. In that case the system will deallocate 8GB of your 40GB memory. What does swapinfo -tam say?

Re: Oracle and memory

Stafford Hyacinth — Fri, 04 Nov 2005 18:26:07 GMT

swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 0 4096 0% 0 - 1 /dev/vg00/lvol2
dev 4096 0 4096 0% 0 - 1 /dev/vg00/swap2
dev 4096 0 4096 0% 0 - 1 /dev/vg00/swap3
dev 4096 0 4096 0% 0 - 1 /dev/vg00/swap4
reserve - 12915 -12915
memory 31561 16789 14772 53%
total 47945 29704 18241 62% - 0 -

Re: Oracle and memory

Ted Buis — Sat, 05 Nov 2005 10:42:35 GMT

Actually, the maximum swap space is determined by the formula maxswapchunks*swapchunks*1024. So, your 8192*2048*1024=16GB which is the physical device swap that you have. You may wish to consider increasing maxswapchunks to 2-3X the current value, so you wouldn't have to do that if you decided to add more device swap, but I don't think it is necessary now. I was thinking that the this formula needs to cover the virtual address space of physical device swap space plus pseudoswap which in your case is 75% of RAM or ~30GB for a total of 46GB, but that doesn't appear to be the case. It seems to only have to cover the physical device swap space.

Re: Oracle and memory

Ted Buis — Sat, 05 Nov 2005 10:49:42 GMT

You could use the "top" command to verify that your system sees all the physical and virtual memory.

Re: Oracle and memory

Steven E. Protter — Sat, 05 Nov 2005 13:36:16 GMT

Few things occurred to me.

1) There is a limit on how much swap the OS will use. I can't remember if it was 16 GB or 32 GB but beyond that there is not more.

2) Powerful system you got, love working with big hardware.

3) Your shmmax might be a little light for Oracle. You can go up to 25% of memory which HP-UX defines as ram plus swap. 1.2 GB of shmmax isn't going to be enough to run the kind of database a system this powerful is likely to run.

SEP

Re: Oracle and memory

A. Clay Stephenson — Sat, 05 Nov 2005 20:48:09 GMT

Shmmax is not 1.2GB but 12GB and for most applications that's plenty. Your situation may not be too dissimilar to this scenario: Consider a systems doing thousands of logical i/o's per seconds but only a very small number of physical i/o's per seconds. I have seen exactly that kind of situation in Oracle and Informix. Admittedly my example involves buffer cache but the situation is exactly the same in doing i/o in the SGA. The problem is that the system is doing tons and tons of reads (albeit logical ones) and the performance can be terrible. In cases like this, the answer is not more resources and faster hardware but better algorithms. In many, many cases one index exceeded all the resources and tuning by an order of magnitude.

You and your DBA's really need to get a handle on where the bottlenecks are and attack the problem in the order of the biggest bottlenecks.

I'll leave you with one other whackball thought. Convince your management to do development and test on an intentionally resource limited box with less than warp-speed hardware. When the algoritms are good enough to work well in that environment then they almost always scale up very well in production environments. The fact that this may greatly annoy your developers is an added bonus.

Re: Oracle and memory

Steven E. Protter — Sun, 06 Nov 2005 02:38:37 GMT

Always good to have a proofreader.

Thanks A. Clay.

SEP