Re: Monitoring questionable long running sessions

Rick,

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:5fj3f05etkikdlle9fb41r5ahemus0od2d_at_4ax.com...

> Do you have any particular reason to suspect that parallelity has
> anything to do with the problem? And which problem are you addressing:
> A broken session or an inefficient query?

For PQ, CBO usually prefers, sometimes erroneously full table scans, assuming that several slaves running in parallel would produce results faster in comparison to an alternative serial execution (I am simplifying the situation a bit). I do not think at this stage that the session is somehow broken but rather that the executon plan is inefficient. That's why I suggested running the query 'serially'.

Posting execution plans as I asked earlier would be useful too.

>
> >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).
>
> Well that is a good hint since I was not sure what session to trace
> in case where several Pxxx processes are running. Of course, when
> someone starts such a query and the problem mentioned happens, the
> server appears to be very slow for all other users, in which case fast
> action is required. Tracing and using tkprof to do analysis is not
> fast enough. In a first step, I would be glad to be able to recognize
> quickly if it is worth killing the session or OS process to free
> resources. As I explained, I am afraid that the query is doing some
> legitime task which by itself runs slow, and then I would have to let
> it run. Finding the reason is different issue where tracing and using
> tkprof would definitely help.

You can quickly take a look at what session waits are during an execution. Something like:

select * from v$session_wait
where sid in (coordinator_sid, slave_sids);

> Bye
> Rick Denoire
>
>
Received on Sun Jul 11 2004 - 20:43:35 CDT