Re: wait time is huge compared with cpu time

Hi

The database suffer cache buffer chains latch badly, this is a stress test environment.

I thought when you acquire a latch and you cannot get to it the server process spins which uses CPU. How is that cPU calculated? Is it included in the CPU used by this session ?

I have 450 sessions LS

tia

On 7/23/06, ramick <ramick_at_dotster.com> wrote:
>
> "Iguess to get the response time using Statspack of a highly loaded system
> or badly tuned system, which causes massive waits, is not possible. "
>
> Response time of what?
>
> If you are looking for the response time of an individual SQL statement,
> then trace with 10046.
>
> When I look at the part of a statspack report you posted earlier, it
> appears
> you have a pretty severe latch free wait condition - I would be you
> weren't
> getting much response time for anything due to all this spinning. This is
> the result of 2 or more processes contending and waiting for the same
> latch.
> I don't remember for 8174 and I don't have a test instance at that level,
> but further down in a 9205 statspack report there are sections that may
> help
> you find out which latch or group of latches may be the issue.
>
> This is why I (and probably others) suggested taking shorter interval
> snapshots over the period in which there is not an issue into the period
> in
> which there is - over a 2 hour period the real issue might have migrated
> or
> exacerbated itself into something else and with the reports covering this
> time you can probably find out what the real issue is.
>
> Is this a production problem or in a testing environment?
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of amonte
> Sent: Saturday, July 22, 2006 12:46 AM
> To: Cary Millsap
> Cc: oracle-l_at_freelists.org
> Subject: Re: wait time is huge compared with cpu time
>
> Right, it makes sense what page 215 says.
>
> Iguess to get the response time using Statspack of a highly loaded system
> or badly tuned system, which causes massive waits, is not possible. Not
> even
> with short interval Snapshots correct? If this is the case theclosest REAL
> response time we can get is with sql trace?
>
> TIA
>
> Alex
>
>
>
> On 7/21/06, Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> If you have a copy of Optimizing Oracle Performance handy (Millsap & Holt
> [2003]: O'Reilly), see page 215 for a detailed description of your
> quandary.
>
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> Nullius in verba
>
> Hotsos Symposium 2007 / March 4–8 / Dallas
> Visit www.hotsos.com for curriculum and schedule details...
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of amonte
> Sent: Friday, July 21, 2006 11:35 AM
>
> To: oracle-l_at_freelists.org
> Subject: wait time is huge compared with cpu time
>
> Hi
>
> I have a HP-UX Server running Oracle 8.1.7.4, there are 6 CPUs.
>
> I have a statspack report which shows this (2 hours interval)
>
> Snap Id Snap Time Sessions
> ------- ------------------ --------
> Begin Snap: 143 20-Jul-06 02:31:02 453
> End Snap: 167 20-Jul-06 04:31:06 453
> Elapsed: 120.07 (mins)
>
>
> Top 5 Wait Events
> ~~~~~~~~~~~~~~~~~ Wait %
> Total
> Event Waits Time (cs) Wt
> Time
> -------------------------------------------- ------------ ------------
> -------
> latch free 3,573,965 6,945,555
> 99.25
> log file sync 122,691 30,316
> .43
> log file parallel write 146,604 18,685
> .27
> SQL*Net more data to client 522,539 1,762
> .03
> db file sequential read 10,923 534
> .01
>
>
> Statistic Total per Second per Trans
>
> --------------------------------- ---------------- ------------
> ------------
> CPU used by this session 2,990,059 415.1 27.6
> CPU used when call started 2,423,510 336.4 22.4
>
>
> I was wondering how can we get over 69000 seconds waits in 2 hours with 6
> CPUs? That is much larger than
>
> 3600seconds * 6CPUs * 2hours
>
> TIA
>
> Alex
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l