Oracle 9 memory usage

[system administrator_15]

Hi guys
I have a n4000 6way with 4gb memory and 10gb swap assigned.
i have 4 8.1.7 databases and 4 9i the server has alway run ok with 817 and only recently have we put 9i on there.
but i seem now to have little free memory 50mb and wondered if for 91 weather there where any kernal parameteres i should pay special attention to( i used to have about 2 gb free memory).
what can i do to improve performance.
are there any problems with memory usage on 9i, the server was recently installed with 11i version 1 64bit.
what about maxtsiz and maxdsiz are there any accurate measurements to determine what these need to be set to.
regards
andrew

[Pete Randall]

Andrew,

For that kind of load, the best thing you can do is add at least another 4GB of memory.


Pete

[Michael Schulte zur Sur]

Hi,

for a rough memory consumption look at, how much shared memory the oracle instances use and run show sga on each instance.

greetings,

Michael

[system administrator_15]

shared memory reads as per attachment

[Steven E. Protter]

Unless you have a lot of activity, your swap settings are on the high side and likely to make your system look a little sluggish.

Before tuning the kernel, you might want to take a look at some performance data. I'm attaching the prior version of my script that does this. I just modified it at work yesterday and I'm not there right now.

I would pay attention to the settings for sga and such in the oracle databases. These can be tuned to reduce memory usage.

The fact that a system uses its memory is not in itself a problem. It doesn't do you much good if its just sitting there.

SEP

[Jeff Schussele]

Hi andrew,

Oracle memory usage is entirely dependent on the SGA size the DBAs set up for EACH DB instance.

You have two options:

1) Explain to the DBAs that they need to lower the SGA sizes for these *eight* DB instances.

2) Add more RAM. Right now you have less than 512MB avg for each DB. 1GB for each would be better.

And I'd check your dbc_max_pct kernel parm & if it's still at the default of 50 - LOWER it.

Rgds,
Jeff

[Jean-Luc Oudart]

Andrew,

I suppose you should start with Oracle resource usage.
Use statspack reports to analyze your hit ratio.
For the shared pool you can use the attached SQL script.

Regards,
Jean-Luc

[system administrator_15]

jeff
dbc_max at 20 and dbc_min at 5
regards

[Stuart Abramson_2]

1. Check dbc_min_pct and dbc_max_pct set to 5% and 10% respectively.

2. I have n4000 with 8 GB RAM and 8 cpus, running 5 x 8.1.7 SIDs, in 32-bit mode.

3. Run this command to see how big kernel is:

# /usr/contrib/Q4/bin/kmeminfo
kmeminfo (3.11)
libp4 (5.17): Opening /stand/vmunix /dev/kmem

Loading symbols from /stand/vmunix

======================================================================
Date: Tue Dec 23 09:26:56 2003

Processing pfdat table (2042785 entries)...

----------------------------------------------------------------------
Physical memory usage summary (in pages):

Physmem = 2097152 Available physical memory:
Freemem = 1237900 Free physical memory
Used = 859252 Used physical memory:
System = 353451 by kernel:
Static = 54367 for text and static data
Dynamic = 85226 for dynamic data
Bufcache = 209715 for file-system buffer cache
Eqmem = 47 for equiv. mapped page pool
SCmem = 4096 for system critical page pool
User = 459056 by user processes
Uarea = 3856 for thread uareas
Disowned = 50864 disowned pages

------------------------------------------------------------

A page is 4096 bytes. My kernel is 353451 pages = 1.2 GB. I have then 8 - 1.2 GB = 6.8 GB free memory.

You probably only have 4 - 1.2 GB = 2.8 GB free memory (assuming your kernel is similar tto mine.)

If you divide 2.8 GB / 8 SIDs you get 350 MB SGA for each SID.

So, as stated above, buy more memory or tell your DBAs to cut the SGA back to 350 MB for each SID.

I'd buy more memory.

Are you running in 64-bit mode? If so you can have bigger shared memory-s. But you don't have memory for it.

[Bill Hassell]

Free memory is not important in a virtual memory system such as HP-UX. What is important is the page-out rate over time. Use vmstat (or Glance) and if page-outs are in single digitas, you're fine. Double digits mean that more RAM will speed things up a bit, and triple digits means mandatory RAM (at least double) or live with very slow response times. Reducing SGA will prevent page-outs (swapping) but at the expense of Oracle spending a lot more time on disk.

maxtsiz never needs to be changed for Oracle. maxdsiz is only for 32bit applications, maxdsiz_64 is for 64bit programs, but if the programs run, there is no need to change. The maxdsiz* values are protection limits of fences to prevent runaway programs from grabbing unlimited amounts of RAM. A program will abort with out of local memeory error if maxdsiz* is set too low. Be careful though, Oracle reports memory problems without telling you if the memory problem is local or in shared memory.