Oracle 8.1.7 upgrade increases memory usage

[Frances Prindle]

We have a L class server that was running oracle 8.0.5 with memory usage of approx 60%. Upgraded oracle to 8.1.7, memory usage now at 100%. Are there parameters that need to be modified?

dbc_max_pct @ 10
dbc_min_pct @ 5

[Bill Hassell · Accepted solution]

Memory usage at 100% is a good thing (you are using all the RAM you bought) as long as there is little or no page outs (swapping). The increase may be expected (check the Oracle version notes) or it may be due to a change in one or more of the default initialization parameters. The DBC percentages are meaningless without the size of RAM stated. These values (5-10%) would be low if you have only 500 megs of RAM, and way too large if you have 24,000 megs of RAM.

[James R. Ferguson]

Hi:

10% of what? If you have more than, perhaps 300-400 MB of memory tied up in the buffer cache, you are probably wasting it. Let Oracle do its buffering. In fact, by allowing the Unix buffer cache to operate, too, you are doing *double* work.

Regards!

...JRF...

[Roger Baptiste]

hi,

what is RAM size??
Is the 100% memory usage constant? or does it occur only at times? Is this construed as a problem by the user?

It is not always a bad thing to have 100% memory usage. Only if you notice
the system swapping heavily, then it can considered a problem.

More information would help.

-raj

[Frances Prindle]

I have 4G physical memory and it is at 100% constantly.

[nancy rippey]

For oracle 8.1.7 we make the following kernel changes
STRMSGSZ 65535

maxdsiz 0X08000000

maxusers 256

nflocks 512

semmns 1000

semmni 500

shmmax 1073741824

Hope this help.s

[Roger Baptiste]

are you running one instance of db? has the dba increased the SGA size?

Also, tune the kernel params as suggested by others.

-raj

[Alan Riggs]

It has been my experience that every Oracle upgrade results in increased memory use. In particular, the size of the individual client connections usually increases noticeably. You are definitely peaking RAM, and that is a bad thing for syustem performance. Your options are:
buy more RAM
find memory hogs to trim.

Buying RAM is easier, but perhaps not an option. Find which processes are using memory using the ps command:
NIX95= ps -e -o "user,pid,ppid,sz,vsz,comm" | sort -rn +4|more

Other things to look at:
400MB is not horrible as a buffer cache max, but if this is a dedicated oracle server and you are using the vxfs mount options recommended by oracle then I would cut it even further (perhaps in half, even).

It might be possible to trim the size of your oracle SGA(s), but make sure you consult with your DBA(s) about possible performance impact.