Re: v$wait_event: wait_time < 0

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Thu, 14 Jun 2007 18:10:37 GMT
Message-ID: <xwfci.9067$cx4.9007@trnddc07>

Brian Peasland wrote:
> Chuck wrote:

>> I know this question was asked before but I don't think the answer was
>> clear. I have the following returned from a query of v$session_wait
>>
>> SID EVENT           WAIT_TIME  SECONDS_IN_WAIT STATE
>> --- --------------  ---------  --------------- -----------------
>> 19 latch free              -1              361 WAITED KNOWN TIME
>>
>> The seconds_in_wait is constantly increasing. According to the 9i
>> docs, the wait_time of -1 and state indicate that the wait event was
>> very short and is now over. But if I look at the server process for
>> sid 19, it's eating up cpu like there's no tomorrow (12.4% on an 8 cpu
>> server). What exactly is the server process doing? Normally you can
>> tell by the wait events but apparently not in this case.

>
> If the process is on the CPU doing work, then it is not waiting for
> anything, hence no wait event will be seen during that time. A process
> is either processing (on the CPU) or waiting for some other event to
> occur (read data from disk, get a response back from the user, etc).
>

>> This process sat on the "latch free" event for 20 minutes with the
>> seconds_in_wait increasing. If I run it again it may show up as
>> sitting on "scattered read" for 20 minutes before finishing. A third
>> time it may sit on "sql*net message from client" all the while the
>> server process is running away with cpu. What is going on
>>
>> DB Version is 9.2.0.5. O/S is solaris.

>
> You'll want to look at the entire service time. Service time = CPU time
> + Wait Time. So let's say your process runs for 10 hours (600 minutes).
> But your session only waits for a total of 60 minutes. Then CPU Time =
> 540 minutes for this session (600 minutes=CPU_Time+60 minutes). In this
> example, you could cut your wait time down to 0 and your process would
> still take 9 hours (540 minutes)! On the other hand, your service time
> may be 45 minutes with 30 minutes of wait time (15 minutes of CPU).
> Cutting down on wait time in this example may be of big benefit to you.
>
> So you'll have to figure out how much of your service time is CPU time
> and how much is wait time. Total wait time can be determined from a SQL
> trace or from V$SESSION_EVENT. So how much time is being used by the CPU
> in your case? If you are predominantly CPU-bound for your process, then
> you need to look at the CPU-intensive operations being performed
> (sorting, calculations, etc) by this application process. See if those
> can be refined.
>
>
> HTH,
> Brian
>
>
>

So there there is no definitive way to break out exactly what it's doing within that black box category of "CPU time"?

If I look at the explain plan for the SQL there's not much there that should be using CPU. There is a sort but that should be the last thing done and should only be operating on about 200 rows. Received on Thu Jun 14 2007 - 13:10:37 CDT