I have an interesting problem I would like some help in explaining....
Environment--
Oracle 8.0.5 EE
MP-RAS
Problem--
The situation is like this; I had 4 MTS processes that were utilizing 100% of the cpu time on the machine for about 6 hours this afternoon. These four processes were the same user ( application login ) running the same sql statement. Each process had a high number of waits, db file sequential read values and no actual i/o activity ( as seen by sar -d ).
I would greatly appreciate any insight anyone can give me to help me understand what might have caused this to occur. I should be able to post any additional information that you think is needed.
High db_sequential reads might not always lead to poorly TUNED/written SQL, but it might be worth it to make sure that the SQL in picture is using proper indexes and Stuff.
Meanwhile making sure that it is not waiting on anything else(doubly sure) would be a good idea. Can you post the o/o of select * from v$session_wait ??
Since its MTS, make sure SGA is sized properly ('cos SORT area is allocated from SGA for MTS...)