Re: Monitoring questionable long running sessions

OK.

Could please trace the OS process which corresponds to SID 112 ?

You can use truss -f -o a.log -p process_id in order to obtain the trace. You'll see what system calls are executed by the PID and will know for sure there are /are not any read operations.

Thanks.

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:f0c1107f.0407130502.5d8088bd_at_posting.google.com...
> "VC" <boston103_at_hotmail.com> wrote in message
news:<6xFIc.77090$Oq2.65625_at_attbi_s52>...
>
> > The only process with this problem metalink mentions is lgwr althought
> > it's unclear how exactly 100% CPU consumption occurs since a typcal
'spin'
> > is like this:
> >
> > get a latch ->wait->retry-> etc. Due to waits, there cannot be 100%
CPU
> > usage by a 'spinning' process.
> >
> > When you observed your situation, was it lgwr or some other process ?
>
> No, it was not the lgwr. Search for"hanging spinning" in Metalink, you
> will get articles like:
>
> "Diagnosing high CPU utilization"
> "No response from server, does it hang or spin"
> "How to troubleshoot spinning runaway runtime processes"
> etc.
>
> Lot of stuff I have to rediscover now.
>
> To continue with this issue, OK, let's do it right.
>
> At this very moment, the query has been running for two days again.
> What I did:
> Found out coordinator and slave processes using your query, and then:
>
> select * from v$session_wait
> where sid in (34, 46,47,80,72,21,102,60,99,34)
>
> 1 21 41212 PX Deq: Execution
> Msg sleeptime/senderid 268566527 1001FFFF passes 41136 0000A0B0 0 00 0 2
WAITING
> 2 34 41273 PX Deq: Execute
> Reply sleeptime/senderid 200 000000C8 passes 41136 0000A0B0 0 00 0 2
WAITING
> 3 46 41139 PX Deq: Table Q
> Normal sleeptime/senderid 200 000000C8 passes 41134 0000A0AE 0 00 0 2
WAITING
> 4 47 41139 PX Deq: Table Q
> Normal sleeptime/senderid 200 000000C8 passes 41134 0000A0AE 0 00 0 2
WAITING
> 5 60 41199 PX Deq: Execution
> Msg sleeptime/senderid 268566527 1001FFFF passes 41136 0000A0B0 0 00 0 2
WAITING
> 6 72 41140 PX Deq: Table Q
> Normal sleeptime/senderid 200 000000C8 passes 41134 0000A0AE 0 00 0 2
WAITING
> 7 80 41422 PX Deq: Table Q
> Normal sleeptime/senderid 200 000000C8 passes 41132 0000A0AC 0 00 0 2
WAITING
> 8 99 41199 PX Deq: Execution
> Msg sleeptime/senderid 268566527 1001FFFF passes 41137 0000A0B1 0 00 0 2
WAITING
> 9 102 5625 db file sequential
> read file# 33 00000021 block# 213471 000341DF blocks 1 00000001 -1 83911
WAITED
> KNOWN TIME
>
> The last line with the session 112 is the one which is consuming CPU
> at this very moment (the query coord. has an SID of 34).
>
> The wait_event here is said to be "db file sequential read", but as
> far as I can see (and I can see far enough on the OS side), no I/O is
> taking place. It *can not* be waiting for that. It does not even show
> the file being read (it shows only "file#"). Repeating the query
> yields the same result.
>
> What now?
>
> Rick Denoire
Received on Tue Jul 13 2004 - 08:17:11 CDT