Monitoring questionable long running sessions

Hello

Last Friday, a query started by a colleague was consuming all eight CPUs at nearly 100% for quite a long time (Oracle 9.2.0.4). The OS (Linux RHAS 2.1) was showing at times unusually high (I would even say exagerated high) harddisk throughput values of more than 160 MB/s.

He then called me and asked me to investigated why his query was not delivering any results - since just one day before, it ran through in about 5 min. In fact, I could not recognize any other activities like "logical reads" or the like.

That reminded me similar situations when running 8.1.7 in the past. From time to time, sessions started to consume CPU at 100% indefinitely even when the DB session was closed. But this is the first time this happens on Oracle 9i since migrating 3 months ago.

I just restarted the instance and asked the colleague to rerun his query, which, again, is running since Friday at 100% on two CPUs. Don't know why it hijacked only two CPUs this time.

How can I be sure that the query is not doing some very inneficient operations or calculations? How can I find out if the instance or session itself is broken? I looked at the execution plan and remember a line "granule iteration" with a cost of 28000.

Of course, there is the command "oradebug", which I actually used to do a heapdump, but I myself can't do anything with the trace file. My question is how can I quickly diagnose the session.

Did you have similar experience with this kind of "run away" sessions?

Thanks a lot
Rick Denoire   Received on Sun Jul 11 2004 - 15:18:08 CDT