Performance probs with Oracle 8

[RedBack]

I have a rp7400 running 12 Oracle instances. The I/O appears to be a bottleneck, yet with I look at the performance of the SAN (2GB fc, brocade switch & VA7400) it shows that it is bearly being used. My memory utalization is high but there is no excessive paging. Can someome tell me if these parameters look okay. There is 4Gb of Ram.
SHMMAX=3,221,225,472
SHMMIN=Not Set
SHMMNI=512
SHMSEG=150
SEMMNS=8640
SEMMNI=8192
SEMMSL=Not Set
SEMMSL_OVERIDE=2048

Thanks

Mark

[Stefan Farrelly]

Could you explain why you think the I/O appears to be a bottleneck when you also said the SAN is barely being used ?

What does; sar -d 1 30
show for disk i/o ?

Also, what do you mean by no excessive paging ? any paging is bad. What does> vmstat 1 30
show for paging ?

[Brian Crabtree]

Also, post your "dbc_min_pct" and "dbc_max_pct" settings.

Thanks,

Brian

[RedBack]

Sar -d 1 30
device %busy avque r+w/s blks/s avwait avserv
Average c1t6d0 5.67 0.50 8 81 4.59 8.96
Average c2t6d0 2.40 0.50 3 35 5.00 9.77
Average c6t0d1 71.99 0.66 29 1097 48.45 160.48
Average c6t0d3 5.97 0.50 5 158 4.83 7.15
Average c6t1d7 68.79 1.50 215 9893 6.15 15.02
Average c6t2d1 7.77 1.50 13 333 4.89 11.48
Average c6t2d3 17.07 0.50 14 1991 5.23 17.52
Average c8t2d2 0.17 0.50 3 94 5.23 0.81
Average c6t0d5 17.01 0.84 152 4743 5.04 3.74
Average c8t0d4 0.97 0.50 1 130 5.62 14.33
Average c8t2d0 0.10 0.50 3 89 4.60 0.47

Dbc_max_pct=2
Dbc_min_pct=2

[RedBack]

vmstat 1 30
procs memory page faults cpu
r b w avm free re at pi po fr de sr in sy cs us sy id
3 15 0 98014 10607 94 23 0 0 33 0 0 1588 19448 4169 31 6 63
1 8 0 99227 10556 16 0 3 0 0 0 0 1477 37250 1278 29 0 71
1 8 0 99227 10556 12 0 3 0 0 0 0 1376 30224 1101 29 1 70
1 8 0 99227 10556 9 0 3 0 0 0 0 1294 24723 1008 35 1 64
1 8 0 99227 10556 7 0 2 0 0 0 0 1251 20904 915 25 0 75
1 8 0 99227 10556 5 0 2 0 0 0 0 1212 17308 815 31 3 66
2 16 0 99931 10556 4 0 1 0 0 0 0 1181 23670 810 46 5 50
2 16 0 99931 9915 156 1 1 0 1 0 0 1129 34332 796 32 4 64
2 16 0 99931 9726 140 0 1 0 0 0 0 1160 28494 844 28 0 72
2 16 0 99931 9726 112 0 0 0 0 0 0 1119 23221 799 28 1 71
2 16 0 99931 9726 89 0 0 0 0 0 0 1115 18912 728 35 1 64
4 3 0 98529 9726 71 0 0 0 0 0 0 1140 17292 751 61 5 34
4 3 0 98529 9726 56 0 0 0 0 0 0 1173 23144 956 76 3 21
4 3 0 98529 9726 44 0 0 0 0 0 0 1137 40491 1055 61 1 38
4 3 0 98529 9720 35 0 0 0 0 0 0 1248 36637 1198 59 4 37
4 3 0 98529 9720 28 0 0 0 0 0 0 1481 34265 1532 68 3 29
4 4 0 104484 9720 22 0 0 0 0 0 0 1456 32169 1744 71 2 27
4 4 0 104484 9720 17 0 0 0 0 0 0 1441 29482 1798 55 2 43
4 4 0 104484 9720 13 0 0 0 0 0 0 1413 26695 1708 58 5 37
4 4 0 104484 9720 10 0 0 0 0 0 0 1322 44941 1570 32 0 68
procs memory page faults cpu
r b w avm free re at pi po fr de sr in sy cs us sy id
4 4 0 104484 9720 8 0 0 0 0 0 0 1288 37205 1379 26 1 73
1 11 0 105203 9720 6 0 0 0 0 0 0 1235 30328 1193 27 1 72
1 11 0 105203 9720 4 0 0 0 0 0 0 1166 24786 1068 25 0 75
1 11 0 105203 9720 3 0 0 0 0 0 0 1112 20276 923 27 2 71
1 11 0 105203 9720 1 0 0 0 0 0 0 1428 33248 926 51 6 43
1 20 0 110134 10542 1 0 0 0 0 0 0 1358 31553 874 29 2 69
1 20 0 110134 10542 0 0 0 0 0 0 0 1258 25529 765 25 1 74
1 20 0 110134 10542 0 0 0 0 0 0 0 1184 20927 728 26 0 74
1 20 0 110134 10550 0 0 0 0 0 0 0 1183 19089 760 46 1 53
1 20 0 110134 10550 0 0 0 0 0 0 0 1271 19279 919 55 2 43

[Bill Hassell]

12 instances of Oracle in just 4Gb? You need a lot more RAM, increase SGA for all the instances, and make sure all instances of Oracle are 64bit. If you are crippled with 32bit Oracle instances, you need to add all the memory window patches and then start each instance in it's own memory window. I would look at 500 to 1000 megs per instance of Oracle (you'll need 8Gb min, 12Gb recommended and increase SGA)

[T G Manikandan]

Bill has the right point.
The amount of memory for the 12 instance is less.

HOw many processors you have on the server.
THe performance is infact dependent on each and every aspect.


I would go with these options.

From your output I could not find a high I/O on the disks.Infact the avserv time is more than the wait time.
Probably all the disks are are minimal is the busy%.
The highest one is c6t0d1 from which you can distribute some datafiles to other disk.
But things look ok.

Things to cross check,


Just find out whether the SGA size of the instances are too high for the instance probably you can lower the size if they are not really reqd.



You can use Glance or
UNIX95= ps -e -o ruser,pid,vsz=Kbytes
to find out each process memory usage.

If you are using 8i or above and dedicated server model of database even each client connection would grab up around 25MB of memory.
YOu can also think upon the MTS model of database.