Re: How to improve oracle IO performance ?

On Feb 25, 9:14 am, "jshen...._at_gmail.com" <jshen...._at_gmail.com> wrote:
> hi,
>
> Our oracle8.1.7 runs on HP-UX 11i with HP disk-array xp-1024. The
> disk array is raid-0.
>
> there are about 3 million accout mainteined in the DB which has
> accouting data input into the db. There are three snapshots on four
> tables of the DB. The refreshing interval is 5min. the four tables
> which are snapshoted mainteins the most part of information in DB.
>
> The max session number of DB is 600, and the peak connection number
> recorded is 571.
>
> we experience DB performance problem in past months. And, there
> happened three times of DB crash.
>
> I'm new to this area. Is there anyone would like to share
> knowledge and exprience with such problem ? ( e.g. where should I
> start ? what should be the key point of analysis? is there any must-
> read on DB optimization? )
>
> any word will be highly appreciated.
>
> Joe
>
> ps. the following is tracefile of DB crash , and disk io on DB server.
>
> 1. DB crash trace file
>
> /oracle/app/oracle/admin/pin65db/bdump/pmon_8454_pin65db.trc
> Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
> With the Partitioning option
> JServer Release 8.1.7.4.0 - 64bit Production
> ORACLE_HOME = /oracle/app/oracle/product/8.1.7
> System name: HP-UX
> Node name: cm3-dcb2
> Release: B.11.11
> Version: U
> Machine: 9000/800
> Instance name: pin65db
> Redo thread mounted by this instance: 1
> Oracle process number: 2
> Unix process pid: 8454, image: oracle_at_cm3-dcb2 (PMON)
>
> *** 2007-02-08 11:22:51.273
> *** SESSION ID:(1.1) 2007-02-08 11:22:51.273
> Exception signal: 10, code = 800003ffff7f3d88
> sc_onstack: 00000000172bc000, sc_mask 0000000000000000
> ORA-07445: exception encountered: core dump [10] [4286528904]
> [388743168] [0] [] []
> ----- Call Stack Trace -----
> calling call entry argument values in
> hex
> location type point (? means dubious
> value)
> -------------------- -------- --------------------
> ----------------------------
> Warning: Entry/Exit code is optimized. Cannot restore context (UNWIND
> 22)
> Error from U_get_previous_frame_x is 1
> Stack is not Windable
> Stack has no Unwind_descriptor
> sdsnm: dlmodinfo failed for address : 0x40000000
> ssexhd()+344 ? ksedst() 000000000 ?
> 800003FFFF7F0340 ?
> 800003FFE4002F50 ?
> 1D41D8F418 ?
> _sigreturn()+0 ? ssexhd() 100000000 ?
> 000000000 ?
> 053360020 ?
> 800003FFFF7F3840 ?
> kglobcl()+340 ? _sigreturn() 800003FFD000EDF0 ?
> 000000000 ?
> C00000013B1471C8 ?
> 8000000100080D68 ?
> kglobfr()+204 ? kglobcl() 800003FFFF7F3438 ?
> 8000000100004F60 ?
> 800003FFD0016268 ?
> C00000000003F593 ?
> kgllccl()+7040 ? kglobfr() 000000000 ?
> 000000000 ?
> 000000041 ?
> 100000000 ?
> kgllcu()+532 ? kgllccl() 000001772 ?
> 800003FFD000EDF0 ?
> 4000000000ABA987 ?
> 300000000 ?
> kslilcr()+348 ? kgllcu() C0000000009F7E63 ?
> 8000000100003E40 ?
> 200000000000 ?
> 000000001 ?
> kslcllt()+84 ? kslilcr() 008000008 ?
> C0000001291F97A0 ?
> 8000000100075340 ?
> C000000000A04177 ?
> kslcll()+1292 ? kslcllt() 100000000 ?
> 40000000007D9FBF ?
> 000000000 ?
> C00000013B145E50 ?
> ksuxfl()+1736 ? kslcll() 000A0568F ?
> 3EB1C4335 ?
> 800003FFFF7F29B8 ?
> 4000000000DB1207 ?
> ksuxda()+12 ? ksuxfl() 800003FFE4002F50 ?
> 4000000000AD042F ?
> 000000000 ?
> 4000000000AD00AB ?
> 0x0000000040000000 ? ksuxda() 4000000000D14F73 ?
> 00000001E ?
> 00000007F ?
> 800003FFFF7F5990 ?
> ----- Argument/Register Address Dump -----
> Argument/Register addr=000000000.
> Dump of memory from 0x000000000 to 0x000000100
> 00000000 00000000 00000000 00000000 00000000 [................]
> Repeat 15 times
> Argument/Register addr=800003ffff7f0340.
> Dump of memory from 0x800003FFFF7F0340 to 0x800003FFFF7F0440
> FF7F0340 800003FF FF7F60C0 00000000 00000002 [......`.........]
> FF7F0350 800003FF FBFFF480 00000000 00000000 [................]
> FF7F0360 00000000 00000000 00000000 00000000 [................]
> Repeat 4 times
> FF7F03B0 C0000000 000218B3 00000000 00000000 [................]
> FF7F03C0 800003FF FF7F0330 80000001 00075340 [.......0......S@]
> FF7F03D0 00000001 00005250 800003FF FF7F0158 [......RP.......X]
> FF7F03E0 80000001 000439A0 80000001 00000000 [......9.........]
> FF7F03F0 800003FF FBFFF480 00000000 00000040 [...............@]
> FF7F0400 00000000 00000000 00000000 00000000 [................]
> FF7F0410 00000002 00000000 00000000 00000200 [................]
> FF7F0420 00000000 00000000 00000000 00000000 [................]
> FF7F0430 00000000 00000430 40000000 00CEEB10 [.......0@.......]
> Argument/Register addr=800003ffe4002f50.
> Dump of memory from 0x800003FFE4002F50 to 0x800003FFE4003050
> E4002F50 00000000 00000000 00000000 00000000 [................]
> Repeat 15 times
> Argument/Register addr=800003ffff7f3840.
> Dump of memory from 0x800003FFFF7F3840 to 0x800003FFFF7F3940
> FF7F3840 00000000 00000000 00000000 00000000 [................]
> Repeat 5 times
> FF7F38A0 32000041 172BC000 00000000 00BAB000 [2..A.+..........]
> FF7F38B0 00000000 5282C040 00000000 00BA3000 [....R..@......0.]
> FF7F38C0 00000000 00BA0800 00000000 00000000 [................]
> FF7F38D0 00000000 172BC108 00000000 00000100 [.....+..........]
> FF7F38E0 00000000 00000000 00000000 00000011 [................]
> FF7F38F0 00000000 08000008 00000000 00030720 [............... ]
> FF7F3900 00000000 00000001 00000000 000331D0 [..............1.]
> FF7F3910 00000000 00000801 00000000 FFFFFFFF [................]
> FF7F3920 00000000 001FBAE8 00000000 7FC1BE4C [...............L]
> FF7F3930 00000000 00000003 00000000 00000000 [................]
> Argument/Register addr=800003ffd000edf0.
> Dump of memory from 0x800003FFD000EDF0 to 0x800003FFD000EEF0
> D000EDF0 00000000 00000000 00000000 00000000 [................]
> Repeat 2 times
> D000EE20 80000001 001B8148 00000000 00000000 [.......H........]
> D000EE30 00000000 00000000 00000000 00000000 [................]
> Repeat 5 times
> D000EE90 00000004 00000000 00000005 00000000 [................]
> D000EEA0 00000000 00000000 00000000 00000000 [................]
> Repeat 3 times
> D000EEE0 00000000 00000000 00000006 00000000 [................]
> Argument/Register addr=c00000013b1471c8.
> Dump of memory from 0xC00000013B1471C8 to 0xC00000013B1472C8
> 3B1471C0 0B000000 00000000 [........]
> 3B1471D0 C0000001 3DF66EC8 00000000 00000000 [....=.n.........]
> 3B1471E0 C0000001 295DAC90 C0000001 295DADB0 [....)]......)]..]
> 3B1471F0 00000000 00000000 00000000 00000000 [................]
> Repeat 1 times
> 3B147210 00000000 00000000 C0000001 59D335A0 [............Y.5.]
> 3B147220 00000000 00000000 00000000 00000000 [................]
> 3B147230 C0000001 5C1730D0 00000001 00000000 [....\.0.........]
> 3B147240 00000000 00000000 00000000 00000000 [................]
> Repeat 1 times
> 3B147260 00000000 00000000 C0000001 3B18A190 [............;...]
> 3B147270 00000000 00000000 00000000 00000000 [................]
> 3B147280 00000001 00000000 00000000 00000000 [................]
> 3B147290 00000000 00000000 00000000 00000000 [................]
> Repeat 1 times
> 3B1472B0 C0000001 3B2753D0 C0000001 295D6A90 [....;'S.....)]j.]
> 3B1472C0 00000000 00000000 [........]
> Argument/Register addr=8000000100080d68.
> Dump of memory from 0x8000000100080D68 to 0x8000000100080E68
> 00080D60 80000001 00080BE0 [........]
> 00080D70 00000001 00000000 80000001 00080D80 [................]
> 00080D80 646C6D6F 64696E66 6F3A2049 6E76616C [dlmodinfo: Inval]
> 00080D90 69642061 64647265 73732E00 00000000 [id address......]
> 00080DA0 00000000 00000000 00000000 00000000 [................]
> Repeat 11 times
> 00080E60 00000000 00000000 [........]
> Argument/Register addr=800003ffff7f3438.
> Dump of memory from 0x800003FFFF7F3438 to 0x800003FFFF7F3538
> FF7F3430 0000000E 00000000 [........]
> FF7F3440 00000000 00000000 00000000 00000000 [................]
> Repeat 6 times
> FF7F34B0 00000000 00000002 00000000 0000007F [................]
> FF7F34C0 C0000000 0003F593 00000000 0000007F [................]
> FF7F34D0 800003FF FF7F0132 C0000000 00938848 [.......2.......H]
> FF7F34E0 800003FF E4002F50 800003FF D000EDF0 [....../P........]
> FF7F34F0 800003FF E4002F50 00000000 D0016268 [....../P......bh]
> FF7F3500 800003FF D0016268 00000000 001B8038 [......bh.......8]
> FF7F3510 800003FF D0016268 800003FF D000EDF0 [......bh........]
> FF7F3520 800003FF E4002F50 800003FF D001B108 [....../P........]
> FF7F3530 C0000000 009F6648 [......fH]
> Argument/Register addr=8000000100004f60.
> Dump of memory from 0x8000000100004F60 to 0x8000000100005060
> 00004F60 C0000001 292636E0 40000000 00248198 [....)&6.@....$..]
> 00004F70 80000001 00005738 00002106 00002711 [......W8..!...'.]
> 00004F80 FFFFFFFF 00000000 00000001 00000000 [................]
> 00004F90 00000000 00000145 00000006 00000001 [.......E........]
> 00004FA0 00000000 00000000 00000000 00000000 [................]
> Repeat 11 times
> Argument/Register addr=800003ffd0016268.
> Dump of memory from 0x800003FFD0016268 to 0x800003FFD0016368
> D0016260 00000001 00000001 [........]
> D0016270 00000001 00000001 00000000 00000001 [................]
> D0016280 00000001 00000000 00000000 00000000 [................]
> D0016290 FFFFFFFF 00000000 00000940 00000000 [...........@....]
> D00162A0 00000001 00000001 00000001 00000001 [................]
> D00162B0 00000000 00000000 00360000 00000021 [.........6.....!]
> D00162C0 00000001 00000001 00000001 00000001 [................]
> D00162D0 00000000 00000001 00000001 00000000 [................]
> D00162E0 00000000 00000000 FFFFFFFF 00000000 [................]
> D00162F0 00000940 00000000 00000001 00000001 [...@............]
> D0016300 00000001 00000001 00000000 00000000 [................]
> D0016310 00360000 00000021 00000001 00000001 [.6.....!........]
> D0016320 00000001 00000001 00000000 00000001 [................]
> D0016330 00000001 00000000 00000000 00000000 [................]
> D0016340 FFFFFFFF 00000000 00000940 00000000 [...........@....]
> D0016350 00000001 00000001 00000001 00000001 [................]
> D0016360 00000000 00000000 [........]
> Argument/Register addr=c00000000003f593.
> Dump of memory from 0xC00000000003F593 to 0xC00000000003F693
> 0003F590 53DB3EF1 53C23EC1 E840D000 37DE3EE1 [S.>.S.>..@..7.>.]
> 0003F5A0 53613801 E820D000 537B3811 00000000 [Sa8.. ..S{8.....]
> 0003F5B0 73C23FE1 73C30168 73C43EB1 73C53EC1 [s.?.s..hs.>.s.>.]
> 0003F5C0 3413006C 73DB3ED1 47580000 6BC03F09 [4..ls.>.GX..k.?.]
> 0003F5D0 8B132200 73BA3F81 375F009E F7E0041C [..".s.?.7_......]
> 0003F5E0 34040002 341A0002 0FFA12A0 53A33F81 [4...4.......S.?.]
> 0003F5F0 48760078 881620EA 34650010 37DD3FA1 [Hv.x.. .4e..7.?.]
> 0003F600 E800B794 34BA0000 53DB3ED1 48790078 [....4...S.>.Hy.x]
> 0003F610 8B202232 34BA0000 E800BAD4 37DD3FA1 [. "24.......7.?.]
> 0003F620 53DB3ED1 341C0000 53C23E81 53C53EC1 [S.>.4...S.>.S.>.]
> 0003F630 53C43EB1 E840D000 53C33EAD 8A8021CA [S.>..@..S.>...!.]
> 0003F640 34650010 37DD3FA1 E800B704 34BA0000 [4e..7.?.....4...]
> 0003F650 53DB3ED1 48770078 82E02172 6BC43F09 [S.>.Hw.x..!rk.?.]
> 0003F660 34BA0000 E800BA3C 37DD3FA1 53DB3ED1 [4......<7.?.S.>.]
> 0003F670 37D73F11 341A0020 34180002 37DD3FA1 [7.?.4.. 4...7.?.]
> 0003F680 EBFDAC31 34790000 53DB3ED1 83804098 [...14y..S.>...@.]
> 0003F690 6BDC3F01 [k.?.]
> Argument/Register addr=000000041.
> Dump of memory from 0x000000041 to 0x000000141
> 00000040 00000000 00000000 00000000 00000000 [................]
> Repeat 15 times
> 00000140 00000000 [....]
> Argument/Register addr=4000000000aba987.
> Dump of memory from 0x4000000000ABA987 to 0x4000000000ABAA87
> 00ABA980 53DB3CE1 CB809C7D 53C23C81 [S.<....}S.<.]
> 00ABA990 347A0000 375A004E 0F403342 53C63CD1 [4z..7Z.N._at_3BS.<.]
> 00ABA9A0 375A3FB3 0F401370 53C53CC1 73400000 [7Z?..@.pS.<.s@..]
> .......
>
> [snip]
>
> ----- End of Call Stack Trace -----
> ===================================================
> PROCESS STATE
> -------------
> Process global information:
> process: c0000001291c6ec0, call: c0000001293f6100, xact: 0,
> curses: c000000129269870, usrses: c000000129269870
> ----------------------------------------
> SO: c0000001291c6ec0, type: 1, owner: 0, pt: 0, flag: INIT/-/-/0x00
> (process) Oracle pid=2, calls cur/top: c0000001293f6100/
> c0000001293f6100, flag: (6) SYSTEM
> int error: 0, call error: 0, sess error: 0, txn error 0
> (post info) last post received: 13835058060568321616 106 2
> last post received-location: kslges
> last process to post me: c00000012920fae0 87 0
> last post sent: 0 0 51
> last post sent-location: ktmpsm
> last process posted by me: c0000001291c7f80 1 10
> (latch info) wait_event=0 bits=0
> Process Group: DEFAULT, pseudo proc: c0000001292638a8
> O/S info: user: oracle, term: UNKNOWN, ospid: 8454
> OSD pid info: 8454
> ----------------------------------------
> SO: c0000001295fbdc0, type: 9, owner: c0000001291c6ec0, pt: 0,
> flag: INIT/-/-/0x00
> (broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner:
> c0000001291c6ec0,
> event: 2, last message event: 9, messages read:
> 1
> channel: (c0000001295fc420) scumnt mount lock
> scope: 101, event: 15, last mesage
> event: 9,
> publishers/subscribers: 0/13,
> messages published: 1
> ----------------------------------------
> SO: c000000129269870, type: 3, owner: c0000001291c6ec0, pt: 0,
> flag: INIT/-/-/0x00
> (session) trans: 0, creator: c0000001291c6ec0, flag: (51) USR/-
> BSY/-/-/-/-/-
> DID: 0000-0000-00000000, short-term DID:
> 0000-0000-00000000
> txn branch: 0
> oct: 0, prv: 0, user: 0/SYS
> last wait for 'pmon timer' blocking sess=0x0 seq=33761
> wait_time=-2
> duration=12c, =0, =0
> ----------------------------------------
> SO: c0000001295fc030, type: 9, owner: c0000001291c6ec0, pt: 0,
> flag: INIT/-/-/0x00
> (broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner:
> c0000001291c6ec0,
> event: 2, last message event: 2, messages read:
> 0
> channel: (c0000001295fc2e0) system events
> broadcast channel
> scope: 101, event: 2218913, last
> mesage event: 0,
>
> publishers/subscribers: 0/292,
> messages published: 0
> ----------------------------------------
> SO: c0000001293f6100, type: 2, owner: c0000001291c6ec0, pt: 0,
> flag: INIT/-/-/0x00
> (call) sess: cur c000000129269870, rec 0, usr c000000129269870;
> depth: 0
> ===================================================
> CURRENT SESSION'S INSTANTIATION STATE
> -------------------------------------
> current session=c000000129269870
> ******************** Cursor Dump ************************
> Current cursor: 0, pgadep: 0
> Cursor Dump:
> End of cursor dump
> END OF PROCESS STATE
> ******************** Cursor Dump ************************
> Current cursor: 0, pgadep: 0
> Cursor Dump:
> End of cursor dump
> ssexhd: crashing the process...
> Background_Core_Dump = partial
>
> 2. disk io
>
> tty cpu
> tin tout us ni sy id
> 0 1326 6 0 3 91
>
> device bps sps msps
>
> c0t6d0 7 2.4 1.0
> c2t6d0 9 2.4 1.0
> c22t2d2 1376 41.0 1.0
> c22t3d3 1187 90.0 1.0
> c22t3d4 881 75.1 1.0
> c22t3d5 893 83.1 1.0
> c22t3d6 975 81.1 1.0
> c22t3d7 1066 88.8 1.0
> c22t4d0 1041 88.4 1.0
> c22t4d1 961 76.7 1.0
> c22t4d2 896 78.1 1.0
> c27t3d0 894 84.3 1.0
> c27t3d1 968 72.9 1.0
> c27t3d2 1237 89.0 1.0
> c27t3d3 1276 80.9 1.0
> c27t3d4 1277 88.8 1.0
> c27t3d5 1450 90.4 1.0
> c27t3d6 1336 89.0 1.0
> c27t3d7 1237 84.5 1.0
> c19t3d3 0 0.0 1.0
> c19t3d4 0 0.0 1.0
> c19t3d5 0 0.0 1.0
> c19t3d6 0 0.0 1.0
> c19t3d7 0 0.0 1.0
> c19t4d0 0 0.0 1.0
> c19t4d1 0 0.0 1.0
> c19t4d2 0 0.0 1.0
> c21t3d0 0 0.0 1.0
> c21t3d1 0 0.0 1.0
> c21t3d2 0 0.0 1.0
> c21t3d3 0 0.0 1.0
> c21t3d4 0 0.0 1.0
> c21t3d5 0 0.0 1.0
> c21t3d6 0 0.0 1.0
> c21t3d7 0 0.0 1.0
> c22t4d3 63 15.9 1.0
> c22t4d4 60 15.1 1.0
> c22t4d5 77 19.1 1.0
> c22t4d6 53 13.1 1.0
> c22t4d7 53 12.9 1.0
> c22t5d0 45 11.2 1.0
> c22t5d1 58 14.3 1.0
> c22t5d2 50 12.4 1.0
> c27t4d0 62 15.3 1.0
> c27t4d1 47 11.6 1.0
> c27t4d2 59 14.7 1.0
> c27t4d3 55 13.5 1.0
> c27t4d4 49 12.2 1.0
> c27t4d5 37 9.2 1.0
> c27t4d6 50 12.5 1.0
> c27t4d7 51 12.7 1.0
> c19t4d3 0 0.0 1.0
> c19t4d4 0 0.0 1.0
> c19t4d5 0 0.0 1.0
> c19t4d6 0 0.0 1.0
> c19t4d7 0 0.0 1.0
> c19t5d0 0 0.0 1.0
> c19t5d1 0 0.0 1.0
> c19t5d2 0 0.0 1.0
> c21t4d0 0 0.0 1.0
> c21t4d1 0 0.0 1.0
> c21t4d2 0 0.0 1.0
> c21t4d3 0 0.0 1.0
> c21t4d4 0 0.0 1.0
> c21t4d5 0 0.0 1.0
> c21t4d6 0 0.0 1.0
> c21t4d7 0 0.0 1.0
> c22t5d3 434 108.8 1.0
> c22t5d4 400 100.2 1.0
> c22t5d5 430 106.8 1.0
> c22t5d6 410 101.8 1.0
> c22t5d7 415 103.2 1.0
> c22t6d0 422 105.4 1.0
> c22t6d1 434 108.8 1.0
> c22t6d2 400 100.0 1.0
> c27t5d0 436 108.8 1.0
> c27t5d1 419 104.8 1.0
> c27t5d2 438 109.8 1.0
> c27t5d3 420 105.0 1.0
> c27t5d4 426 107.4 1.0
> c27t5d5 403 101.4 1.0
> c27t5d6 436 109.6 1.0
> c27t5d7 418 104.4 1.0
> c19t5d3 0 0.0 1.0
> c19t5d4 0 0.0 1.0
> c19t5d5 0 0.0 1.0
> c19t5d6 0 0.0 1.0
> c19t5d7 0 0.0 1.0
> c19t6d0 0 0.0 1.0
> c19t6d1 0 0.0 1.0
> c19t6d2 0 0.0 1.0
> c21t5d0 0 0.0 1.0
> c21t5d1 0 0.0 1.0
> c21t5d2 0 0.0 1.0
> c21t5d3 0 0.0 1.0
> c21t5d4 0 0.0 1.0
> c21t5d5 0 0.0 1.0
> c21t5d6 0 0.0 1.0
> c21t5d7 0 0.0 1.0
> c22t6d3 541 134.3 1.0
> c22t6d4 524 131.1 1.0
> c27t6d0 540 136.7 1.0
> c27t6d1 518 128.9 1.0
> c31t3d3 521 130.1 1.0
> c31t3d4 545 136.1 1.0
> c31t3d5 538 134.7 1.0
> c31t3d6 549 137.1 1.0
> c31t5d3 521 131.7 1.0
> c19t6d3 0 0.0 1.0
> c19t6d4 0 0.0 1.0
> c21t6d0 0 0.0 1.0
> c21t6d1 0 0.0 1.0
> c26t3d3 0 0.0 1.0
> c26t3d4 0 0.0 1.0
> c26t3d5 0 0.0 1.0
> c26t3d6 0 0.0 1.0
> c26t5d3 0 0.0 1.0
> c22t7d5 5398 236.1 1.0
> c19t7d5 0 0.0 1.0
> c22t7d3 301 75.7 1.0
> c27t7d0 252 62.4 1.0
> c19t7d3 0 0.0 1.0
> c21t7d0 0 0.0 1.0
> c22t2d3 958 28.7 1.0
> c27t2d2 918 27.7 1.0
> c27t2d3 960 67.7 1.0
> c27t2d4 933 40.8 1.0
> c27t2d5 918 25.9 1.0
> c27t2d6 918 27.9 1.0
> c27t2d7 1235 36.5 1.0
> c19t2d2 0 0.0 1.0
> c19t2d3 0 0.0 1.0
> c21t2d2 0 0.0 1.0
> c21t2d3 0 0.0 1.0
> c21t2d4 0 0.0 1.0
> c21t2d5 0 0.0 1.0
> c21t2d6 0 0.0 1.0
> c21t2d7 0 0.0 1.0
> c22t0d0 0 0.0 1.0
> c22t0d1 0 0.0 1.0
> c22t0d2 0 0.0 1.0
> c22t0d3 0 0.0 1.0
> c22t0d4 0 0.0 1.0
> c22t0d5 0 0.0 1.0
> c22t0d6 0 0.0 1.0
> c22t1d0 0 0.0 1.0
> c22t1d1 0 0.0 1.0
> c22t1d2 0 0.0 1.0
> c22t1d3 0 0.0 1.0
> c22t1d7 0 0.0 1.0
> c27t0d0 0 0.0 1.0
> c27t0d1 0 0.0 1.0
> c27t0d2 0 0.0 1.0
> c27t0d3 0 0.0 1.0
> c27t0d4 0 0.0 1.0
> c27t0d5 0 0.0 1.0
> c27t0d6 0 0.0 1.0
> c27t1d0 0 0.0 1.0
> c27t1d1 0 0.0 1.0
> c27t1d2 0 0.0 1.0
> c27t1d3 0 0.0 1.0
> c27t1d7 0 0.0 1.0
> c19t0d0 0 0.0 1.0
> c19t0d1 0 0.0 1.0
> c19t0d2 0 0.0 1.0
> c19t0d3 0 0.0 1.0
> c19t0d4 0 0.0 1.0
> c19t0d5 0 0.0 1.0
> c19t0d6 0 0.0 1.0
> c19t1d0 0 0.0 1.0
> c19t1d1 0 0.0 1.0
> c19t1d2 0 0.0 1.0
> c19t1d3 0 0.0 1.0
> c19t1d7 0 0.0 1.0
> c21t0d0 0 0.0 1.0
> c21t0d1 0 0.0 1.0
> c21t0d2 0 0.0 1.0
> c21t0d3 0 0.0 1.0
> c21t0d4 0 0.0 1.0
> c21t0d5 0 0.0 1.0
> c21t0d6 0 0.0 1.0
> c21t1d0 0 0.0 1.0
> c21t1d1 0 0.0 1.0
> c21t1d2 0 0.0 1.0
> c21t1d3 0 0.0 1.0
> c21t1d7 0 0.0 1.0
>
> 3. sar result
>
> 22:07:00 %usr %sys %wio %idle
> 22:07:05 6 3 76 16
> 22:07:10 6 2 77 15
> 22:07:15 5 2 70 23
> 22:07:20 5 2 62 31
> 22:07:25 6 2 74 17
> 22:07:30 6 2 74 18
> 22:07:35 5 3 75 17
> 22:07:40 5 3 72 20
> 22:07:45 6 2 70 22
> 22:07:50 5 2 65 28
> 22:07:55 5 2 68 24
> 22:08:00 6 3 69 22
> 22:08:05 6 3 75 17
> 22:08:10 5 2 65 28
> 22:08:15 6 3 71 20
> 22:08:20 6 2 74 19
> 22:08:25 6 2 77 15
> 22:08:30 5 3 71 21
> 22:08:35 6 3 75 16
> 22:08:40 5 2 69 24
> 22:08:45 5 2 75 18
> 22:08:50 6 3 72 20
> 22:08:55 5 2 71 21
>
> although 'sar' report above, the info shown on diskarray hardware
> monitor (xp-1024's control panel) show less than 15% io wait.

There are several bug repots on metalink that this might match but the posted information does have a close resemblence to bug# 1948533. If you have a support contract with Oracle you can open a Service Request and try to have it confirmed. Being that the bug report was closed as being non-reproducable there is likely no fix.

If upgrading from this old release is not possible then in general stable systems start experiencing crash type problems when resources are exhausted. That is the system has or is using all available memory, cpu, etc...

Try running statspack (See Performance and Tuning manual) reports during peek load times and see how things look. Tuning a few poorly performing statments might relieve the problem if it caused by Oracle resource problems and not OS resource problems. Take a close look at OS resources: memory, cpy, number of processes supported (max value for forked processes), etc....

HTH -- Mark D Powell -- Received on Sun Feb 25 2007 - 13:59:52 CST