Re: Monitoring questionable long running sessions

Rick,

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:2hq5f0p5543r1b8noanjsp85n5daljr7k0_at_4ax.com...
> "VC" <boston103_at_hotmail.com> wrote:
>
>
> >Posting execution plans as I asked earlier would be useful too.
>
> It would not help. It turned out that the sessions were really
> "broken".

I wonder how you determined that session were broken. The fact that the SQL was missing from the statspack report
is not sufficient, by itself, to arrive at the conclusion.

>
> After killing the DB sessions... nothing happened. "Something" was
> still running 100% on two CPUs. I had to kill the OS processes to get
> rid of the CPU hug.

So maybe it was not Oracle processes that were consuming CPU but rather the application itself that was accessing the DB. If the processes were indeed shadow (dedicated) Oracle processes, then you should have been able to relate them to specific sessions via v$process and v$session using the processes' OS PIDs.

>
> I read something about mad "spinning" CPU.

Never heard about anything like that.

> What I am missing is something like a "pstree" for DB sessions, in
> other words, how can I find slave_sids belonging to one coordinator?
> I will have to examinate the v$session view, perhaps there is a field
> indicating "I am a slave_sid".

You can use something like:

SELECT QCSID, SID, INST_ID "Inst",
  SERVER_GROUP "Group", SERVER_SET "Set",   DEGREE "Degree", REQ_DEGREE "Req Degree" FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET; ... to see the coordinator (QCSID) and slaves (SID).

(
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/tuningpe .htm#57218 ).

>
> Sofar, the only way to identify broken sessions is via all these
> peculiarities.
>
> Bye
> Rick Denoire

Regards.

VC Received on Mon Jul 12 2004 - 16:33:33 CDT