Re: Monitoring questionable long running sessions

Hello,

Judging by your description, the query runs in parallel mode. If the statistics is not quite OK, the optimizer may have made an incorrect choice on how to execute the query.

You can do the following:

1. Collect statistics
2. Get the execution plans both with PQ enabled and disabled and post it here

Also, run the query with PQ disabled and check if it's running 'faster'.

In order to see what the query is doing during its long run, find the query coordinator (the original SID that submitted the query) and its slaves' SIDs. Then you can trace the sessions in the usual way (the 10046 event).

VC

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:pc63f0d67nrpu1uhenv31a6qsaikv0nhaa_at_4ax.com...
> 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 - 16:02:32 CDT