Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: v$wait_event: wait_time < 0

Re: v$wait_event: wait_time < 0

From: joel garry <joel-garry_at_home.com>
Date: Thu, 14 Jun 2007 16:18:03 -0700
Message-ID: <1181863083.545730.141170@q19g2000prn.googlegroups.com>


On Jun 14, 11:10 am, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> 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"?

Which Solaris? Google dtrace if 10.

Also see http://www.ixora.com.au/newsletter/2001_03.htm for some older thoughts, including why caching an entire db in the SGA may not be such a great idea (curiously, not mentioning latch issues with large numbers of buffers, which would have been worse in older versions of Oracle).

>
> 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.-

http://orafaq.com/papers/cpu.pdf is kind of interesting, though Charles' excellent description of what to look for in the trace is better. If you have latching issues with cpu issues, you may have some problem due to other processes besides the one that suffers. For example, running a batch that does a whole bunch of stuff over a period of time, then running that same batch so its block requests in the SGA keep bumping up against the first one, the first one would slow down with cpu trying to track all those block changes, the second one might suffer immensely. Readers may not block writers, but nobody ever said they won't have to wait while Oracle figures it all out.

Oh, and what David said.

jg

--
@home.com is bogus.
Windows live chat on Linspire:
http://news.yahoo.com/s/ap/20070614/ap_on_hi_te/microsoft_linspire;_ylt=AvVYijIGjPVnh7.CbDc2FCXMWM0F
Received on Thu Jun 14 2007 - 18:18:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US