Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Monitoring questionable long running sessions
"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 WAITEDKNOWN 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:02:35 CDT