Oracle 9 memory usage

[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