RE: wait time is huge compared with cpu time

From: ramick <ramick_at_dotster.com>
Date: Sat, 22 Jul 2006 17:52:51 -0700
Message-Id: <20060723005251.625003A2759@turing.freelists.org>

“I guess 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.

I guess 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 the closest 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

Received on Sat Jul 22 2006 - 19:52:51 CDT