RE: wait time is huge compared with cpu time

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 21 Jul 2006 11:51:20 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF625AC6@WIN02.hotsos.com>

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 Fri Jul 21 2006 - 11:51:20 CDT