"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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of amonte
Sent: Saturday, July 22, 2006 12:46 AM
To: Cary Millsap
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of amonte
Sent: Friday, July 21, 2006 11:35 AM
To: oracle-l@xxxxxxxxxxxxx
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
