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.

[system administrator_15]

here's my output
Date: Tue Dec 23 14:42:03 2003

Processing pfdat table (1021904 entries)...

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

Physmem = 1048576 Available physical memory:
Freemem = 10140 Free physical memory
Used = 1038436 Used physical memory:
System = 209706 by kernel:
Static = 26672 for text and static data
Dynamic = 0 for dynamic data
Bufcache = 183003 for file-system buffer cache
Eqmem = 31 for equiv. mapped page pool
SCmem = 0 for system critical page pool
User = 684541 by user processes
Uarea = 3040 for thread uareas
Disowned = 26158 disowned pages

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

Dynamic = 0 Kernel dynamic data (sysmap):
MALLOC = 0 memory buckets:
bucket[ 5] = 0 size 32 bytes
bucket[ 6] = 0 size 64 bytes
bucket[ 7] = 0 size 128 bytes
bucket[ 8] = 0 size 256 bytes
bucket[ 9] = 0 size 512 bytes
bucket[10] = 0 size 1024 bytes
bucket[11] = 0 size 2048 bytes
bucket[12] = 0 size 4096 bytes
bucket[13] = 0 size 2 pages
bucket[14] = 0 size 3 pages
bucket[15] = 0 size 4 pages
bucket[16] = 0 size 5 pages
bucket[17] = 0 size 6 pages
bucket[18] = 0 size 7 pages
bucket[19] = 0 size 8 pages
bucket[20] = 0 size > 8 pages
Kalloc = 0 kalloc()
Eqalloc = 0 eqalloc()
Reserved = 0 Reserved pools


so if iam reading correctly i have 838mb assigned to kernal and therefore leaving 3.2 for users at the moment.
regards
andym

[Alex Ostapenko]

Hi Andrew!

I just want to emphasize Bill Hassell's reply and two of his points above...
(1) Physical memory all used is not a bad thing. You have to look at other metrics such as page-out rate to conclude that you have a memory problem.
(2) The kernel parameters referenced above "allow" an application to work and are not associated with performance per se.

Now, in many cases, Oracle performance problems are caused by disk bottlenecks. Therefore, in trying to improve Oracle performance when problems are due to disk, "the best I/O is no I/O".
(a) That means that if an application is doing multiple updates to a row, try to do multiple changes with one update.
(b) If a large SQL is not using an index resulting in a tablespace scan, try to put an index on it.
(c) If a SQL is selecting a lot of columns when only a few are needed, create a view to select only the needed columns.
(d) If the cache hit ratio (Oracle SGA or disk DBC) is low, try to increase them to improve cache hit ratio reducing physical I/O's.
(e) If you have two paths to the same device (e.g., HP DS2100 series, EMC open systems storage), but only one path is in use, try to "pvchange" (temporary change, does not survive a reboot) or "vgreduce/vgextend" (permanent change, but do it very carefully) to have some devices use one interface and others the other.
(f) If the physical I/O's are concentrated on one physical device, try to move database objects around to spread the I/O.
(g) If manual load balancing doesn't work, consider HP-UX LVM striping or extent-based striping or EMC striped metavolumes to levelize the load on multiple devices.
(h) And so on.
All these ideas will either reduce the total number of physical I/O's, or move some physical I/O's from one device where it may be causing a bottleneck to other devices that may not be as busy. I cannot overemphasize the importance of the DBA working closely with the application developers to design efficient SQL. Avoiding unnecessary I/O's right from the code design will potentially reduce the likelihood of problems later. (Unfortunately, this may be more difficult with vendor-provided code, but you have to try.)

Good luck!

=:-) Alex

[Jim Butler]

For oracle - dbc_max_pct should not evaluate to greater than 128 mB, generally speaking.

4 x .03 = 120Mb
Set dbc_max_pct to 3 - (or max 5)
dbc_min_pct to 2 or 3.



That will help your performance.

Also - You may need to bump these

semmni 600
semmns 1000
semmnu 256
semume 96
shmmni 850
shmseg 400

I don't know your database layout, but if that didn't solve it,
nflocks > 1000